Joins Overview

Relational databases store data in normalized tables to avoid duplication: customer details live once in a customers table, product details live once in a products table, and an order simply references the customer_id and product_id it needs. A join is how you recombine that related data across tables for a single query.

Why joins exist

Consider a small e-commerce schema: customers, products, orders, and order_items. An order row only stores a customer_id, not the customer’s name or email — that would duplicate data across every order the same customer places. Likewise, each row in order_items stores a product_id rather than repeating the product’s full name and price on every line item. Joins let you ask questions that span these tables, such as “which customer placed order 1042, and what products were in it?”, in a single query.

General syntax

The general shape of a join

SQL

SELECT columns
FROM table_a
JOIN_TYPE table_b ON table_a.some_column = table_b.matching_column;

The ON clause tells PostgreSQL how rows in one table relate to rows in the other — almost always a foreign key on one side matching a primary key on the other.

Orders joined to the customers who placed them

SQL

SELECT o.order_id, o.order_date, c.first_name, c.last_name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

Order line items joined to the products they reference

SQL

SELECT oi.order_id, p.name, oi.quantity, p.price
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id;

Join types covered in this section

Join type	Returns
`INNER JOIN`	Only rows that have a match on both sides (the default for a bare `JOIN`)
`LEFT JOIN`	All rows from the left table, with NULLs for unmatched right-side columns
`RIGHT JOIN`	All rows from the right table, with NULLs for unmatched left-side columns
`FULL JOIN`	All rows from both tables, matched where possible, NULLs elsewhere
`CROSS JOIN`	Every combination of rows from both tables (the Cartesian product)
Self join	A table joined to itself, typically to relate rows to other rows in the same table

Note

Every join type shares the same basic mental model: PostgreSQL walks through candidate row pairs and decides, using your ON condition, which pairs belong together. What differs between join types is only what happens to rows that don’t find a match. The following pages work through each type with the same sample schema.

Joins let you query across normalized tables without duplicating data
The ON clause defines how rows from each table are related
Which join type you pick determines what happens to unmatched rows