Introduction to JOINs

A JOIN is a SQL operation that combines rows from two or more tables based on a related column — typically a primary key in one table matching a foreign key in another. JOINs are needed because the relational model stores data in normalised form — each entity in its own table — and JOINs are the mechanism for reassembling that data at query time.

Without JOINs, you could only query one table at a time. You could retrieve an order's customer_id from the orders table, but not the customer's name — that lives in the customers table. JOINs bridge the tables: SELECT o.order_id, c.first_name FROM orders AS o JOIN customers AS c ON o.customer_id = c.customer_id retrieves both the order data and the customer's name in a single query by matching the customer_id foreign key in orders with the customer_id primary key in customers.

JOINs are the relational model's answer to data integration. Data is stored normalised — each fact in one place — and JOINs combine it at query time without duplicating storage. This design means updating a customer's name requires changing one row in one table, and every query that joins to that customer automatically reflects the update. Without JOINs (or their equivalent), you would need to store customer names redundantly in every table that references customers — reintroducing all the update anomalies that normalisation was designed to eliminate.

INNER JOIN returns only rows where the join condition is satisfied in both tables. If a customer has no orders, they do not appear in the result of customers INNER JOIN orders. If an order references a customer_id that does not exist in the customers table, that order does not appear either. INNER JOIN is the intersection — only the rows that have a match on both sides.

LEFT JOIN returns every row from the left table, whether or not it has a match in the right table. Rows from the left table that have no match in the right table appear with NULL values for all right-table columns. LEFT JOIN is "keep everything from the left, add right-side data where available." A customer with no orders appears with NULL for all order columns. A customer with three orders appears three times — once per order.

The practical choice: use INNER JOIN when you only care about rows that have related data on both sides — orders with known customers, order items with known products. Use LEFT JOIN when you need all rows from one table regardless of whether they have matches — all customers including those who have never ordered, all products including those never sold, all stores including those with no employees. The LEFT JOIN + WHERE right_table.id IS NULL pattern specifically finds the rows with no match — customers who have never ordered, products never sold — which is one of the most common analytical queries.

If you use the old comma-separated table syntax (FROM table1, table2) without a WHERE condition connecting them, you get a Cartesian product — every row from table1 combined with every row from table2. With 1,000 customers and 10,000 orders, the result is 10,000,000 rows. On production tables with millions of rows, a Cartesian product can generate billions of rows and crash the database by exhausting memory or disk.

In modern JOIN syntax (FROM table1 JOIN table2 ON ...), forgetting the ON clause is a syntax error in most databases — the query fails to parse. This is one of the safety advantages of explicit JOIN syntax over the older comma syntax. Some databases allow JOIN without ON (treating it as CROSS JOIN), but this requires the CROSS JOIN keyword to be explicit in well-written SQL.

The safeguard: after writing any JOIN query, check the result row count before fetching all rows. SELECT COUNT(*) first. If a join of a 100-row table and a 200-row table returns 20,000 rows, something is wrong — either a missing ON condition or a one-to-many relationship you did not account for. Expected row count for an INNER JOIN should be at most min(left_rows, right_rows) × the fan-out factor of the relationship. A result vastly exceeding this is the sign of a missing or incorrect join condition.

Use LEFT JOIN whenever the absence of a match on the right side carries meaning and should appear in the result rather than being silently excluded. The three most common scenarios: finding rows with no related data (customers with no orders), reporting on all items regardless of activity (all products including unsold, all stores including empty), and optional relationships where the right-side data may or may not exist.

The "find no-match rows" pattern: LEFT JOIN followed by WHERE right_table.primary_key IS NULL returns only the left-table rows that have no match. This is the SQL idiom for "which X has no Y?" — customers who have never ordered, employees without a store assignment, products that have never been sold. An INNER JOIN cannot return these rows because they have no match — they are excluded by the intersection logic.

The "keep all with optional enrichment" pattern: reporting on all stores including those with no orders. SELECT s.store_id, s.city, COUNT(o.order_id) AS order_count FROM stores AS s LEFT JOIN orders AS o ON s.store_id = o.store_id GROUP BY s.store_id, s.city returns all stores with 0 for those with no orders. An INNER JOIN would exclude stores with no orders — making them invisible in the report, which misrepresents the data. The choice between INNER and LEFT JOIN is fundamentally a question: "do I want to see rows with no match?" If yes, LEFT JOIN. If no, INNER JOIN.

With LEFT JOIN, the placement of a filter condition — in the ON clause versus the WHERE clause — produces different results and this difference is one of the most common sources of subtle bugs in SQL queries.

A condition in the ON clause is applied during the join itself, before the outer join adds NULLs for unmatched rows. A condition in the WHERE clause is applied after the outer join, to the full result including the NULL rows. This difference matters because WHERE NULL conditions evaluate to NULL (not TRUE), so WHERE filters that reference right-table columns exclude the NULL rows — effectively converting the LEFT JOIN into an INNER JOIN for those rows.

Concrete example: FROM customers AS c LEFT JOIN orders AS o ON c.customer_id = o.customer_id AND o.order_status = 'Delivered' — the ON filter means "join only to delivered orders; customers with no delivered orders appear with NULL order columns." FROM customers AS c LEFT JOIN orders AS o ON c.customer_id = o.customer_id WHERE o.order_status = 'Delivered' — the WHERE filter means "after joining, keep only rows where order_status is Delivered; customers with no delivered orders are excluded because their order_status is NULL, which is not equal to Delivered." The first version is a true LEFT JOIN that preserves all customers. The second is effectively an INNER JOIN that loses customers with no delivered orders. Use ON for conditions that define the join relationship; use WHERE for conditions that filter the overall result. When you want to filter which rows from the right table participate in the join while keeping all left rows, put the filter in ON.

Cause: Two or more joined tables have a column with the same name — in FreshCart, both customers and stores have a city column. When SELECT references city without a table alias prefix, the database cannot determine which table's city column you mean and throws an ambiguity error.

Fix: Prefix every column reference with the table alias: SELECT c.city AS customer_city, s.city AS store_city. Always use table aliases in multi-table queries and qualify every column reference with its table alias. If you see an ambiguity error, search your SELECT list for every column name that exists in more than one of the joined tables and add the alias prefix.

Cause: A missing ON clause (accidentally creating a CROSS JOIN), an incorrect ON condition that matches too broadly, or a one-to-many relationship you did not account for. If orders JOIN order_items returns more rows than orders, that is correct — each order can have multiple items. But if customers JOIN customers returns customers_count² rows, a CROSS JOIN or bad ON condition is the cause.

Fix: Always run SELECT COUNT(*) on the JOIN result before fetching all rows. Compare the count to your expectation. For a one-to-one relationship, the result should have at most as many rows as the smaller table. For a one-to-many, the result has more rows than the parent table — one per child row. If the count is far larger than expected, examine the ON clause: is it correct? Are both columns actually the right join keys? Add LIMIT 5 and inspect the actual rows to see what the JOIN is matching.

Cause: A WHERE condition on the right-table column (e.g., WHERE o.order_status = 'Delivered') is filtering out the NULL rows that LEFT JOIN adds for unmatched left-table rows. WHERE NULL = 'Delivered' evaluates to NULL (not TRUE), so those rows are discarded. The LEFT JOIN correctly produces NULLs for unmatched rows, but WHERE immediately removes them.

Fix: Move the right-table filter from WHERE to the ON clause: LEFT JOIN orders AS o ON c.customer_id = o.customer_id AND o.order_status = 'Delivered'. This applies the filter within the join, before NULLs are added for unmatched rows. The result: all customers appear; for customers with no delivered orders, the order columns are NULL. If you specifically want to exclude unmatched rows, use INNER JOIN instead of LEFT JOIN — be explicit about your intent.

Cause: A one-to-many join multiplied the rows before aggregation. If orders are joined to order_items (one order has multiple items), orders.total_amount is repeated once per item. SUM(orders.total_amount) adds the total_amount multiple times — once per item — inflating the result. This is the fan-out bug.

Fix: Aggregate at the correct granularity. For order-level metrics, aggregate orders before joining to order_items. For item-level metrics, aggregate order_items. Use CTEs: WITH order_totals AS (SELECT order_id, SUM(line_total) AS total FROM order_items GROUP BY order_id) then join order_totals to orders. Or use COUNT(DISTINCT o.order_id) instead of COUNT(*) when one order appears multiple times due to a one-to-many join. Always verify aggregates by cross-checking totals against known values before trusting the result.

Cause: The ON clause references the wrong columns — either two columns from the same table (t1.id = t1.id always matches), two unrelated columns (o.total_amount = c.customer_id), or swapped foreign key references (o.store_id = c.customer_id). These produce either zero rows (no genuine matches), wrong matches (different entities accidentally matching), or all rows (column = itself).

Fix: Trace each join condition carefully: the ON clause should match the foreign key column in the child table to the primary key column in the parent table. o.customer_id (FK) = c.customer_id (PK). If the join returns zero rows when data clearly exists in both tables, run each table SELECT separately to confirm the values, then manually check whether any values overlap: SELECT DISTINCT customer_id FROM orders INTERSECT SELECT customer_id FROM customers — if this is empty, the join columns do not match.

Write two queries: (1) An INNER JOIN query that shows each delivered order's full details — order_id, order_date, customer full name, customer loyalty_tier, store city, total_amount, and payment_method. Only include orders from 2024. (2) A LEFT JOIN query that shows all customers with a count of their delivered orders — customer_id, full name, city, loyalty_tier, and delivered_order_count (0 for customers with no delivered orders). Sort the second query by delivered_order_count descending.