Joins:
combining tables

In Module 00 you queried single tables. Maya hits her first snag here. The schema was already split: an employee's dept_id is just a number (10, 20), and the human-readable dept_name "Engineering" lives in a separate departments table. That split is deliberate. It is called normalization: each fact is stored exactly once, in one place. The department name "Engineering" appears a single time, not copied onto every employee row.

Normalization keeps data consistent (rename the department once and every employee inherits it) and small (no repeated text). The trade-off is that answering Maya's question, "who works in Engineering?", now requires pulling rows from two tables and matching them up on the shared key. That matching operation is a join. It is the price you pay for clean storage, and it is the single most-used feature of SQL after SELECT itself.

Before any syntax, Maya draws the two tables on a sticky note. This habit will save her more debugging time than any single keyword. Here is the data she is working with for the whole module.

-- departments: 4 rows. Operations (40) has nobody in it yet.
 dept_id | dept_name
---------+-------------
      10 | Engineering
      20 | Sales
      30 | Marketing
      40 | Operations

-- employees: 6 rows. Frank's dept_id is NULL (not assigned yet).
 emp_id | name  | dept_id | salary
--------+-------+---------+--------
      1 | Alice |      10 | 185000
      2 | Bob   |      10 | 120000
      3 | Carol |      20 | 135000
      4 | Dan   |      20 |  98000
      5 | Eve   |      30 | 105000
      6 | Frank |  (null) |  72000

Look at the two odd ones out, because every join type is really a story about them. Frank has no department (his dept_id is NULL), and Operations has no employees (no row in employees points at dept 40). A join walks one table, and for each row asks "which rows in the other table share my key?" The only real decision is what to do with rows that find no partner: Frank and Operations. Keep them or drop them. That single choice is the difference between every join you will ever write.

Maya's actual task was "list every employee next to their department name." The most common join, INNER JOIN, gives exactly that: rows that have a partner on both sides, and nothing else.

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- INNER is the default; "JOIN" alone means the same thing

 name  | dept_name
-------+-------------
 Alice | Engineering
 Bob   | Engineering
 Carol | Sales
 Dan   | Sales
 Eve   | Marketing

Five rows, not six, and not seven. Frank vanished because his NULL dept_id matches no department (and NULL never equals anything, even another NULL). Operations vanished because no employee points at dept 40. An inner join keeps a row only when both sides have a partner. The leftovers are silently dropped, which is exactly why Maya's first headcount came up one short and confused her until she remembered Frank.

Maya's manager corrects the request: "I want every employee, even ones without a department yet." That word "every" is the signal for a LEFT JOIN. It keeps every row from the left table, and fills in NULL wherever the right table has no match.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- "LEFT OUTER JOIN" is the full name; OUTER is optional

 name  | dept_name
-------+-------------
 Alice | Engineering
 Bob   | Engineering
 Carol | Sales
 Dan   | Sales
 Eve   | Marketing
 Frank | (null)

Now all six employees survive. Frank stays, but his dept_name is NULL because there was nothing to fill it with. The left table (employees, the one named right after FROM) is protected: every one of its rows appears, matched or not. Operations is still gone, because it lives only on the right side and the right side has no such protection in a left join.

This is also how Maya answers "which employees are not assigned to a department?" She keeps the left join and filters for the rows where the right side came back empty.

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;   -- only rows that found no partner
-- result: just Frank

A RIGHT JOIN is a LEFT JOIN looking in the mirror: it protects the right table instead. Maya needs it to answer the opposite question, "show every department, even the empty ones," so Operations finally appears.

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

 name  | dept_name
-------+-------------
 Alice | Engineering
 Bob   | Engineering
 Carol | Sales
 Dan   | Sales
 Eve   | Marketing
 (null)| Operations

Operations now shows up with a NULL employee name. Frank is gone again, because the left side is no longer protected. In practice most people just swap the table order and use a LEFT JOIN, since left joins read more naturally, but you should recognize RIGHT JOIN when you meet it.

A FULL OUTER JOIN protects both sides at once. Nobody gets dropped: every unmatched left row and every unmatched right row survives, padded with NULLs. It is how Maya audits both problems in a single query.

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

 name  | dept_name
-------+-------------
 Alice | Engineering
 Bob   | Engineering
 Carol | Sales
 Dan   | Sales
 Eve   | Marketing
 Frank | (null)        -- employee with no department
 (null)| Operations    -- department with no employees

A CROSS JOIN has no ON clause at all. It pairs every row of the left with every row of the right, the full Cartesian product. With 6 employees and 4 departments that is 6 × 4 = 24 rows. It is rarely what you want by accident, but useful on purpose for generating combinations, like every employee against every possible shift.

SELECT e.name, s.shift
FROM employees e
CROSS JOIN (VALUES ('morning'), ('evening')) AS s(shift);
-- 6 employees x 2 shifts = 12 rows

Sometimes the two tables you want to match are the same table. Suppose each employee row also carries a manager_id that points at another employee's emp_id. To list each person next to their manager's name, Maya joins employees to itself, giving the two copies different aliases so the database can tell them apart.

SELECT e.name      AS employee,
       m.name      AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
-- e is "the worker", m is "the same table, seen as managers"
-- LEFT JOIN so the top boss (no manager) is not dropped

The trick is purely the aliases: e and m are two windows onto one table. A LEFT JOIN is the safe default here so that the person at the top, whose manager_id is NULL, still appears with a NULL manager instead of disappearing. Self-joins handle any "row relates to another row in the same table" question: managers, reply-to comments, prerequisite courses.

There are three ways to tell SQL which columns make a match. They differ in how much they trust your naming, and that trust is exactly where bugs hide.

-- 1. ON: most explicit, works for any condition. Always safe.
SELECT * FROM employees e JOIN departments d ON e.dept_id = d.dept_id;

-- 2. USING: shorthand when the column name is identical on both sides.
--    Also collapses the two dept_id columns into one in the output.
SELECT * FROM employees e JOIN departments d USING (dept_id);

-- 3. NATURAL: auto-matches on EVERY same-named column. Convenient, risky.
SELECT * FROM employees NATURAL JOIN departments;

Reach for ON by default. It is explicit, it handles conditions beyond simple equality, and a reader can see precisely what is matched. USING is a tidy shortcut when the key column has the exact same name on both sides. NATURAL JOIN looks elegant but matches on every identically named column automatically, so the day someone adds a created_at column to both tables, the join silently starts matching on that too and the results quietly change.

Often the interesting question is about absence: departments with no employees, customers with no orders. That is an anti-join, and the classic recipe is an outer join plus an IS NULL filter on the side that should have matched.

SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;   -- no employee matched this department
-- result: Operations

Keep every department with the left join, then keep only the ones where the employee side came back empty. The same shape, with the tables flipped, finds employees with no department (Frank). Read it as "keep all of A, then throw away the ones that did match B, leaving only the unmatched."

Sit in Maya's chair. Build the same departments and employees tables (include dept 40 Operations with no staff, and Frank with a NULL dept_id). Before running each query, predict how many rows come back and which of Frank or Operations survives. The gap between your guess and the result is the lesson.

• Write the INNER JOIN of employees and departments. Confirm it returns 5 rows and explain why Frank and Operations are both absent.
• Change it to a LEFT JOIN. Verify Frank reappears with a NULL department, for 6 rows total.
• Swap to a RIGHT JOIN, then a FULL OUTER JOIN. Note exactly which of Frank and Operations shows up in each.
• Find every employee with no department using LEFT JOIN ... WHERE d.dept_id IS NULL. You should get just Frank.
• Find every department with no employees (an anti-join). You should get just Operations.
• Add a manager_id column, point a few employees at a manager, and self-join the table to list each person beside their manager's name. Use a LEFT JOIN so the top boss is not dropped.
• Take your LEFT JOIN and add WHERE d.dept_name = 'Sales'. Watch it silently behave like an inner join, then fix it by moving that condition into the ON clause.
• Write a CROSS JOIN of employees against two shift values and confirm the row count is exactly employees × 2.