PostgreSQL JOINs: combining tables

Data is split across tables so each fact is stored once. A join puts it back together by matching rows, usually a foreign key against a primary key (orders.customer_id = customers.id).

INNER JOIN (or just JOIN): only rows that match on both sides.
LEFT JOIN: every row from the left table; columns from the right are NULL when there is no match.
RIGHT JOIN: the mirror image. Most people rewrite it as a LEFT JOIN.
FULL JOIN: every row from both sides, NULLs where either side is missing.
CROSS JOIN: every combination of rows (a Cartesian product).
Self join: a table joined to itself, e.g. employees to their managers.

Give tables short aliases (orders o) and prefix columns (o.id) to avoid “column reference is ambiguous” errors. USING (col) is shorthand when both columns share a name.

A classic trap: putting a condition on the right table of a LEFT JOIN in WHERE turns it back into an inner join. Put such conditions in the ON clause instead.

Syntax

SELECT a.col, b.col
FROM table_a AS a
[INNER | LEFT | RIGHT | FULL] JOIN table_b AS b
  ON a.key = b.key;

Examples

Edit the code and run it. Output is printed the way psql prints it.

7.1INNER JOIN: orders with customer names

SELECT o.id AS order_id, o.order_date, c.name AS customer, o.status
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

orders o gives the table a short alias.
ON c.id = o.customer_id pairs each order with its customer.
Every order has a customer, so all 20 orders appear.

7.2LEFT JOIN: keep rows without a match

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.id;

Oliver and Hannah have no orders but still appear, with order_id NULL. With an inner join they would vanish.

7.3Find rows with no match (anti-join)

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Keep everything with LEFT JOIN, then keep only the rows where the right side is missing. This answers “which customers never ordered?”.

7.4Joining four tables

SELECT o.id AS order_id,
       c.name AS customer,
       p.name AS product,
       oi.quantity,
       oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN customers   c  ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id = oi.product_id
WHERE o.id <= 3
ORDER BY o.id, p.name;

Joins chain: each JOIN adds a table to the result built so far. One order with three items produces three rows.

7.5Self join: who is my manager?

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

The same table appears twice with different aliases. LEFT JOIN keeps the top bosses whose manager_id is NULL.

7.6FULL JOIN and CROSS JOIN

SELECT d.name AS department, e.first_name
FROM departments d
FULL JOIN employees e ON e.dept_id = d.id
WHERE d.id IS NULL OR e.id IS NULL;

SELECT t.tier, c.country
FROM (VALUES ('free'), ('pro')) AS t(tier)
CROSS JOIN (VALUES ('India'), ('UK')) AS c(country);

The FULL JOIN shows the unmatched rows from both sides: Research (no employees) and Lucy (no department). The CROSS JOIN pairs every tier with every country; VALUES builds a small inline table.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 7.1

Show each order’s id, the customer’s name and the sales rep’s first_name as rep. Include orders without a rep.

Hint

JOIN customers, LEFT JOIN employees

Solution

SELECT o.id, c.name, e.first_name AS rep FROM orders o JOIN customers c ON c.id = o.customer_id LEFT JOIN employees e ON e.id = o.employee_id;

Exercise 7.2

List the name of products that have never been ordered.

Hint

LEFT JOIN order_items ... WHERE oi.product_id IS NULL

Solution

SELECT p.name FROM products p LEFT JOIN order_items oi ON oi.product_id = p.id WHERE oi.product_id IS NULL;

Exercise 7.3

Show each order id and its total (sum of quantity × unit_price), ordered by id.

Hint

JOIN-free: group order_items by order_id. Or join orders and group by o.id.

Solution

SELECT o.id, sum(oi.quantity * oi.unit_price) AS total FROM orders o JOIN order_items oi ON oi.order_id = o.id GROUP BY o.id ORDER BY o.id;

Exercise 7.4

Show each department name with its number of employees as headcount. Departments with nobody should show 0.

Hint

LEFT JOIN and count(e.id), not count(*)

Solution

SELECT d.name, count(e.id) AS headcount FROM departments d LEFT JOIN employees e ON e.dept_id = d.id GROUP BY d.name;