PostgreSQL Joins

Why Joins Exist

Relational databases split data across multiple tables to avoid duplication: customers live in one table, orders in another, linked by a customer_id foreign key. Joins are how you stitch that data back together at query time, so a single result set can show a customer's name next to their order total.

The Join Family

PostgreSQL supports several join types, each with a different rule for which rows survive when a match is or is not found on both sides. Choosing the right join type is one of the most common sources of subtly wrong query results, so it pays to understand each one precisely.

• INNER JOIN — keeps only rows that match in both tables.
• LEFT JOIN — keeps all rows from the left table, filling unmatched right-side columns with NULL.
• RIGHT JOIN — the mirror image of LEFT JOIN, keeping all rows from the right table.
• FULL JOIN — keeps all rows from both tables, matched or not.
• CROSS JOIN — pairs every row on the left with every row on the right (no ON clause).

SELECT o.id AS order_id, c.name AS customer
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id;

Join Conditions

The ON clause defines how rows from each table are matched. It is usually an equality comparison between a primary key and a foreign key, but it can be any boolean expression, including comparisons on multiple columns at once.

SELECT s.name, r.region_name
FROM sales AS s
JOIN regions AS r
  ON s.region_id = r.id
 AND s.year = r.active_year;

SELECT p.name, c.category_name
FROM products AS p
JOIN categories AS c ON p.category_id = c.id
WHERE c.category_name = 'Electronics';