Joins in 750 words or less

 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

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 is a matching row between the joined tables. The ON clause is used to supplement the data rather than filter it. There are three types of outer joins: left, right, and full. These join types indicate which table the main data comes from. Left Outer Joins When you use a left outer join to combine the authors and publishers tables, the result will be a list of all authors with a publisher's name column. If a publisher exists in the author's city, it's listed. Otherwise, the field is set to NULL.

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

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.

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

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. 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.

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 X 8 = 184). The first ten rows look like this:

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.

USE pubs SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers WHERE authors.city = publishers.city ORDER BY au_lname DESC

au_fname	au_lname	pub_name
Cheryl	Carson	Algodata Infosystems
Abraham	Bennet	Algodata Infosystems