PostgreSQL Joins

Joins are the fundamental mechanism for combining rows across related tables in a relational database. PostgreSQL evaluates a join by comparing rows from two (or more) tables using a condition, then assembling a result set based on which rows matched and which did not — the join type governs that last part.

Syntax

The general form of a two-table join:

SELECT select_list
FROM table1
[JOIN_TYPE] table2
  ON table1.column = table2.column;

The INNER keyword is optional for INNER JOIN — bare JOIN means the same thing. The OUTER keyword is optional for LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

Practical Example

The following tables model an e-commerce domain and will be used across all join examples in this guide:

CREATE TABLE categories (
  category_id   SERIAL PRIMARY KEY,
  category_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
  product_id   SERIAL PRIMARY KEY,
  product_name VARCHAR(150) NOT NULL,
  category_id  INT REFERENCES categories(category_id),
  unit_price   NUMERIC(10, 2) NOT NULL
);

CREATE TABLE orders (
  order_id    SERIAL PRIMARY KEY,
  product_id  INT REFERENCES products(product_id),
  quantity    INT NOT NULL,
  ordered_at  TIMESTAMPTZ DEFAULT now()
);

INSERT INTO categories (category_name)
VALUES ('Electronics'), ('Furniture'), ('Apparel'), ('Seasonal');

INSERT INTO products (product_name, category_id, unit_price)
VALUES
  ('Noise-cancelling headphones', 1, 199.00),
  ('Standing desk',               2, 549.00),
  ('Merino wool sweater',         3,  89.00),
  ('USB-C hub',                   1,  49.00),
  ('Clearance bin item',          NULL, 5.00);  -- no category assigned

INSERT INTO orders (product_id, quantity)
VALUES (1, 2), (3, 1), (3, 3), (4, 5);
-- product 2 (standing desk) has no orders
-- product 5 (clearance bin) has no orders

INNER JOIN — matched rows only

SELECT
  p.product_name,
  c.category_name,
  o.quantity
FROM orders o
INNER JOIN products p ON p.product_id = o.product_id
INNER JOIN categories c ON c.category_id = p.category_id
ORDER BY o.order_id;

Returns only orders whose product belongs to a category. The “clearance bin item” and “standing desk” are absent because they have no orders or no category.

LEFT JOIN — preserve all orders

SELECT
  o.order_id,
  p.product_name,
  c.category_name
FROM orders o
LEFT JOIN products p ON p.product_id = o.product_id
LEFT JOIN categories c ON c.category_id = p.category_id
ORDER BY o.order_id;

Every order row is preserved. If a product has no category, category_name is NULL.

Anti-join — products with no orders

SELECT p.product_name
FROM products p
LEFT JOIN orders o ON o.product_id = p.product_id
WHERE o.order_id IS NULL;

Finds products in the catalog that have never been ordered.

Verify join column indexes

SELECT
  indexname,
  indexdef
FROM pg_indexes
WHERE tablename IN ('products', 'orders')
  AND indexdef ILIKE '%category_id%' OR indexdef ILIKE '%product_id%';

Join Types Comparison

Testing with Vela

Because joins span multiple tables, join-heavy query rewrites or index additions carry cross-table risk. Vela’s database branching lets you spin up a production-identical branch in seconds, try new join patterns or add indexes on foreign key columns, run EXPLAIN ANALYZE against real data volumes, and confirm performance before any changes land in production. This is especially valuable when profiling hash join vs. nested loop strategies on large tables.

Production Tips

• Always index foreign key columns used in join conditions — PostgreSQL does not create these automatically, and unindexed joins cause sequential scans.
• Qualify every column reference with a table alias in multi-table queries to eliminate ambiguity and make the intent clear to readers.
• Avoid SELECT * in joins — duplicate column names from two tables can silently overwrite each other in application result-set mapping.
• Use EXPLAIN ANALYZE to verify the planner chose the right strategy (hash join for large unsorted tables, merge join for pre-sorted data, nested loop for small tables).
• Moving a right-table filter from WHERE into the ON clause preserves LEFT JOIN semantics and prevents accidentally converting the join to an inner join.
• For anti-join patterns (NOT EXISTS, LEFT JOIN ... WHERE IS NULL), NOT EXISTS often generates a slightly more efficient plan on large datasets — benchmark both forms.