SQL Joins

A join builds one combined table from two by pairing rows on a shared column — the join key. The join *type* decides what happens to rows that have no match on the other side.

Sample tables

Employee              Department
 name  | dept_id       dept_id | dept_name
-------+---------      --------+------------
 Alice |   10            10     | Engineering
 Bob   |   20            20     | Sales
 Carol |   30            40     | Legal

The join key is dept_id. Note that Carol's dept 30 has no matching department, and Legal (40) has no matching employee — those are the interesting rows.

The four join types

1. 1INNER JOIN — keep only rows that match on both sides: Alice-Engineering, Bob-Sales. Carol and Legal vanish.
2. 2LEFT JOIN — keep every left (Employee) row; unmatched right columns become NULL: adds Carol-NULL.
3. 3RIGHT JOIN — keep every right (Department) row; unmatched left columns become NULL: adds NULL-Legal.
4. 4FULL OUTER JOIN — keep everything from both, NULL-filling each unmatched side: both Carol-NULL and NULL-Legal.

SELECT e.name, d.dept_name
FROM Employee e
INNER JOIN Department d ON e.dept_id = d.dept_id;
-- Alice|Engineering, Bob|Sales   (2 rows)

-- swap INNER for LEFT / RIGHT / FULL OUTER to keep the unmatched rows

CROSS JOIN — every pairing

A cross join ignores any key and pairs *every* row with *every* row — the Cartesian product. With 3 employees and 3 departments that is 3 × 3 = 9 rows.

SELECT e.name, d.dept_name FROM Employee e CROSS JOIN Department d;  -- 9 rows