INNER JOIN

INNER JOIN works by evaluating the ON condition for every candidate pair of rows from the left and right tables. For each row in the left table, the database uses the join column's index (if available) to find matching rows in the right table — rows where the ON condition evaluates to TRUE. When a match is found, the columns from both rows are concatenated into a single output row. Rows from either table that have no matching partner in the other table are excluded from the result.

The database does not literally evaluate every combination of rows (which would be O(n²)). Instead it uses join algorithms: the nested loop join (iterate left rows, index-lookup right), hash join (build a hash table from the smaller table, probe it with each left row), or merge join (sort both tables on the join key, merge-scan). The query optimiser chooses the algorithm based on table sizes, available indexes, and memory.

The result of INNER JOIN is always a subset of the Cartesian product of the two tables — only rows where the ON condition is satisfied. The output has all columns from both tables (as specified in SELECT) for each matched pair. For foreign key relationships in a well-designed schema, INNER JOIN and LEFT JOIN produce the same result — because the FK constraint guarantees every FK value has a matching PK. The difference emerges only for optional relationships or when checking for unmatched rows.

This is the fan-out bug. The orders-to-order_items relationship is one-to-many: one order can have many items. When you JOIN orders to order_items, each order row is duplicated once per item — an order with 3 items appears 3 times in the joined result. When you then SUM(orders.total_amount), you are adding the same total_amount three times for that order.

Example: order 1001 has total_amount = ₹500 and 3 items. After the JOIN, the result has 3 rows all with total_amount = ₹500. SUM(total_amount) for this order contributes ₹1,500 instead of ₹500 — triple-counted. The final sum across all orders is inflated by the average number of items per order.

The three fixes: (1) SUM the item-level column instead — SUM(order_items.line_total) is correct because each line_total is unique to each item row, not duplicated. (2) Use COUNT(DISTINCT order_id) instead of COUNT(*) to count orders, not item rows. (3) Pre-aggregate order_items in a subquery or CTE first (SELECT order_id, SUM(line_total) AS total FROM order_items GROUP BY order_id), then join that aggregated result to orders — one row per order in the joined result, no fan-out. The fan-out bug is especially dangerous because the query runs without errors and returns plausible-looking numbers. Always cross-check JOIN aggregates against simpler single-table queries.

For INNER JOIN specifically, a filter in ON and a filter in WHERE produce identical results. This is because INNER JOIN already excludes unmatched rows — it does not have the asymmetric NULL-preservation behaviour of LEFT JOIN. Whether the filter is applied during the join (ON) or after (WHERE), the same rows end up being excluded.

Example: JOIN customers AS c ON o.customer_id = c.customer_id AND c.city = 'Seattle' produces the same result as JOIN customers AS c ON o.customer_id = c.customer_id WHERE c.city = 'Seattle'. Both return only orders from Seattle customers.

However, there is a convention preference: conditions that define the join relationship (matching keys) belong in ON; conditions that filter the result belong in WHERE. This makes queries more readable and semantically clear. ON answers "how do the tables relate?" WHERE answers "what rows from the joined result do I want?" Mixing business filters into ON makes the join condition harder to scan. The performance implication is theoretically the same for INNER JOIN since the optimiser can push predicates into the join regardless — but readability favours keeping relationship conditions in ON and filter conditions in WHERE.

Joining more than two tables means chaining multiple JOIN clauses. Each JOIN adds one more table to the current result. The syntax is sequential: FROM a JOIN b ON a.key = b.key JOIN c ON b.key = c.key JOIN d ON c.key = d.key. Each JOIN's ON clause can reference columns from any table that has already been introduced — you cannot reference a table before it appears in the FROM/JOIN chain.

Logically, the order of JOINs matters for which columns are available in subsequent ON clauses. You cannot join order_items to products before joining orders to order_items — because order_items is not yet in scope when you try to join products to it. The practical rule: join tables in the order of the relationship chain, following the FK path through the schema.

Physically, the query optimiser reorders JOINs for performance — it does not respect the written order. It evaluates all possible join orderings (up to a threshold) and chooses the one with the lowest estimated cost based on table sizes, indexes, and statistics. For queries with 5+ tables, the optimiser uses heuristics rather than exhaustive search. You can influence the physical order with hints in some databases (e.g., LEADING in PostgreSQL), but this is rarely necessary unless the optimiser is making a provably bad choice. Write JOINs in the logical relationship order for readability; trust the optimiser for physical ordering.

Finding the top seller per store is a "top N per group" problem. The approach requires computing revenue per (store, product) combination, then selecting the product with the highest revenue within each store group.

Step 1 — compute revenue per store per product: SELECT o.store_id, p.product_id, p.product_name, SUM(oi.line_total) AS revenue FROM orders AS o JOIN order_items AS oi ON o.order_id = oi.order_id JOIN products AS p ON oi.product_id = p.product_id WHERE o.order_status = 'Delivered' GROUP BY o.store_id, p.product_id, p.product_name. This gives one row per (store, product) pair with total revenue.

Step 2 — select the top product per store. The cleanest approach uses ROW_NUMBER() window function: wrap Step 1 in a CTE or subquery, add ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY revenue DESC) AS rn, then filter WHERE rn = 1 in the outer query. This assigns rank 1 to the highest-revenue product within each store and returns only those. An alternative without window functions: join the Step 1 result to a subquery that finds MAX(revenue) per store, matching on both store_id and revenue = max_revenue. The ROW_NUMBER approach is cleaner and handles ties more explicitly (ROW_NUMBER picks one; DENSE_RANK preserves ties). Window functions are covered in depth in Module 45.

Cause: The ON condition references the wrong columns, uses mismatched data types, or the FK values in one table do not exist as PK values in the other. Common causes: joining on the wrong column (o.store_id = c.customer_id by mistake), type mismatch where one column is VARCHAR and the other is INTEGER, or the tables genuinely have no overlapping values (staging data that has not been linked yet).

Fix: Test each table independently: SELECT DISTINCT customer_id FROM orders LIMIT 5 and SELECT customer_id FROM customers LIMIT 5 — confirm the values overlap. Check data types: SELECT pg_typeof(o.customer_id), pg_typeof(c.customer_id) FROM orders AS o, customers AS c LIMIT 1. If types differ, cast one: ON o.customer_id = c.customer_id::INTEGER. If no values overlap, investigate the data pipeline — FK values in the child table must exist in the parent table for INNER JOIN to return rows.

Cause: The join column on the looked-up table (the inner table) is not indexed. Without an index, each row from the outer table requires a full sequential scan of the inner table to find matches. On a 100,000-row table joined to a 10,000-row table without indexes, this means up to 100,000 × 10,000 = 1 billion comparisons in the worst case.

Fix: Add an index on the join column: CREATE INDEX idx_orders_customer_id ON orders(customer_id). For primary keys and unique columns, indexes already exist. Check with: SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders'. Use EXPLAIN ANALYZE to confirm the query plan shows Index Scan (fast) rather than Sequential Scan (slow) on the inner table. For very large tables, also consider composite indexes that cover both the join column and the most common WHERE columns.

Cause: A many-to-many relationship in the join chain is creating a multiplicative fan-out. If orders join to order_items (one-to-many) AND order_items join to products (many-to-one), the result has one row per order-item-product combination — which may be more rows than you expected if you were thinking in terms of orders or products.

Fix: Always count rows at each JOIN step: add each JOIN one at a time and SELECT COUNT(*) after each one. When the count jumps unexpectedly, the last added JOIN is causing the fan-out. Examine the relationship: is it truly one-to-many? Does the ON condition match multiple rows? The solution is usually to aggregate one side before joining: pre-aggregate order_items to order-level totals, then join that aggregated result to orders — removing the item-level multiplication.

Cause: Both orders and order_items have an order_id column. Without a table alias prefix, SELECT order_id is ambiguous — the database does not know which table's order_id you mean.

Fix: Always prefix columns with their table alias in multi-table queries: SELECT o.order_id, oi.order_id AS item_order_id. In practice, for a shared key column like order_id, you usually only need one copy: SELECT o.order_id — prefix with the alias of the table that owns the column as a PK. Establish the habit of prefixing every column reference with a table alias in any query involving JOINs — this prevents both ambiguity errors and confusion about which table a column comes from.

Cause: A filter inside the CTE is not behaving as expected, or the outer query's WHERE is conflicting with the CTE's logic. CTEs in PostgreSQL are not always inlined by the optimiser — they may be materialised (computed once, cached), which means subsequent filters in the outer query cannot be pushed into the CTE for optimisation. In some versions, the CTE materialises all its rows even if the outer query only needs a few.

Fix: Verify the CTE result independently: WITH my_cte AS (...) SELECT * FROM my_cte LIMIT 20. Confirm the CTE produces the expected rows. If the outer join to the CTE returns wrong results, check that the ON condition references the correct column names in the CTE (alias names, not original table names). In PostgreSQL 12+, use MATERIALIZED or NOT MATERIALIZED hints to control whether the CTE is inlined: WITH cte AS NOT MATERIALIZED (...) — NOT MATERIALIZED allows the optimiser to push filters into the CTE for better performance.

Write a query that produces a category performance report — one row per product category. Show: category, number of distinct products in that category, number of distinct orders that contained at least one product from that category, total units sold, total revenue (from order_items.line_total, rounded to 2 decimal places), average selling price (rounded to 2 decimal places), and the category's share of total delivered revenue as a percentage (rounded to 1 decimal place). Only include delivered orders. Sort by total revenue descending.