Learn SQL with PostgreSQL - Advanced Table Joins (Relationships Between Tables)

#Introduction

Welcome to episode 6 of the Learn SQL with PostgreSQL series! So far we've learned to manage a single table: creating it, filling it, and processing it. But now it's time for relational databases to show their true power: combining data from many tables. This is what distinguishes a relational database from a mere collection of spreadsheet files.

Imagine you have an orders table and a customers table. An order only stores customer_id, not the customer's full name. To display "Order #123 belonging to Budi Santoso", you have to combine both tables based on the foreign key relationship we built in episode 2. That's the job of JOIN.

In this episode, we'll cover the concept of joins thoroughly: INNER JOIN to take matching data, LEFT and RIGHT OUTER JOIN to preserve one side, FULL OUTER JOIN for all data, CROSS JOIN for Cartesian combinations, and SELF JOIN for hierarchical data. We'll also learn to combine three or more tables in a single query.

#Basic Join Concepts

JOIN is an operation that combines rows from two tables based on a specific condition — usually a foreign key match. The result is new rows combining columns from both tables.

The easiest visualization is the circle diagram:

• INNER JOIN: the intersection of two circles — only rows that match on both sides.
• LEFT JOIN: the full left circle + the intersection — all left rows, only the matching right data.
• RIGHT JOIN: the opposite of LEFT — all right rows.
• FULL JOIN: the complete union of both circles.

SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.id;

The ON clause determines the joining condition — here it matches customer_id on orders with id on customers. The JOIN keyword itself is identical to INNER JOIN.

#INNER JOIN: Only Matching Data

INNER JOIN is the most common type of join. It only returns rows that have a match in both tables. Orders without a matching customer won't appear.

SELECT
    o.id AS order_id,
    c.full_name AS customer,
    o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
ORDER BY o.total DESC;

#LEFT, RIGHT, and FULL OUTER JOIN

#LEFT OUTER JOIN

LEFT JOIN takes ALL rows from the left table, plus the matching data from the right table. If there's no match, the right columns are filled with NULL. This is the most common pattern for finding "data without a partner":

SELECT c.full_name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.full_name;

Notice: customers who have never ordered will still appear with a order_id of NULL. This answers the query "show all customers along with their orders — including those who have never ordered".

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

The LEFT JOIN + WHERE right side IS NULL pattern is a classic idiom for finding left rows that have no partner.

#RIGHT and FULL OUTER JOIN

RIGHT JOIN is the mirror image of LEFT JOIN: all right rows are preserved, left is filled with NULL if there's no match. FULL JOIN combines both: all rows from both sides, with NULL on the side that doesn't match.

SELECT c.full_name, o.id AS order_id
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;
 
SELECT c.full_name, o.id AS order_id
FROM customers c
FULL JOIN orders o ON o.customer_id = c.id;

#CROSS JOIN: Cartesian Combination

CROSS JOIN produces the combination of every row in the left table with every row in the right table — without an ON condition. If the left table has 10 rows and the right has 5, the result is 50 rows.

SELECT p.name, s.size_label
FROM products p
CROSS JOIN product_sizes s;

The example above is suitable for generating a product-per-size list. But be careful: CROSS JOIN can explode easily — a 1000×1000 table produces 1 million rows.

#SELF JOIN: A Table Joining Itself

SELF JOIN joins a table with itself. This odd-sounding name is very useful for hierarchical data — e.g. an employees table with a manager_id column pointing to another employee, or categories that have a parent category.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

To display each employee's name along with their manager's name, we join the table with itself using different aliases:

SELECT
    e.name AS karyawan,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY m.name NULLS FIRST;

Notice two things: the same table is joined twice with the aliases e (employee) and m (manager), and we use LEFT JOIN so that employees without a manager (the CEO) still appear. This SELF JOIN pattern is also used for multi-level category structures and social networks ("friends of friends" relationships).

#Multi-Table Joins: Three Tables or More

The real world rarely involves only two tables. An order consists of an order header, order items, and products. To display the product name on each order, we combine three tables at once:

SELECT
    o.id AS order_id,
    c.full_name AS customer,
    p.name AS product,
    oi.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 123;

Each JOIN is added sequentially, and each has an ON condition connecting its new table to the previous result. There's no hard limit on the number of joins, but the more joins, the higher the execution cost — in episode 15 we'll learn to analyze their performance.

#Common Join Mistakes

#Closing

In this episode 6 we've mastered joins in all their forms: INNER JOIN for matching data, LEFT/RIGHT/FULL JOIN to preserve one or both sides, CROSS JOIN for Cartesian combinations, SELF JOIN for hierarchical data, and the multi-table join technique for combining many tables in a single query.

Key takeaways:

• INNER JOIN takes the intersection; LEFT JOIN preserves the left table.
• The LEFT JOIN ... WHERE right side IS NULL pattern finds data without a partner.
• SELF JOIN is the key to hierarchical data (employee-manager, multi-level categories).
• Always write an ON condition — without it the query becomes a dangerous CROSS JOIN.
• Understand the direction of the relationship before aggregating multi-join results.

In episode 7, we'll expand our query capabilities: Subqueries, Set Operations & Expressions — from subqueries in WHERE, FROM, and SELECT, the CASE WHEN branching logic, NULL-handling functions like COALESCE and NULLIF, to the set operations UNION, INTERSECT, and EXCEPT. You'll see just how expressive SQL really is.