SQL Joins in PostgreSQL

Joins combine rows from two or more tables based on a related column — this is the relational equivalent of MongoDB's embedded documents or a $lookup aggregation stage.

Assume two tables: employees(id, name, department_id) and departments(id, name).

INNER JOIN

Returns only rows that have a match in both tables.

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

An employee with no matching department (or a department with no employees) simply doesn't appear in the result.

LEFT JOIN (LEFT OUTER JOIN)

Returns every row from the left table, plus matching rows from the right — filling in NULL where there's no match.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
-- every employee appears, even ones with no department assigned

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror image of LEFT JOIN — every row from the right table, matched with the left where possible.

SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
-- every department appears, even ones with zero employees

FULL OUTER JOIN

Returns everything from both tables — matched where possible, NULL-padded where not.

SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

CROSS JOIN

Every row from the first table paired with every row from the second — the Cartesian product. No ON clause, and the result size is rows_in_a × rows_in_b.

SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;

Self Join

A table joined to itself — useful for hierarchical data, like employees and their managers.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Common Errors & Troubleshooting

• Row count multiplying unexpectedly after a join — if the "one" side of a one-to-many relationship (like departments joined to employees) gets duplicated per match, that's expected join behavior, not a bug — aggregate afterward if you need one row per department.
• A LEFT JOIN behaving like an INNER JOIN — a WHERE clause referencing the right table's column (e.g. WHERE d.name = 'Engineering') filters out the NULL-padded unmatched rows too, effectively turning it back into an inner join; move that condition into the ON clause instead if you want to keep unmatched left rows.
• Accidentally writing a CROSS JOIN — forgetting the ON condition on a regular join (or a stray comma between table names in the FROM clause) produces a full Cartesian product — always double check every join has its matching ON.

Key Takeaways

• INNER JOIN only keeps matched rows; LEFT/RIGHT/FULL OUTER joins keep unmatched rows too, filling gaps with NULL.
• LEFT JOIN is the one you'll reach for most often — "give me all of table A, plus whatever matches from B."
• A self join just means both sides of the JOIN reference the same table, usually with different aliases.