SQL Joins Explained Through Office Lunch Politics

· 3 min read · Syed Omar Faruk Towaha
SQL Joins Explained Through Office Lunch Politics

Every SQL tutorial explains joins with Venn diagrams. Venn diagrams are fine, but they've never been passive-aggressive at a team lunch. Let's fix that.

We have two tables. employees is everyone on the team. orders is the lunch order the office manager collected this morning.

-- employees: id, name
-- orders:    employee_id, dish

Some employees ordered. Some forgot. And one order belongs to an employee ID that doesn't exist anymore, because Kevin left in March and still gets lunch somehow.

INNER JOIN: only people who ordered and still work here

SELECT e.name, o.dish
FROM employees e
INNER JOIN orders o ON o.employee_id = e.id;

Only rows that match on both sides. The people who forgot to order? Not in the result. Kevin's ghost order? Also gone. INNER JOIN is the office manager reading out the list: if your name and your order aren't both there, you don't exist today.

LEFT JOIN: everyone on the team, lunch or not

SELECT e.name, o.dish
FROM employees e
LEFT JOIN orders o ON o.employee_id = e.id;

Every employee appears. Those who didn't order get NULL in the dish column, which is SQL's way of saying "desk salad." This is the join you want 80% of the time: "show me all X, and their Y if they have one."

A classic trick: find people who didn't order.

SELECT e.name
FROM employees e
LEFT JOIN orders o ON o.employee_id = e.id
WHERE o.employee_id IS NULL;

RIGHT JOIN: every order, even the haunted ones

Same as LEFT JOIN, but keeps everything from the right table. Every order appears, including Kevin's, with NULL for the employee name. Honestly, most people just swap the table order and use LEFT JOIN, because reading right-to-left is for Arabic, Hebrew and people who enjoy confusion.

FULL OUTER JOIN: the HR audit

Everyone and every order. Employees without orders, orders without employees, and all the happy matches in between. It's the "let's reconcile everything" join, and it is how you discover that Kevin has been receiving lunch for seven months.

CROSS JOIN: chaos

Every employee paired with every order. Eight employees and seven orders give 56 rows. This is what happens when you forget the ON clause, and it's also how a query that should return 10,000 rows returns 10 billion and the database administrator appears behind you, silently.

Row counts
Same data, five joins, very different answers.

Two habits that save you

  1. Count before and after. If a LEFT JOIN returns more rows than the left table had, the right table has duplicates per key. Your "list of employees" now lists Priya three times because she ordered three samosas.
  2. Put filters in the right place. A condition on the right table in WHERE quietly turns your LEFT JOIN back into an INNER JOIN. Put it in the ON clause instead if you want to keep the unmatched rows.
-- keeps everyone, only matches vegetarian orders
LEFT JOIN orders o ON o.employee_id = e.id AND o.dish LIKE '%paneer%'

Joins aren't hard. They're just office politics with stricter syntax.

// related

// prefer the terminal?

Open the terminal blog and type read sql-joins-office-lunch-politics.