Advanced SQL Joins with Examples: Beyond the Basics

Yes. You write each query as its own joined SELECT, then combine the two result sets with a set operator. UNION merges them and removes duplicates. UNION ALL merges without removing duplicates. INTERSECT keeps only rows that appear in both result sets.

SELECT c.customer_name
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id
WHERE  o.order_status = 'Delivered'

UNION

SELECT c.customer_name
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id
WHERE  o.order_status = 'Pending';

Each side of the UNION is a complete joined query. The first finds customers with delivered orders. The second finds customers with pending orders. The UNION stacks them and removes duplicates so any customer appearing in both sets shows up only once.

The output on our data is two rows — Alice Corp (delivered) and Charlie Ltd (pending). If we used UNION ALL instead, Alice Corp would appear three times because she has three delivered orders.

Two rules govern this: both SELECTs must return the same number of columns, and the matching columns must have compatible data types. Column names come from the first SELECT. Break either rule and the query fails on the spot.

Oracle, PostgreSQL, MySQL, and SQL Server all support this pattern with the same syntax. The optimizer's ability to push predicates down into each side of the UNION varies by database — sometimes each side runs independently and the results are combined, sometimes the database is smarter about it.

NULL in a join means the value is unknown. Since unknown never equals anything — including another unknown — a join condition that involves a NULL in the join column never matches that row.

What that means for your result depends on the join type:

• INNER JOIN — the row with NULL in the join column is silently dropped. No warning, no error. It just does not appear.
• LEFT JOIN — the left row is kept and NULLs appear in all the right-table columns, because no matching right row was found.
• RIGHT JOIN — mirror of LEFT, from the right side.
• FULL OUTER JOIN — both sides kept, with NULLs filling in wherever the match failed.

SELECT c.customer_name, o.order_id
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Customers who have no orders come back with o.order_id as NULL. That is the intended behaviour of a LEFT JOIN — it is how the report shows every customer, whether they have ordered or not.

The implication worth remembering: if you add a WHERE clause that references a right-side column with a non-NULL condition, you silently convert the LEFT JOIN into an INNER JOIN. Every customer row that had NULL in that right-side column fails the WHERE test and drops out.

To replace the NULLs in the output with a display value, use COALESCE(o.order_id, 0) or NVL in Oracle or ISNULL in SQL Server. Same effect as COALESCE, different names. Stick with COALESCE if you want portable code.

You chain the joins. Each additional JOIN clause connects to a table already in the working set, and the database applies them left to right. Each join sees the result of the previous one.

SELECT c.customer_name,
       o.order_id,
       o.order_amount,
       p.payment_method
FROM   customers c
JOIN   orders    o ON c.customer_id = o.customer_id
JOIN   payments  p ON o.order_id    = p.order_id;

Three tables, joined through two conditions. The first join pairs customers with their orders. The second pairs those order rows with their payments. The result has one row per payment, with the customer name and order details next to it.

The number of tables is not the limit. You can join as many as you need. What matters is that each join condition references a table already in scope — you cannot join to a table you have not mentioned yet.

Two things to watch for as joins accumulate:

• Row multiplication. If any intermediate join produces multiple matches per row, the result grows. A four-table join where each level doubles the row count produces sixteen rows for every starting row.
• Ambiguous column names. Two tables with a column named customer_id require you to prefix every reference with the table alias. Do it consistently from the start.

You can also mix join types. A LEFT JOIN from customers to orders, followed by a LEFT JOIN from orders to payments, keeps every customer even when they have no orders and no payments. A single INNER JOIN in the chain would drop all the unmatched rows from that point forward.

Join the tables first, then apply the window function to the joined result. The window function sees every column the join produced and computes a value across a set of related rows without collapsing them.

SELECT c.customer_name,
       o.order_id,
       o.order_amount,
       RANK() OVER (
           PARTITION BY c.customer_id
           ORDER BY o.order_amount DESC
       ) AS order_rank
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id;

Each order gets a rank within its customer's orders. Alice Corp's three orders get ranks 1, 2, 3 in descending order of amount. Bob Traders's two orders get ranks 1 and 2. Every row is preserved — the window function adds a column, it does not collapse anything.

The pattern for "top N per group" combines a window function with a subquery or CTE:

WITH ranked AS (
    SELECT c.customer_name,
           o.order_id,
           o.order_amount,
           ROW_NUMBER() OVER (
               PARTITION BY c.customer_id
               ORDER BY o.order_amount DESC
           ) AS rn
    FROM   customers c
    JOIN   orders o ON c.customer_id = o.customer_id
)
SELECT customer_name, order_id, order_amount
FROM   ranked
WHERE  rn = 1;

That returns each customer's single largest order. It is the standard recipe for "show me the top item per category" reporting, and it works in every major database that supports window functions (MySQL 8.0+, PostgreSQL, SQL Server, Oracle).

Window functions are more expensive than simple aggregates because they need to sort the data within each partition. On large tables, the performance depends heavily on whether the partition and order columns are indexed. When a "top N per group" query is slow, the fix is usually an index that covers the partition and sort columns.

Yes. You combine conditions with AND or OR inside the ON clause. Every condition has to be true (for AND) or at least one has to be true (for OR) for a pair of rows to match.

SELECT c.customer_name,
       o.order_id,
       o.order_amount
FROM   customers c
JOIN   orders o
       ON c.customer_id = o.customer_id
      AND o.order_status = 'Delivered'
      AND o.order_amount > 5000;

Three conditions in one join. The database pairs each customer with each order and keeps only the pairs where all three conditions are true. On our data, only Alice Corp's orders qualify — order 1001, 1003, and 1007.

Multiple join conditions are common when the business rule for matching involves more than one column. A department identified by both a code and a location. A price that depends on both a product and a date range. Anywhere a single column is not enough to say "these two rows belong together".

Non-equality conditions work too. ON o.order_amount > c.credit_limit matches orders that exceed their customer's credit limit. Non-equi joins are less common than equality joins but the syntax is identical — the condition is just a comparison instead of an equality.

On a LEFT JOIN, remember the distinction between ON and WHERE. A condition in ON filters only which right-side rows are eligible to match; it does not drop left-side rows that have no match. A condition in WHERE filters the final result, which removes unmatched left-side rows entirely.

The optimizer's job is to find an execution plan — a sequence of operations that gets the answer as cheaply as possible. For a multi-join query there are many possible plans: which table to read first, which join algorithm to use (nested loop, hash join, merge join), which indexes to leverage. The optimizer estimates the cost of each alternative and picks the one it thinks is cheapest.

The estimation uses statistics about the tables — how many rows, how many distinct values per column, how the data is distributed. When those statistics are stale or missing, the optimizer guesses wrong and the plan is bad.

Four things you can do to help it pick a good plan:

• Index the join columns. Every column in an ON clause should have an index unless the table is tiny. This is the single biggest win.
• Filter early. Write WHERE conditions that reduce the row count before the join. Even if the optimizer could push them down automatically, writing them explicitly helps it notice.
• Select only the columns you need. SELECT * forces the database to read every column from every table, even the ones you will not use. Listing columns explicitly lets the database use narrower, faster index-only scans.
• Use INNER JOIN where possible. INNER JOIN is generally cheaper than OUTER JOIN because it does not track unmatched rows.

To see what the optimizer actually chose, run EXPLAIN in front of the query. PostgreSQL has EXPLAIN ANALYZE, MySQL has EXPLAIN and EXPLAIN FORMAT=JSON, Oracle has EXPLAIN PLAN and DBMS_XPLAN. The plan shows which tables were scanned, which indexes were used, and how many rows each step processed. A plan that says "sequential scan" on a large table is almost always a missing index.

One more habit worth building: if a query is slow and adding indexes has not helped, the row count at each step in the execution plan tells you where the work is happening. If a step that should produce a hundred rows is producing a million, something is wrong with the join condition or the statistics.

A subquery is a query inside a query. It can appear in three places, and each position has its own behaviour and performance profile.

Subquery in WHERE — filtering the rows

SELECT c.customer_name
FROM   customers c
WHERE  c.customer_id IN (
    SELECT o.customer_id
    FROM   orders o
    WHERE  o.order_amount > 10000
);

The inner query returns the list of customers who have placed an order above 10,000. The outer query filters customers to that list. You could also do this with a join and DISTINCT, and the optimizer usually treats the two forms similarly.

Subquery in FROM — a derived table

SELECT c.customer_name,
       t.total_spend
FROM   customers c
JOIN  (
    SELECT customer_id, SUM(order_amount) AS total_spend
    FROM   orders
    GROUP BY customer_id
) t ON c.customer_id = t.customer_id;

The subquery runs first, producing one row per customer with their total spend. That result is joined to the customers table as if it were a real table. This is the standard pattern for joining an aggregated result to a detail table — you cannot join and aggregate at the same level without GROUP BY collapsing everything.

Subquery in SELECT — a computed column

SELECT c.customer_name,
       (SELECT COUNT(*)
        FROM   orders o
        WHERE  o.customer_id = c.customer_id) AS order_count
FROM   customers c;

A scalar subquery runs once per row of the outer query, producing a single value for that row. It works, but it can be slow — on a million-row customers table, that is a million subquery executions.

The faster alternative is a LEFT JOIN against a grouped subquery, which runs the aggregation once and joins the result:

SELECT c.customer_name,
       COALESCE(t.order_count, 0) AS order_count
FROM   customers c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM   orders
    GROUP BY customer_id
) t ON c.customer_id = t.customer_id;

Same output, much faster on large tables. If you ever see a correlated subquery in a SELECT list and the query is slow, this rewrite is the first thing to try.

Six techniques cover the vast majority of join performance work. They are listed roughly in order of impact.

1. Index the join columns. Every column referenced in an ON clause should have an index unless the table is tiny. Without an index, the database has to scan the whole table to find matching rows. With one, it looks them up directly.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

2. Filter early. Apply WHERE conditions that reduce the row count before the join, and put extra matching rules inside the ON clause so only the interesting rows get combined. On a table with millions of rows, filtering to ten thousand before joining is the difference between a plan that works and one that times out.

3. Select only the columns you need. SELECT * forces the database to read every column from every table. Naming the columns you actually want lets it use narrower indexes and reduces the amount of data moving through the pipeline.

4. Use INNER JOIN where possible. INNER JOIN does not have to track unmatched rows and fill them with NULLs. If you do not need the unmatched rows, do not use an OUTER JOIN just because it feels safer — it costs more.

5. Consider subqueries or temporary tables. Sometimes pre-filtering or pre-aggregating into a temporary table and then joining to that is faster than a single complex query, especially when the same aggregated result is used by multiple downstream queries.

6. Look at the execution plan. Run EXPLAIN in front of the query and read the plan. It shows where the time is actually going. Inefficient joins, unexpected full table scans, and mismatch between estimated and actual row counts are all visible in the plan. Fixing what the plan identifies is far more effective than guessing.

A self-join connects a table to itself using two aliases. It handles hierarchies one level deep. A recursive CTE walks a hierarchy to any depth by repeatedly joining the result back to the source.

Self-join for direct relationships

SELECT c1.customer_name AS customer,
       c2.customer_name AS referred_by
FROM   customers c1
LEFT JOIN customers c2 ON c1.referred_by_id = c2.customer_id;

Each customer appears with the customer who referred them. Two aliases (c1 and c2) are essential — without them the database cannot tell which customer_name you mean. On our data, C002 is referred by C001, C005 by C002, and so on. Customers with no referrer come back with NULL.

Recursive CTE for multi-level hierarchies

WITH RECURSIVE referral_chain AS (
    SELECT customer_id,
           customer_name,
           referred_by_id,
           1 AS level
    FROM   customers
    WHERE  referred_by_id IS NULL

    UNION ALL

    SELECT c.customer_id,
           c.customer_name,
           c.referred_by_id,
           rc.level + 1
    FROM   customers c
    JOIN   referral_chain rc ON c.referred_by_id = rc.customer_id
)
SELECT * FROM referral_chain ORDER BY level, customer_id;

The CTE has two parts. The anchor query selects the root rows — the customers with no referrer. The recursive part joins the CTE back to the source and produces the next level. The database keeps running the recursive part until it stops producing new rows.

On our data, the output has three levels: C001 and C003 at level 1 (no referrer), C002, C004, C006 at level 2, and C005, C007 at level 3.

When to use which

Use a self-join when the depth is fixed. Managers and their direct reports. Categories and their parent category. Use a recursive CTE when the depth varies. Full organisational charts, folder trees, bill of materials.

Recursive CTEs can loop forever if the data contains cycles — two rows that point at each other as parents. Add a depth guard like WHERE rc.level < 10 in the recursive part to bound the work. Most databases cap recursion at 100 iterations by default and error out before anything dangerous happens, but explicit protection is worth having.

Support varies by database. PostgreSQL, SQL Server, and Oracle support recursive CTEs natively. MySQL added support in version 8.0. If you are on an older MySQL, you need a stored procedure or a loop in the application layer instead.

Join first, then aggregate. The join produces the working set of rows from multiple tables, and GROUP BY with an aggregate function collapses those rows into per-category summaries.

SELECT c.customer_name,
       COUNT(o.order_id)      AS order_count,
       SUM(o.order_amount)    AS total_spend,
       AVG(o.order_amount)    AS avg_order
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_spend DESC NULLS LAST;

One row per customer with their order count, total spend, and average order value. The LEFT JOIN keeps customers who have no orders — they appear with a count of zero and NULL for the amount columns.

If you want zero instead of NULL for those columns, wrap the aggregates in COALESCE:

COALESCE(SUM(o.order_amount), 0) AS total_spend

The one subtle bug to watch for: COUNT(*) vs COUNT(column) on the joined result. If the join multiplies rows — a customer with three orders produces three joined rows — then COUNT(*) counts the multiplied rows. Use COUNT(DISTINCT o.order_id) when you actually want the number of distinct orders, not the number of joined rows.

On our data, using COUNT(*) would give Alice Corp a value of 3 (three joined rows). Using COUNT(DISTINCT o.order_id) would also give 3, because each joined row corresponds to a distinct order. The distinction matters when a join multiplies through two one-to-many relationships — for example customers joined to orders joined to order items, where one order can have many items.

The ON clause accepts any expression, not just column names. You can join on a value computed from arithmetic, functions, or string concatenation.

SELECT o.order_id,
       o.order_amount,
       d.discount_pct,
       o.order_amount * (1 - d.discount_pct) AS final_amount
FROM   orders     o
JOIN   discounts  d
       ON o.customer_id = d.customer_id
      AND o.order_amount * (1 - d.discount_pct) > 5000;

The join condition computes the discounted price for each pair of rows and keeps only the pairs where the computed value exceeds 5,000. Both the join and the SELECT list use the same expression, so the output is consistent with the filter.

Computed joins turn up whenever two systems disagree on formatting. Customer IDs stored with different prefixes. Dates stored as timestamps in one table and plain dates in the other. Values stored with currency symbols embedded.

SELECT o.order_id, c.customer_name
FROM   orders    o
JOIN   customers c
       ON o.customer_id = 'C' || SUBSTR(c.customer_id, 2)
      AND UPPER(c.region) = 'NORTH';

The performance cost is real. A computed join condition usually disables any index on the underlying columns, because the index stores raw values, not computed ones. UPPER(c.customer_name) = UPPER(o.customer_name) cannot use the index on customer_name — the index would need to be over the uppercased expression, not the raw column.

For occasionally-run queries, this is fine. For frequently-run queries that compute the same expression every time, consider a function-based index (Oracle, PostgreSQL) or a persisted computed column (SQL Server) so the database can index the computed value directly.

They combine data in fundamentally different ways. A FULL OUTER JOIN merges columns side by side based on a matching condition, filling NULLs where one side has no match. A UNION stacks rows from two queries on top of each other, without any matching logic.

-- FULL OUTER JOIN: side-by-side, one row per matched pair or unmatched row
SELECT c.customer_name, o.order_id
FROM   customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

-- UNION: stacked, one row per unique value across both queries
SELECT customer_name FROM customers
UNION
SELECT customer_name FROM suppliers;

The FULL OUTER JOIN returns ten rows on our data — all seven customers (three of them with NULL order columns because they have no orders) plus all seven orders (all matched, so no orphan order rows). The UNION returns a single column with every distinct customer name and every distinct supplier name, one row per unique name.

Which you use depends on the question. "Show me every customer alongside their orders, including customers who have none" is a FULL OUTER JOIN. "Show me every distinct name in either table, without duplicates" is a UNION.

One practical difference: FULL OUTER JOIN requires a matching condition between two tables. UNION requires two queries that return the same column shape. They solve different problems with different requirements.

MySQL is worth noting here. It does not natively support FULL OUTER JOIN. To emulate it, you write a LEFT JOIN and a RIGHT JOIN and combine them with UNION:

SELECT c.customer_name, o.order_id
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION

SELECT c.customer_name, o.order_id
FROM   customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

That produces the same result as a native FULL OUTER JOIN. The UNION handles the overlap between the two joins.

The two joins differ in what happens to rows where the join column does not match. INNER JOIN drops them. LEFT JOIN keeps the left-side row and fills the right-side columns with NULLs.

-- INNER JOIN: only customers who have orders
SELECT c.customer_name, o.order_id
FROM   customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Returns 7 rows (one per order)

-- LEFT JOIN: every customer, with their orders where they exist
SELECT c.customer_name, o.order_id
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Returns 10 rows (7 orders + 3 customers with no orders)

On our data, the INNER JOIN returns seven rows — every order has a valid customer, so every order shows up. Customers C005, C006, and C007 do not appear because they have no orders to match against. The LEFT JOIN returns ten rows — the same seven orders, plus the three customers who have no orders, with NULL for their order_id.

The choice between them is really a question of which side is "primary." If you want a report on orders, use INNER JOIN — you only care about orders that have a customer. If you want a report on customers, use LEFT JOIN — you want every customer, whether they have ordered or not.

The NULLs a LEFT JOIN produces in the right-side columns are your signal that a match did not happen. To count unmatched left rows, add WHERE o.order_id IS NULL after the LEFT JOIN and the result is the customers who have never ordered. That pattern — LEFT JOIN plus NULL check on the right side — is the standard way to find missing relationships.

Yes. A subquery in the FROM clause produces a derived table — an intermediate result that the outer query joins to as if it were a real table. This is the standard pattern for combining an aggregated result with detail data.

SELECT c.customer_name,
       t.total_spend,
       t.order_count
FROM   customers c
JOIN  (
    SELECT customer_id,
           SUM(order_amount) AS total_spend,
           COUNT(*)          AS order_count
    FROM   orders
    GROUP BY customer_id
) t ON c.customer_id = t.customer_id;

The inner query aggregates orders by customer, producing one row per customer with their total spend and order count. The outer query joins that result to the customers table. Each customer appears once, with their aggregated metrics next to them.

The reason this pattern exists: you cannot join customers to orders and then apply SUM without a GROUP BY that collapses the join's output. The subquery keeps the two operations at separate levels — aggregate first, join second. The result is clearer and often faster, because the aggregation can use an index on orders.customer_id without involving the customers table at all.

The derived table must have an alias (here, t). Every database requires it. Without an alias, the query fails with "subquery in FROM must have an alias" — which is one of those error messages that never makes sense until someone explains it once.

You can chain multiple derived tables in a single query. Each one runs its own aggregation or transformation, and the outer query joins them together. On very complex reports, this is often clearer than a single query with five joins and a dozen CASE expressions in the SELECT list.

Joins are usually the most expensive operation in a query, and they are almost always where a slow report spends its time. The cost depends on four things: how many rows each table has, which join type you use, whether the join columns are indexed, and how the optimizer chooses to execute the join.

The biggest single factor is indexes. Without an index on the join column, the database has to scan the whole table to find matches. With one, it looks them up directly. On a table with a million rows, that is the difference between a few hundred milliseconds and a few seconds.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

The join type matters too. INNER JOIN is generally cheaper than OUTER JOIN because it does not track unmatched rows. If you do not need the outer rows, do not pay for them.

Reducing the working set before the join is the third lever. A tight WHERE clause reduces the number of rows that need to be combined. A specific column list in the SELECT reduces the amount of data that needs to be moved through the pipeline.

The fourth thing is knowing what the optimizer actually chose. Run EXPLAIN in front of the query, or EXPLAIN ANALYZE for the version with real timings. The plan shows you which tables were scanned, which indexes were used, and how many rows each step processed. When the plan says "sequential scan" on a large table, an index is missing. When the estimated row count is far from the actual, statistics are stale and the optimizer is guessing wrong.

In practice, the biggest performance wins come from: (1) indexing the join columns, (2) filtering before the join, (3) selecting only needed columns, (4) using the simplest join type that solves the problem, and (5) reviewing the execution plan when the first four have not helped. Points one through three solve most real-world problems.

The JOIN goes in the FROM clause as usual. The CASE goes in the SELECT list and transforms the joined output based on conditions. The two work together cleanly — the join brings columns into scope, and the CASE decides how to present them.

SELECT c.customer_name,
       o.order_id,
       o.order_amount,
       CASE
           WHEN o.order_amount > 15000 THEN 'High'
           WHEN o.order_amount > 5000  THEN 'Medium'
           ELSE 'Low'
       END AS order_size
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id
ORDER BY o.order_amount DESC;

Every order comes back with a size category next to it. The join pairs each order with its customer, and the CASE categorises the order amount into High, Medium, or Low. On our data, order 1004 (22,000) is High, order 1006 (18,000) is High, order 1001 (12,000) is Medium, and the smaller ones land in Low.

The same CASE pattern can appear in an ON clause if the matching itself depends on conditional logic:

SELECT c.customer_name, o.order_id
FROM   customers c
JOIN   orders o
       ON c.customer_id = o.customer_id
      AND o.order_status = CASE
              WHEN c.region = 'North' THEN 'Delivered'
              ELSE 'Shipped'
          END;

That join matches North-region customers only to delivered orders, and everyone else to shipped orders. It is unusual to write joins this way, but the syntax allows it and there are cases where the business rule is exactly this conditional.

Where CASE pays off most in practice is in the SELECT list, converting raw codes to readable labels. Status codes like "D" or "P" mean nothing to a report reader; CASE turns them into "Delivered" or "Pending". This is one of the simplest ways to make a query's output immediately useful to a non-technical audience.

If you find yourself repeating the same CASE expression across many queries, that is usually a signal that the database should store the labels directly, or expose them through a lookup table that you join to. The database schema is often the right place to solve this problem, not the query layer.