SQL

A JOIN combines rows from two tables based on a related column.

Tables for examples:

• employees(id, name, dept_id)
• departments(id, name)

Some employees might have null dept_id; some departments might have no employees.

1. INNER JOIN (default JOIN). Returns only rows with matches in BOTH tables.

SELECT e.name, d.name FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

Excludes employees without a dept and departments without employees.

2. LEFT JOIN (a.k.a. LEFT OUTER JOIN). All rows from left + matching from right; NULLs where no match.

SELECT e.name, d.name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Includes ALL employees. dept_id null → d.name is NULL.

3. RIGHT JOIN. Mirror of LEFT — all rows from right + matching from left. Rarely used; prefer LEFT JOIN with table order swapped.

4. FULL OUTER JOIN. All rows from BOTH tables; NULLs where no match. Gives the union of LEFT and RIGHT JOIN.

5. CROSS JOIN. Cartesian product — every row of one with every row of the other. m × n rows. Useful for generating combinations.

6. SELF JOIN. A table joined to itself, with aliases.

-- Find employees and their managers (same table)
SELECT e.name AS employee, m.name AS manager
FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

Visualizing as Venn diagrams:

• INNER = intersection
• LEFT = left circle (entire)
• RIGHT = right circle (entire)
• FULL = both circles entirely

Performance note: the optimizer often converts joins via index lookups. For OLTP with proper indexes, joining 100k-row tables via primary key is sub-millisecond.

Common pitfall: WHERE filters AFTER the join completes. To filter the right side before joining (to keep nulls from LEFT JOIN), put the condition in the ON clause:

LEFT JOIN departments d ON e.dept_id = d.id AND d.active = true

NOT

LEFT JOIN departments d ON e.dept_id = d.id WHERE d.active = true  -- this turns LEFT into INNER