Cover by Lynn Beighley

Safari, the world’s most comprehensive technology and business learning platform.

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required

O'Reilly logo

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

image with no caption

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

image with no caption

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 with the relationship between ...

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required