Greg Heo

SQL JOINs, part 2

The exploration of SQL JOIN continues with the oddballs: CROSS and OUTER.

Last time, we started our discussion of SQL joins and covered the common cases: LEFT, INNER, and RIGHT. Today, we’ll cover the lesser-used CROSS and OUTER joins.

The OUTER limits

Here’s a sample data set:

Products
Product IDProduct name
100Pencil
101Pen
103Paperclips
Prices
Product IDProduct price
1000.25
1010.40
1020.75


Let’s review some things from last week. The directional joins (LEFT and RIGHT) mark one table as the primary one. INNER join is like a conjunction of left and right where the record and join have to exist in both tables.

SELECT * FROM products
INNER JOIN prices ON (products.product_id=prices.product_id)
Product IDProduct nameProduct IDProduct Price
100Pencil1000.25
101Pen1010.40

OUTER join is like saying “either LEFT or RIGHT” (a “disjunction” in tech-speak). The result set will contain data from either just the left table, just the right, or both:

SELECT * FROM products
OUTER JOIN prices ON (products.product_id=prices.product_id)
Product IDProduct nameProduct IDProduct Price
100Pencil1000.25
101Pen1010.40
NULLNULL1020.75
103PaperclipsNULLNULL

Notice we get a full set of four records. For products 100 and 101 we have a full set of information: ID, name, price. Product 102 looks like a RIGHT join with just a price and NULL for the name. Product 103 looks like a LEFT join with a name and no price.

CROSS Combinations

We’re back at the beginning in a way: a cross join is really the mother of all joins. Let’s look at an example:

SELECT * FROM products CROSS JOIN prices;
Product IDProduct nameProduct IDProduct Price
100Pencil1000.25
100Pencil1010.40
100Pencil1020.75
101Pen1000.25
101Pen1010.40
101Pen1020.75

That’s just a snippet from the results. Note that every record in the left table will be joined to every record in the right table.

Why is this useful? It’s the first step to the other joins. To turn these results into an inner join, for example, we just need to match up the two product IDs and discard the rows where the IDs don’t match.

That sounds useful for the SQL engine itself, but what about useful to humans? Let’s say you have one table listing every entree available at a restaurant and another table listing all the wines. Wouldn’t it be nice to pull out a list of every entree + wine combination? Yes it would!

SELECT * FROM entrees CROSS JOIN wines;

In Closing

That concludes our discussion of SQL JOINs. Let’s hear your own JOIN tips and horror stories!