Retrieving Data from Multiple Tables
I am a lover of historical fiction. Suppose I wanted to see all of the books of that genre. You will note that there is nothing in the book table which identifies the genre. However, there is a table called genres_books that contains a bookid column and a genre column. The declaration of bookid in genres_books indicates that it references the book table. So, one could issue the following query to list the bookid for each book that has a genre equal to "Historical fiction".
select bookid from genre_books where genre = "Historical fiction"; BOOKID cather03 cather07 cooper03 cooper04 defoe02 eliot04 hawthorne03 hawthorne04 scott01 scott07 stevenson06 twain05 twain09
Unfortunately, this does not tell you very much about the book. What you really need is to see the information in the particular row from the book table that has the same bookid listed in the genres_books table. You can do this using a query that specifies a join operation on the two tables as shown in the following example.
select last_name, title from book, genre_books
where book.bookid = genre_books.bookid and genre = "Historical fiction";
LAST_NAME TITLE
CatherW O pioneers! By Willa Sibert Cather ...
CatherW Shadows on the rock.
CooperJ The last of the Mohicans; a narrative of 1757.
CooperJ The prairie : a tale
DefoeD Memoirs of a cavalier:
EliotG Romola.
HawthorneN The scarlet letter, a romance.
HawthorneN The house of the seven gables, a romance.
ScottW Rob Roy.
ScottW Ivanhoe; a romance,
StevensonR Kidnapped : being memoirs of the adventures of David Balfour
TwainM The prince and the pauper : a tale for young people of all ages
TwainM Connecticut Yankee in King Arthur's court
The join is specified by listing each table in the FROM clause and then including in the WHERE clause an equals operation between the bookid columns in each table. When designing a database (see Defining a Database), as much as possible you will want to use the same column names between tables which are related in this way. These relationships can (and should) be explicitly declared through the foreign and primary key specifications in the CREATE TABLE statement. When you use the same column names in the two tables, the join operation based on those columns containing the same values is called a natural join. SQL provides a simpler syntax for specifying natural joins. For example, the above query can also be specified as follows.
select last_name, title from book natural join genre_books
where genre = "Historical fiction";
Join processing is a fundamental feature of all relational database systems. As such, SQL defines a rich set of join specification options. The syntax for specifying joins is given below.
select_statement:
SELECT select_item[, select_item]…
FROM table_ref [, table_ref… [WHERE conditional_expr]select_stmt: SELECT [FIRST | ALL | DISTINCT] select_item [, select_item]... FROM table_ref [, table_ref]...
select_item:
named_expr
| [table_name.]*named_expr:
[HIDE] {column_var | value_expr} [[AS] alias_name]table_ref:
table_spec | table_jointable_spec:
[database_name.]table_name [[AS] correlation_name]
| (table_join)table_join:
natural_join | qualified_join | cross_joinnatural_join:
table_ref NATURAL [INNER | LEFT [OUTER]] JOIN table_specqualified_join:
table_ref [INNER | LEFT [OUTER]] JOIN table_spec
[USING (column_name [, column_name]...) | ON conditional_expr]cross_join:
table_ref CROSS JOIN table_spec
The select_item result can be hidden from the result by specifying HIDE in front of the expression. This is useful when used in conjunction with an alias_name to hide an expression that is used in subsequent SELECT result column expressions but is of itself of no value in the SELECT result set. This is particularly true when used with subqueries.
The [table_name.]* select_item will add all of the columns from the specified table_name (or all of the tables in the FROM clause if table_name is not specified) to the result set.
The NATURAL JOIN specification indicates that the join is to be performed based on the common columns (names and types) from the two tables. The join is formed from the columns from the table (or tables) specified on the left side of "NATURAL ... JOIN" that have identical values with those columns from the table (or tables) on the right side that have the same name. Since common column names are used to form the join, sometimes you may not get the expected results because the tables may have unrelated columns that happen to have the same name. Thus, if you desire to make extensive use of the NATURAL JOIN, care must be taken in naming the columns in your table definitions so that common column names between related tables are only those upon which the joins are based. It is also best to explicitly declared the relationship using the PRIMARY KEY and FOREIGN KEY/REFERENCES clauses in your CREATE TABLE declarations.
By default, a NATURAL JOIN specification performs an inner join between two tables. An inner join is a join between those tables that have matching values in the join columns. However, sometimes it is possible to have values in one table that have no matching entry in the other. An outer join allows one to see those unmatched rows as wells. For example, the following query will return the list of all the books in the inventory for each author as well as those authors for which no books are available.
select bookid, full_name, title from author natural left outer join book; FULL_NAME TITLE Alcott, Louisa May Moods Alcott, Louisa May On picket duty, and other tales. Alcott, Louisa May Little women, or, Meg, Jo, Beth, and Amy ... Eliot, George Middlemarch: a study of provincial life. Faulkner, William *NULL* Ferber, Edna Dawn O'Hara, the girl who laughed, Ferber, Edna Show boat; a novel by Edna Ferber. Ferber, Edna American beauty, Franklin, Benjamin Advice to a young tradesman Gaskell, Elizabeth Cleghorn Mary Barton: a tale of Manchester life ... Gaskell, Elizabeth Cleghorn North and South. Gaskell, Elizabeth Cleghorn The life of Charlotte Bronte, by E.C. Gaskell. Gaskell, Elizabeth Cleghorn Wives and daughters. A novel. Gaskell, Elizabeth Cleghorn Cranford. Hardy, Thomas A pair of blue eyes; a novel by Thomas Hardy ... Hardy, Thomas Under the greenwood tree Hardy, Thomas Far from the madding crowd, Hardy, Thomas A Laodicean. A novel. Hawthorne, Nathaniel Fanshawe, a tale ... Hawthorne, Nathaniel Twice-told tales. Hawthorne, Nathaniel The scarlet letter, a romance. Hawthorne, Nathaniel The house of the seven gables, a romance. Hemingway, Ernest *NULL* Hobbes, Thomas Leviathan ...
A left outer join will include those rows from author (full_name is a column of author) that do not have a corresponding row in book (author is the left-side table in the join clause). In this example, our bookshop evidently does not have a book by Faulkner or Hemingway. To see only the authors that do not have a book in the inventory, enter the query below.
select full_name, title from author natural left join book where title is null; FULL_NAME TITLE Faulkner, William *NULL* Hemingway, Ernest *NULL* Steinbeck, John *NULL*
When there are common columns between two tables in which some of the columns should not be included in the join you can specify a qualified join where you explicitly identify the join columns. For example, each bookshop patron is serviced by one account manager. The account manager is identified by the mgrid column in the patron table. However, both tables also have a name column but clearly that column should not be used in the join. So, to see a list of account managers and the patrons each one services, enter the following SELECT statement.
select acctmgr.name, patron.name from acctmgr inner join patron using(mgrid); ACCTMGR.NAME PATRON.NAME Fox, Joe Bernard Arnult Fox, Joe Chatsworth Osborne Jr. Fox, Joe Giorgio Armani Kelly, Kathleen Stephen Jobs Kelly, Kathleen Scrooge McDuck Kelly, Kathleen Jay Gatsby Doel, Frank Warren Buffett Doel, Frank Artimis Fowel II Kralik, Alfred William Gates, III Kralik, Alfred Thurston Howell III Kralik, Alfred Charles Montgomery Burns Kralik, Alfred Jean Luc Picard Novac, Klara Mukesh Ambani Novac, Klara Richie Rich Novac, Klara Lucille Bluth Noble, Barney Carlos Slim Helu Noble, Barney Bruce Wayne Zonn, Amy Jed Clampett Zonn, Amy Jeffrey Bezos
The "inner" does not actually have to be specified as the default is to perform an inner join. Also notice that the columns in the SELECT expression list are qualified by table name to differentiate the account manager name from the patron name.
Where the join columns between the tables do not have the same name use the ON clause to provide the join conditions. Issue the following query on the NSF awards database to list the 2001 NSF grants awards to those sponsors located in North Dakota.
select name, award_date, title from sponsor join award on(sponsor_nm = name)
where state = "ND" and award_date between date "2001-01-01" and date "2001-12-31"
NAME AWARD_DATE TITLE
Bismarck St Coll 2001-07-10 Energy Technology Education Project
Cankdeska Cikana Community 2001-07-23 Cankdeska Cikana Community College Rural..
Dakota Technologies, Inc. 2001-06-22 SBIR Phase I: Novel Ultrasensitive Gas..
North Dakota State U Fargo 2001-06-11 Optics for Scientists and Engineers Lab..
North Dakota State U Fargo 2001-04-19 GOALI: Sequencing the Assembly Line and Anal..
North Dakota State U Fargo 2001-08-06 US-Egypt Cooperative Research: Development of..
North Dakota State U Fargo 2001-05-31 SGER: Evaluation and Modeling of Interlayer..
North Dakota State U Fargo 2001-09-25 Mathematics and Engineering Scholarships
North Dakota State U Fargo 2001-11-26 Developing and Assessing Impact of Problem-..
North Dakota State U Fargo 2001-12-26 Novel Instrumentation and Experimental for ..
North Dakota State U Fargo 2001-09-26 High Performance Network Connection in Suppo..
North Dakota State U Fargo 2001-05-11 Molecular Basis of Substrate Specificity, ..
North Dakota State U Fargo 2001-04-18 Statics: The next generation
SMC 2001-11-15 SBIR Phase I: Protective Metal Foam Hybrid..
Sitting Bull College 2001-03-07 Sitting Bull College Rural Systemic Initiative
Turtle Mountain Cmty Col 2001-09-20 Rural Systemic Initiatives in Science, Math..
U of North Dakota 2001-04-26 Red River Geoscience Education Pilot Project
U of North Dakota 2001-04-10 CAREER: Thermoeconomic Modeling as a Tool for..
U of North Dakota 2001-08-30 Acquisition of a Variable Temperature Automa..
U of North Dakota 2001-07-28 Acquisition of an Automated Sequencer
U of North Dakota 2001-05-02 CAREER: Protein Export in Escherichia coli
U of North Dakota 2001-02-20 REU Site: Research Experience for Undergradu..
U of North Dakota 2001-04-27 CAREER: Environmental Heterogeneity, Populat..
U of North Dakota 2001-11-19 University of North Dakota Computer Science,..
United Tribes Tech College 2001-07-20 United Tribes- Rural Systemic Initiative
The above examples all involve joins between just two tables. However, a SELECT statement can involve joins between more than two tables. Joins still occur in pairs. The result of a single join operation is a virtual table that is then joined with another table. Join processing proceeds in a left-to-right manner. Thus, the left-hand "table" for the second join is the result of the previous join and is joined to the next table. In the above syntax specification note that a table_ref on the left hand side of the join operator can be a fully specified join whereas the right-hand side is table_primary—a table name. This processing order can be altered (or clarified) using parentheses. For example, the query below will return the investigator name and the research title for all NSF awards granted to the University of Colorado at Denver.
select person.name, title
from (award natural join investigator natural join person)
join sponsor on (sponsor_nm = sponsor.name)
where sponsor.name = "U of Colorado Denver";
PERSON.NAME TITLE
Hirshman, Elliot Using Midazolam to Explore the Nature of Implicit Memory
Zapien, Donald C. RUI: Investigation of the Relationship of Ferritin's Struct..
Mandel, Jan Scalable Submesh Computing
Andrew., Andrew Acquisition of a High-Performance Parallel Computer for..
Mandel, Jan Acquisition of a High-Performance Parallel Computer for..
Bennethum, Lynn S. Acquisition of a High-Performance Parallel Computer for..
Russell, Thomas F. Acquisition of a High-Performance Parallel Computer for..
Billups, Stephen C. Acquisition of a High-Performance Parallel Computer for..
Stith, Bradley J. Lipid Signaling During Fertilization
Zamudio, Stacy Ancestry, Altitude and Placental Development in Highlands ..
Charles.§, Charles M. REU Site: American Economic Association Summer Training Pr..
Andrew., Andrew Preconditioned Algorithms for Large Eigenvalue Problems..
Sievering, Herman Sea-Salt Aqueous Phase SO2 Oxidation: Contribution to Mari..
Tracer, David P. Breast Feeding Structure and Parental Investment in Papua..
Jenkins, Peter E. Toward T3 Tetherless Communications Workshop, University..
Sanders, Nancy M. School District Capacity to Support the Mathematics Standar..
Billups, Stephen C. Algorithms for Nonsmooth Equations
Weaver, Gabriela C. Proof of Concept Proposal for Physical Chemistry in Practi..
Rens, Kevin L. Concrete Maturity: A Quantitative Understanding of How..
Notice that both the person and sponsor tables have a column called name. Thus, references to each name must be qualified with the table name to ensure that SQL uses the correct name.