How IT WorksA Primer On SQL Joins
Joshua Trupin
One of the trickiest things about learning SQL is mastering how various JOIN statements differ in the ways they combine data from multiple data tables. There are three types of joins: inner, outer, and cross. In addition, there are three types of outer joins: left, right, and full. It can be frustrating trying to keep them differentiated, so here's a quick guide. All of the following examples involve joining the authors and publishers tables in the Pubs sample database included with SQL Server™.
Inner Joins
In an inner join, records from two tables are combined and added to a query's results only if the values of the joined fields meet certain specified criteria. If you use an inner join to combine the authors and publishers tables based on their city and state columns, the result would be a list of all authors who live in the same city as a publisher (see Figure 1):
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
AND a.state = p.state
ORDER BY a.au_lname ASC, a.au_fname ASC
Figure 1 Inner Join
au_fname | au_lname | pub_name |
Abraham | Bennet | Algodata Infosystems |
Cheryl | Carson | Algodata Infosystems |
Outer Joins
An outer join returns all rows from the joined tables whether or not there's a matching row between them. The ON clause supplements the data rather than filtering it. The three types of outer joins, left, right, and full, indicate the main data's source.
Left Outer Joins
When you use a left outer join to combine two tables, all the rows from the left-hand table are included in the results. So, for the authors and publishers tables, the result will include a list of all authors along with a publisher's name column. If a publisher exists in the author's city, it's listed. Otherwise, the field in the publisher's column is set to NULL (see Figure 2):
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Figure 2 Left Outer Join
au_fname | au_lname | pub_name |
Reginald | Blotchet-Halls | NULL |
Michel | DeFrance | NULL |
Innes | del Castillo | NULL |
Ann | Dull | NULL |
Marjorie | Green | NULL |
Morningstar | Greene | NULL |
Burt | Gringlesby | NULL |
Sheryl | Hunter | NULL |
Livia | Karsen | NULL |
Charlene | Locksley | NULL |
Stearns | MacFeather | NULL |
Heather | McBadden | NULL |
Michael | O'Leary | NULL |
Sylvia | Panteley | NULL |
Albert | Ringer | NULL |
Anne | Ringer | NULL |
Meander | Smith | NULL |
Dean | Straight | NULL |
Dirk | Stringer | NULL |
Johnson | White | NULL |
Akiko | Yokomoto | NULL |
Abraham | Bennet | Algodata Infosystems |
Cheryl | Carson | Algodata Infosystems |
Right Outer Joins
A right outer join is conceptually the same as a left outer join except that all the rows from the right-hand table are included in the results. They can be included more than once, if more than one author exists in the publisher's city. If no author lives in the publisher's city, the author name fields are set to NULL (see Figure 3):
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Figure 3 Right Outer Join
au_fname | au_lname | pub_name |
Abraham | Bennet | Algodata Infosystems |
Cheryl | Carson | Algodata Infosystems |
NULL | NULL | Binnet & Hardley |
NULL | NULL | Five Lakes Publishing |
NULL | NULL | GGG&G |
NULL | NULL | Lucerne Publishing |
NULL | NULL | New Moon Books |
NULL | NULL | Ramona Publishers |
NULL | NULL | Scootney Books |
Full Outer Join
As you might have gathered, a full outer join retrieves all the rows from both joined tables. It returns all of the paired rows where the join condition is true, plus the unpaired rows from each table concatenated with NULL rows from the other table. You usually won't want to use one of these.
Cross Join
A cross join returns not the sum but the product of two tables. Each row in the left-hand table is matched up with each row in the right-hand table. It's the set of all possible row combinations, without any filtering, as shown here:
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC
The resultset contains 184 rows (authors has 23 rows, and publishers has 8; therefore, 23 × 8 = 184). The first 11 rows look like Figure 4.
Figure 4 Cross Join
au_fname | au_lname | pub_name |
Akiko | Yokomoto | New Moon Books |
Akiko | Yokomoto | Binnet & Hardley |
Akiko | Yokomoto | Algodata Infosystems |
Akiko | Yokomoto | Five Lakes Publishing |
Akiko | Yokomoto | Ramona Publishers |
Akiko | Yokomoto | GGG&G |
Akiko | Yokomoto | Scootney Books |
Akiko | Yokomoto | Lucerne Publishing |
Johnson | White | New Moon Books |
Johnson | White | Binnet & Hardley |
Johnson | White | Algodata Infosystems |
However, if you add a WHERE clause (like WHERE authors.city = publishers.city), a cross join functions as an inner join—it uses the condition to filter all possible row combinations down to the ones you want (see Figure 5):
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
WHERE authors.city = publishers.city
ORDER BY au_lname DESC
Figure 5 WHERE Clause
au_fname | au_lname | pub_name |
Cheryl | Carson | Algodata Infosystems |
Abraham | Bennet | Algodata Infosystems |
Joshua Trupin is the Executive Editor of TechNet Magazine and MSDN Magazine.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.