SQL Joins

Introduction

I've been building Rails apps with PostgreSQL, and Active Record hides a lot of the SQL from you. That was fine until I needed a report that listed every user including the ones with no orders, and my query kept silently dropping them.

The setup

Two tables. users has four people. orders has five orders, each pointing at a user through user_id, which is a foreign keya column that references the primary key of another table.

CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER,
  item    TEXT NOT NULL
);

Two rows are deliberately awkward, because they're where the join types differ:

• Bob is a user with no orders.
• Order 105 belongs to user_id = 9, a user who no longer exists.

Every join starts the same way: for each row on the left, find the rows on the right where orders.user_id = users.id. The join type decides what happens to the rows that don't have a match.

INNER JOIN

Only rows with a match on both sides. Bob is gone (no orders), order 105 is gone (no user). This is what you get if you just write JOIN.

SELECT users.name, orders.item
FROM users
INNER JOIN orders ON orders.user_id = users.id;

LEFT JOIN

Every row from the left table, matched where possible. Bob shows up with NULL in the order columns. Order 105 is still dropped, because it's on the right.

SELECT users.name, orders.item
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

This is the one I needed. "All users, with their orders if they have any."

RIGHT JOIN

The mirror image. Every row from the right table. Order 105 shows up with NULL user columns, Bob is dropped. In practice you'll rarely see this one, because you can always swap the table order and write it as a LEFT JOIN.

SELECT users.name, orders.item
FROM users
RIGHT JOIN orders ON orders.user_id = users.id;

FULL OUTER JOIN

Everything from both sides. Bob and order 105 appear, each padded with NULLs. Useful for finding mismatches: a WHERE users.id IS NULL OR orders.id IS NULL on top of this gives you exactly the orphaned rows.

SELECT users.name, orders.item
FROM users
FULL OUTER JOIN orders ON orders.user_id = users.id;

Try it

Click through the four joins. The Venn diagram shows which side is kept, the source tables cross out the rows that get dropped, and the result table shows the NULL padding.

SELECT users.name, orders.item
FROM users
INNER JOIN orders
  ON orders.user_id = users.id;

Two gotchas

Filtering a LEFT JOIN in WHERE

This looks like it should give you all users with their cheap orders, but it quietly turns into an inner join:

SELECT users.name, orders.item
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.item = 'Gin';

Bob's row has NULL for orders.item, and NULL = 'Gin' is not true, so WHERE throws her out. If you want to keep unmatched rows, put the condition in the ON clause instead:

LEFT JOIN orders ON orders.user_id = users.id AND orders.item = 'Gin'

Row multiplication

Aldo has two orders, so he shows up twice in the result. Joins produce one row per match, not one row per left-side row. If you COUNT(*) or SUM after a join without thinking about this, your numbers will be inflated. Group by the user or aggregate in a subquery first.

How this maps to Rails

For the record, this is what the same thing looks like in Active Record:

User.joins(:orders)       # INNER JOIN
User.left_joins(:orders)  # LEFT JOIN
User.includes(:orders)    # separate query, then stitched in Ruby

includes isn't a join at all, which is why it never drops anybody. Knowing the SQL underneath made it obvious which one I actually wanted.