JOINs — Complete Guide

Understanding JOINs

A JOIN combines rows from two or more tables based on a related column. JOINs are the mechanism that makes the relational model so powerful — instead of duplicating data across tables, you store it once and combine it at query time.

Understanding the different join types — INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN — is essential for writing correct relational queries.

How Joins Work

When you join two tables, PostgreSQL conceptually creates a Cartesian product of all rows (every row from Table A paired with every row from Table B), then keeps only the pairs that satisfy the ON condition. Different join types differ in how they handle pairs where there is no match.

INNER JOIN — The Most Common Join

INNER JOIN (or just JOIN) returns only the rows where the join condition is satisfied in both tables. If a row in Table A has no matching row in Table B, it is excluded from the result.

-- Get all orders with customer names
SELECT
    c.full_name,
    c.email,
    o.order_id,
    o.ordered_at,
    o.total_amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;

If a customer has never placed an order, they will not appear in this result. If an order somehow has a customer_id that does not exist in customers, it will also be excluded.

The ON Clause

The ON clause specifies the join condition. It is usually a foreign key relationship:

-- Explicit: table.column = table.column
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id

-- USING: shorthand when both columns have the same name
FROM orders o
JOIN customers c USING (customer_id)

USING (column) is equivalent to ON a.column = b.column but removes the duplicate column from the output.

Table Aliases

Always use short aliases when joining multiple tables. Without aliases, queries become unwieldy:

-- Unreadable without aliases
SELECT customers.full_name, orders.order_id
FROM customers INNER JOIN orders ON orders.customer_id = customers.customer_id;

-- Clean with aliases
SELECT c.full_name, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Aliasing the table on the same line as FROM or JOIN keeps the query compact and readable.

LEFT JOIN — Preserving the Left Table

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left (first) table, and the matching rows from the right table. Where there is no match, the right table columns are filled with NULL.

-- All customers, with their order count (0 if they have never ordered)
SELECT
    c.full_name,
    c.email,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name, c.email
ORDER BY order_count DESC;

Customers with no orders get COUNT(o.order_id) = 0 because COUNT of NULLs is zero.

Anti-Join: Finding Missing Relationships

A classic use of LEFT JOIN is finding rows in one table with no corresponding row in another:

-- Products that have never been ordered
SELECT p.product_id, p.name, p.category
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_id IS NULL
ORDER BY p.name;

Since oi.order_id will be NULL for products with no matching order items, the WHERE oi.order_id IS NULL filter keeps only those unmatched products.

RIGHT JOIN — Preserving the Right Table

RIGHT JOIN is the mirror of LEFT JOIN — it returns all rows from the right table. In practice, RIGHT JOIN is rarely used because you can always rewrite it as a LEFT JOIN by swapping the table order:

-- These two queries produce equivalent results
SELECT c.full_name, o.order_id
FROM orders o
RIGHT JOIN customers c ON c.customer_id = o.customer_id;

-- Equivalent LEFT JOIN (preferred)
SELECT c.full_name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

Most developers prefer LEFT JOIN for consistency.

FULL OUTER JOIN — All Rows from Both Tables

FULL OUTER JOIN returns all rows from both tables. Where there is no match on either side, the missing columns are NULL. Use it when you need to see all data regardless of whether a match exists.

-- All employees and all departments, showing gaps in both
SELECT
    COALESCE(e.full_name,  '(no employee)') AS employee,
    COALESCE(d.name,        '(no dept)')    AS department
FROM employees e
FULL OUTER JOIN departments d ON d.dept_id = e.dept_id
ORDER BY department, employee;

This reveals:

• Employees assigned to a non-existent department
• Departments with no employees

CROSS JOIN — The Cartesian Product

CROSS JOIN produces every possible combination of rows from two tables. If Table A has 5 rows and Table B has 4 rows, the result has 20 rows:

-- Generate all possible size/color combinations for a product configurator
SELECT s.size_label, c.color_name, c.hex_code
FROM sizes s
CROSS JOIN colors c
ORDER BY s.sort_order, c.color_name;

CROSS JOIN is intentional when you genuinely need all combinations. Be careful — joining two tables with thousands of rows each produces millions of output rows.

Multi-Table JOINs

Real-world queries often join three or more tables. Chain your JOIN clauses logically:

-- Complete order receipt: customer info + order lines + product details
SELECT
    c.full_name,
    c.email,
    o.order_id,
    o.ordered_at,
    p.name                              AS product,
    p.category,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price         AS line_total
FROM   customers   c
JOIN   orders      o  ON o.customer_id  = c.customer_id
JOIN   order_items oi ON oi.order_id    = o.order_id
JOIN   products    p  ON p.product_id   = oi.product_id
WHERE  o.order_id = 42
ORDER BY p.name;

PostgreSQL's query planner automatically determines the most efficient join order. You do not need to write the tables in the "correct" order — the planner uses statistics to find the optimal execution strategy.

Self-Joins

A self-join joins a table to itself. This is useful for hierarchical data or comparing rows within the same table:

-- Find pairs of products in the same category with similar prices
SELECT
    a.name   AS product_a,
    b.name   AS product_b,
    a.price  AS price_a,
    b.price  AS price_b,
    ABS(a.price - b.price) AS price_diff
FROM products a
JOIN products b ON a.category = b.category
               AND a.product_id < b.product_id  -- avoid duplicates and self-pairs
               AND ABS(a.price - b.price) < 10
ORDER BY price_diff;

-- Employee hierarchy: find each employee and their manager
SELECT
    e.full_name  AS employee,
    m.full_name  AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY m.full_name NULLS FIRST, e.full_name;

The a.product_id < b.product_id condition prevents (A, B) and (B, A) from both appearing and prevents self-pairs (A, A).

Performance Considerations

JOINs are efficient when the joined columns are indexed. PostgreSQL uses three join algorithms internally:

• Nested Loop: fast for small tables or when one side is very selective
• Hash Join: fast for large tables when an equality condition is used
• Merge Join: fast for large, pre-sorted datasets

The query planner chooses automatically based on table size statistics. When joins are slow, check that the foreign key columns have indexes (more on this in the Indexes lesson).

JOINs are the core mechanism of relational databases. Mastering all five types and understanding anti-joins, self-joins, and multi-table chains will let you express any query that spans multiple entities. Next, we go deeper with subqueries and Common Table Expressions.