PostgreSQL JOINs

Real-world databases store data across multiple related tables to avoid repetition. A JOIN combines rows from two or more tables based on a related column. For example, an orders table might store a customer ID, while the actual customer details like name and email live in a separate customers table. A JOIN connects these two tables to produce a complete result.

Why Multiple Tables?

Storing all information in one big table leads to problems:

• Repeated data wastes space
• Updating one piece of information (like a customer's address) requires updating many rows
• Inconsistencies develop when some rows are updated but others are not

Splitting data across tables and linking them with keys solves these problems. JOINs are the mechanism to reunite that data when needed.

Setting Up Example Tables

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    product VARCHAR(100),
    amount NUMERIC(8, 2)
);

INSERT INTO customers (name, city) VALUES
    ('Alice', 'New York'),
    ('Bob', 'Chicago'),
    ('Carol', 'Houston'),
    ('Dan', 'Phoenix');

INSERT INTO orders (customer_id, product, amount) VALUES
    (1, 'Laptop', 1200.00),
    (1, 'Mouse', 25.00),
    (2, 'Keyboard', 75.00),
    (3, 'Monitor', 400.00),
    (5, 'Webcam', 90.00);  -- customer_id 5 does not exist in customers

INNER JOIN

INNER JOIN returns only rows where a match exists in both tables. Rows from either table that have no matching row in the other are excluded.

SELECT customers.name, orders.product, orders.amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

The ON clause specifies the condition that connects the tables — in this case, the order's customer_id must equal the customer's id. Dan (customer 4) has no orders, so he does not appear. The order for customer_id 5 has no matching customer, so it is also excluded.

Using Table Aliases

Table names can be shortened with aliases to reduce repetition:

SELECT c.name, o.product, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

o is an alias for orders and c for customers. This is the standard style when writing JOIN queries.

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns all rows from the left table (the table listed first, after FROM), and matching rows from the right table. If no match exists, NULL fills the right table's columns.

SELECT c.name, o.product, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

All four customers appear in the result. Dan, who has no orders, still appears with NULL in the product and amount columns. This is useful for finding customers who have never placed an order.

Finding Unmatched Rows

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

By filtering for rows where the right table's primary key is NULL, only customers with no orders are returned.

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table and matched rows from the left. Unmatched left-table rows appear as NULL.

SELECT c.name, o.product
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

All five orders appear. The order for the non-existent customer_id 5 appears with NULL in the customer name. RIGHT JOIN is less commonly used than LEFT JOIN — most queries can be rewritten as LEFT JOIN by swapping the table positions.

FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables. When a row has no match in the other table, the missing side appears as NULL.

SELECT c.name, o.product
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

The result includes: all customers (including Dan with no orders) and all orders (including the orphaned order for customer_id 5).

CROSS JOIN

CROSS JOIN returns every possible combination of rows from both tables. It does not use an ON clause.

SELECT c.name, p.size
FROM colors c
CROSS JOIN sizes p;

If colors has 3 rows and sizes has 4 rows, the result has 12 rows (3 × 4). CROSS JOIN is useful for generating combinations, such as all possible product variants.

SELF JOIN

A SELF JOIN joins a table to itself. This is useful when rows in the same table are related to each other, such as an employee-manager relationship where both the employee and the manager exist in the same table.

CREATE TABLE staff (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

INSERT INTO staff (name, manager_id) VALUES
    ('Director', NULL),
    ('Manager A', 1),
    ('Manager B', 1),
    ('Employee X', 2),
    ('Employee Y', 3);

SELECT e.name AS employee, m.name AS manager
FROM staff e
LEFT JOIN staff m ON e.manager_id = m.id;

Each employee row is joined to the manager row in the same table using manager_id. The Director has no manager, so NULL appears for their manager column.

Joining More Than Two Tables

SELECT c.name, o.product, p.category
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id;

Multiple JOINs are chained together. Each JOIN adds another table to the query. The order of JOINs affects readability but not the final result for INNER JOINs.

JOIN Types Summary

Key Points

• JOINs combine rows from multiple tables based on a matching condition defined in the ON clause.
• INNER JOIN returns only matched rows; unmatched rows from either table are excluded.
• LEFT JOIN keeps all rows from the left table, filling unmatched right-side columns with NULL.
• RIGHT JOIN keeps all rows from the right table.
• FULL OUTER JOIN returns all rows from both tables.
• Table aliases shorten column references and improve readability.
• Multiple tables can be joined in a single query by chaining JOIN clauses.