Head First SQL

Chapter 10. Outer Joins, Self-Joins, and Unions: New maneuvers

You only know half of the story about joins. You’ve seen cross joins that return every possible row, and inner joins that return rows from both tables where there is a match. But what you haven’t seen are outer joins that give you back rows that don’t have matching counterparts in the other table, self-joins which (strangely enough) join a single table to itself, and unions that combine the results of queries. Once you learn these tricks, you’ll be able to get at all your data exactly the way you need to. (And we haven’t forgotten about exposing the truth about subqueries, either!)

Cleaning up old data

You can get that information with an outer join.

Let’s take a look at what outer joins do, and then we’ll show you how to find those professions you aren’t using anymore.

An outer joins returns all rows from one of the tables, along with matching information from another table.

With an inner join, you’re comparing rows from two tables, but the order of those two tables doesn’t matter.

Let’s briefly review what the equijoin does. We get all the columns that match toy_id from both tables. It matches up the toy_id that exists in both tables:

It’s about left and right

By comparison, outer joins have more to do ...