SQL set operations: combining and comparing whole query results

The shop example we are using has recently moved to a new order management system. During the migration, some orders were copied from the old system to the new one.

The new system stores orders in the orders table, which we used in P07.

The old system’s data was exported into a separate table called legacy_orders.

Notice three important details:

• Orders 998 and 999 exist only in the old system. Order 999 is a guest order with no customer ID.
• Orders 1001 and 1004 appear in both tables because they were migrated.
• Order 1004 has different statuses in the two systems. It was marked paid in the old system but was later cancelled in the new system.

Finance now needs reports that combine data from both systems. For example:

• A complete list of orders from both systems.
• The total order value without counting migrated orders twice.
• Orders that appear in both systems.
• Orders whose details differ between the two systems.

These requirements are different from the joins we studied in P07. A JOIN combines related rows from two tables, typically adding columns side by side. Here, we need to combine or compare rows returned by separate queries.

SQL provides four set operations for this:

• UNION ALL
• UNION
• INTERSECT
• EXCEPT

Each operation takes the results of two queries and combines or compares their rows. The examples in this lesson use DuckDB. Where another database behaves differently, we will point it out.

The first requirement is to create one list containing every order from both systems, including orders that appear in both tables. We can use UNION ALL to combine the results of two queries. Both queries must return the same number of columns, with compatible data types in corresponding positions. The final ORDER BY sorts the combined result.

SELECT order_id, product, amount
FROM orders
UNION ALL
SELECT order_id, product, amount
FROM legacy_orders
ORDER BY order_id;

UNION also combines the results of two queries, but unlike UNION ALL, it removes duplicate rows.

Two rows are considered duplicates when all their selected column values are the same. Consider a simpler requirement: Find all distinct products sold across both systems.

SELECT product
FROM orders
UNION
SELECT product
FROM legacy_orders
ORDER BY product;

What happens when we combine orders?

Now let’s use UNION to combine the order details, just as we did in section 02.

SELECT order_id, product, amount
FROM orders
UNION
SELECT order_id, product, amount
FROM legacy_orders
ORDER BY order_id;

The result now contains 9 rows instead of 11.

Orders 1001 and 1004 each appeared twice in the UNION ALL result. Since their selected values are identical across both tables, UNION keeps only one copy of each. Notice that order 1004 has different statuses in the two systems. However, status is not part of the query, so it does not affect duplicate detection. This leads to an important rule: UNION compares only the columns returned by the queries.

If two rows have identical values in every selected column, UNION treats them as duplicates, even if they represent different records in the original tables.

Finance now wants to calculate the total value of all orders across both systems, counting each order only once.

We know that UNION removes duplicate rows, so it seems like a reasonable choice. To keep the query simple, someone selects only the columns needed for the report: product and amount.

SELECT product, amount
FROM orders
UNION
SELECT product, amount
FROM legacy_orders
ORDER BY product, amount;

How does this affect the total?

Suppose we calculate the total amount from the combined result:

SELECT SUM(amount) AS total_amount
FROM (
    SELECT product, amount FROM orders
    UNION
    SELECT product, amount FROM legacy_orders
) AS combined;

The total is incorrect because two valid orders were removed. Their combined value is:

3,200 + 750 = 3,950

So the reported total is 3,950 lower than it should be.

Why not use UNION ALL?

We might try replacing UNION with UNION ALL to keep those orders.

SELECT SUM(amount) AS total_amount
FROM (
    SELECT order_id, amount FROM orders
    UNION ALL
    SELECT order_id, amount FROM legacy_orders
) AS combined;

This produces a different problem. UNION ALL keeps every row, including the migrated copies of orders 1001 and 1004. Their amounts, 1,500 and 8,500, are counted twice, adding an extra 10,000 to the total.

Therefore:

• UNION without order_id removes some valid orders.
• UNION ALL counts migrated orders more than once.

Neither query gives the correct total.

The fix: Include the order ID

To count each order once, we need to distinguish orders that happen to share the same product and amount.

Include order_id in both queries:

SELECT SUM(amount) AS total_amount
FROM (
    SELECT order_id, product, amount FROM orders
    UNION
    SELECT order_id, product, amount FROM legacy_orders
) AS combined;

The result is now correct.

Here’s what changes:

• Orders 1001 and 1004 each appear only once because their selected values match across both systems.
• Orders 1002 and 1007 remain separate because they have different order IDs, even though their product and amount are identical.
• Orders 999 and 1005 also remain separate for the same reason.

The combined result contains 9 distinct orders, and each contributes to the total once.

Comparing the three approaches

One important limitation remains: this solution works because the migrated copies have identical selected values in both systems. If the amount or product changes for the same order, UNION will keep both versions because their rows are no longer identical. We will explore how to handle such cases in sections 07 and 11.

The team wants a combined list of order IDs and customer IDs from both systems. However, the second query lists the columns in a different order. Both columns contain integers, so the mistake may not be obvious.

SELECT order_id, customer_id
FROM orders
UNION ALL
SELECT customer_id, order_id
FROM legacy_orders
ORDER BY order_id;

Three rules for combining query results

When using set operations, remember these rules:

1. Both queries must return the same number of columns. A mismatch produces an error.
2. Columns in corresponding positions must have compatible data types. Depending on the database, incompatible types may cause an error or trigger an automatic conversion.
3. Columns in corresponding positions should represent the same information. SQL does not check this for you.

The combined result also takes its column names from the first query.

DuckDB supports UNION BY NAME and UNION ALL BY NAME, which match columns by name instead of position. However, these are not standard features available in every database.

The team wants to combine the two largest orders from the current system with every order from the legacy system.

Someone writes a query that sorts and limits the current orders before adding the legacy orders with UNION ALL.

SELECT order_id, amount
FROM orders
ORDER BY amount DESC, order_id
LIMIT 2
UNION ALL
SELECT order_id, amount
FROM legacy_orders;

The fix: Limit the first query separately

In DuckDB, we can use parentheses to give the first query its own ORDER BY and LIMIT.

(
    SELECT order_id, amount
    FROM orders
    ORDER BY amount DESC, order_id
    LIMIT 2
)
UNION ALL
(
    SELECT order_id, amount
    FROM legacy_orders
)
ORDER BY amount DESC, order_id;

The result contains 6 rows, as intended. Here’s how the query works:

1. The first query sorts current orders by amount and keeps the top two: orders 1004 and 1003.
2. The second query returns all four legacy orders.
3. UNION ALL combines these results without removing duplicates.
4. The final ORDER BY sorts all six rows by amount.

Notice that order 1004 appears twice because it exists in both systems and UNION ALL keeps both copies.

Why include order_id in ORDER BY?

Two current orders, 1003 and 1006, both have an amount of 3,600. If we sorted only by amount, either order could be selected as the second-largest order. Adding order_id makes the result predictable. When amounts are equal, the smaller order ID comes first. Therefore, order 1003 is selected instead of 1006.

What if ORDER BY and LIMIT appear only at the end?

Consider this query:

SELECT order_id, amount
FROM orders
UNION ALL
SELECT order_id, amount
FROM legacy_orders
ORDER BY amount DESC, order_id
LIMIT 2;

Here, SQL combines all 11 rows first, then sorts them and keeps only the top two. Both rows belong to order 1004, one from each system. This is different from selecting the top two current orders before combining them with the legacy orders.

Also, the final ORDER BY in the earlier corrected query sorts all six rows together. If the report must display current orders before legacy orders, we would need an additional source column to control that ordering.

Database note: DuckDB and PostgreSQL support parenthesised query branches.

After combining rows from two systems, we may need to know which system each row came from.

A simple solution is to add a column containing a fixed value such as 'current' or 'legacy'. Someone adds this column and uses UNION, expecting it to remove orders that appear in both systems.

SELECT order_id, 'current' AS source
FROM orders
UNION
SELECT order_id, 'legacy' AS source
FROM legacy_orders
ORDER BY order_id, source;

How do we keep one copy of each order?

The requirement has changed: Create one combined order list, but when an order exists in both systems, keep the current system’s version.

This is important because the current system contains the latest information. For example, order 1004 is marked cancelled in the current system but paid in the legacy system. UNION cannot decide which version is more recent or which system should take priority. It only compares selected values. Instead, we need to define which source wins.

Our approach is:

1. Keep every order from the current system.
2. Add orders from the legacy system only when their order_id does not exist in the current system.

We can use NOT EXISTS, as learned in P08, to exclude migrated legacy orders.

SELECT order_id, 'current' AS source
FROM orders
UNION ALL
SELECT l.order_id, 'legacy' AS source
FROM legacy_orders AS l
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.order_id = l.order_id
)
ORDER BY order_id;

The result now contains 9 orders, one row per order.

Orders 1001 and 1004 come only from the current system because their legacy copies are excluded by NOT EXISTS. Orders 998 and 999 are included from the legacy system because they do not exist in the current table.

Why use UNION ALL instead of UNION?

We have already removed overlapping orders using NOT EXISTS. Since order_id is unique within each source in this example, the two query results now contain no duplicate order records. There is no need for UNION to perform another duplicate-removal step. UNION ALL simply combines the remaining rows.

Finance now wants a report showing the number of orders and total order amount for each customer across both systems.

Each order must be counted only once, even if it appears in both tables. We already solved the duplication problem in section 07 by keeping all current orders and adding only legacy orders that do not exist in the current system.

We can use that same approach here.

The steps are:

1. Build one combined result containing each order exactly once.
2. Group that result by customer_id.
3. Calculate the order count and total amount for each customer.

To do this, place the combined query inside a derived table, then use GROUP BY in the outer query.

SELECT
    customer_id,
    COUNT(*)    AS orders,
    SUM(amount) AS total_amount
FROM (
    SELECT order_id, customer_id, amount
    FROM orders
    UNION ALL
    SELECT l.order_id, l.customer_id, l.amount
    FROM legacy_orders AS l
    WHERE NOT EXISTS (
        SELECT 1
        FROM orders AS o
        WHERE o.order_id = l.order_id
    )
) AS all_orders
GROUP BY customer_id
ORDER BY customer_id;

Understanding the result

Let’s check a few customers against the original tables.

Customer 1 (Asha) has two orders:

• Order 1001: 1,500
• Order 1005: 750

Order 1001 also exists in legacy_orders, but its legacy copy is excluded. Therefore, Asha has 2 orders with a total amount of 2,250.

Customer 3 (Carla) also has two orders:

• Order 1003: 3,600 from the current system.
• Order 998: 1,200 from the legacy system.

Since order 998 does not exist in the current system, it is included. Carla’s total is 4,800.

What about the NULL customer_id?

The result also contains a group where customer_id is NULL.

This group contains two guest orders:

• Order 1007: 3,200
• Order 999: 750

GROUP BY places rows with NULL values in the grouping column into the same group. Therefore, the guest orders produce one row with 2 orders and a total amount of 3,950.

Why combine before aggregating?

If we calculate totals separately in orders and legacy_orders, the same customer may appear in both results. We would then need another aggregation to combine those totals. More importantly, the migrated orders 1001 and 1004 would still be counted twice unless we removed their legacy copies first.

By combining the order records before aggregating, we ensure that each order contributes to the totals only once. Notice that the derived table includes order_id, even though the outer query does not display it. Keeping this key makes the intended level of detail clear: one row represents one order. It also prevents distinct orders with identical customer IDs and amounts from being mistaken for duplicate rows if the combination method changes.

Be careful when filtering by status

The current query includes orders of every status, including pending and cancelled orders. For example, customer 4’s total of 8,500 comes from cancelled order 1004.

Suppose Finance wants to count only paid orders. A safer approach is to include status in the combined result and apply WHERE status = 'paid' in the outer query, before GROUP BY.

Why not filter each source separately before combining? Consider order 1004:

• The current system marks it cancelled.
• The legacy system still marks it paid.

If we filter the current table to paid orders before checking which legacy orders already exist, order 1004 may no longer be present to exclude its legacy copy. The old paid record could then enter the combined result, producing an incorrect paid-order total.

So far, we have used UNION and UNION ALL to combine rows from two queries. INTERSECT works differently. It returns only the distinct rows that appear in both query results.

Suppose the migration team wants to find customer IDs that have orders in both the current and legacy systems.

SELECT customer_id
FROM orders
INTERSECT
SELECT customer_id
FROM legacy_orders
ORDER BY customer_id;

How is this different from INNER JOIN?

We could also find customers appearing in both systems using a join:

SELECT DISTINCT o.customer_id
FROM orders AS o
INNER JOIN legacy_orders AS l
    ON l.customer_id = o.customer_id
ORDER BY o.customer_id;

Notice that this result does not contain NULL. That is because the join condition uses ordinary equality:

l.customer_id = o.customer_id

For guest orders, both customer IDs are NULL. Since NULL = NULL does not evaluate to TRUE, the rows do not match. The join also needs DISTINCT because a customer can have multiple orders, producing repeated matches. INTERSECT, on the other hand, automatically returns each matching row only once.

Which result is correct?

It depends on what the data represents. In our example, NULL means that the order was placed by a guest whose customer ID is unknown. Two guest orders with NULL customer IDs do not necessarily belong to the same customer. Therefore, including NULL would be misleading if the report is supposed to identify known customers who ordered in both systems.

To exclude guest orders, filter out NULL customer IDs from both queries:

SELECT customer_id
FROM orders
WHERE customer_id IS NOT NULL
INTERSECT
SELECT customer_id
FROM legacy_orders
WHERE customer_id IS NOT NULL
ORDER BY customer_id;

Now the result contains only known customer IDs present in both systems. However, there are cases where treating two NULL values as matching is useful, such as comparing records where NULL represents the same missing or unspecified category. The important point is to understand what NULL means in your data before deciding whether to include it or not.

EXCEPT returns rows from the first query that do not appear in the second query. It also removes duplicates, returning each distinct row only once.

Let’s use the customers table from P07.

The requirement is to find customers who have never placed an order in the current system.

We can use EXCEPT to subtract customer IDs found in orders from all customer IDs.

SELECT customer_id
FROM customers
EXCEPT
SELECT customer_id
FROM orders
ORDER BY customer_id;

The result contains only Esha, whose customer ID is 5.

Here’s how it works:

1. The first query returns all customer IDs: 1, 2, 3, 4, 5.
2. The second query returns customer IDs from orders.
3. EXCEPT removes IDs that appear in the second result.

Only customer 5 remains because Esha has never placed an order.

What happens to NULL values?

Remember that guest order 1007 has a NULL customer_id. In P08, we saw that a NULL inside a NOT IN subquery could cause the query to return no rows. EXCEPT behaves differently. It compares rows using the same rules discussed with INTERSECT: two NULL values are treated as matching. A NULL in the second result only removes a NULL from the first result, if one exists. It does not affect other values. Therefore, the guest order’s NULL does not prevent Esha from appearing.

What happens if we reverse the queries?

Unlike UNION and INTERSECT, the order of the queries matters with EXCEPT.

Consider:

SELECT customer_id
FROM orders
EXCEPT
SELECT customer_id
FROM customers
ORDER BY customer_id;

This query asks a different question: Which customer IDs appear in orders but not in the customers table?

The result is NULL because the current orders contain a guest order without a customer ID, while customers has no NULL customer ID. Every non-NULL customer ID from orders exists in customers, so those IDs are removed. The direction of the comparison changes the answer.

Finding products missing from the legacy system

Suppose we want to find products sold in the current system that were never sold in the legacy system.

SELECT product
FROM orders
EXCEPT
SELECT product
FROM legacy_orders
ORDER BY product;

These products appear in orders but not in legacy_orders. Notice that EXCEPT returns each product only once, even when the current system contains multiple orders for that product.

What if we want to compare how many times each product appears, rather than just whether it appears?

SQL provides EXCEPT ALL, which takes duplicate rows into account.

SELECT product
FROM orders
EXCEPT ALL
SELECT product
FROM legacy_orders
ORDER BY product;

When should we use the ALL forms?

The ALL forms are useful when duplicate counts matter, such as comparing two exports to verify that they contain the same rows the same number of times. For example, ordinary EXCEPT would find no difference between a table containing one copy of an order and another containing two identical copies. EXCEPT ALL can detect that difference. To check equality in both directions, run the comparison from A to B and from B to A.

Database support: DuckDB, PostgreSQL and MySQL 8.0.31 or later support INTERSECT ALL and EXCEPT ALL. SQL Server and SQLite support the distinct forms but not their ALL variants.

Before retiring the legacy system, the migration team wants to identify orders whose status has changed between the old and current systems.

We know that EXCEPT compares every selected column. An analyst uses it to compare each order’s ID and status across the two tables.

SELECT order_id, status
FROM legacy_orders
EXCEPT
SELECT order_id, status
FROM orders
ORDER BY order_id;

Finding only orders with changed statuses

If we want only orders that exist in both systems but have different statuses, we should first restrict the legacy query to order IDs present in the current system.

SELECT order_id, status
FROM legacy_orders
WHERE order_id IN (
    SELECT order_id
    FROM orders
)
EXCEPT
SELECT order_id, status
FROM orders
ORDER BY order_id;

Now orders 998 and 999 are excluded because their IDs do not exist in orders. Order 1004 remains because its legacy status differs from the current status. Notice that the WHERE clause applies only to the first query.

Showing the old and new values

EXCEPT tells us that a row differs, but it does not show both versions side by side. For a report showing the actual changes, a join is often easier to understand.

SELECT
    l.order_id,
    l.status AS legacy_status,
    o.status AS current_status
FROM legacy_orders AS l
INNER JOIN orders AS o
    ON o.order_id = l.order_id
WHERE l.status IS DISTINCT FROM o.status
ORDER BY l.order_id;

The INNER JOIN matches orders by ID, while IS DISTINCT FROM keeps rows whose statuses differ. We use IS DISTINCT FROM instead of <> because it handles NULL values correctly. For example, a change from NULL to 'paid' is detected as a difference.

Choosing EXCEPT vs JOIN?

Both approaches are useful, but they answer slightly different questions.

• EXCEPT is convenient when we want to find rows whose selected values are missing or different between two datasets.
• JOIN is more useful when we need to display the old and new values side by side.

For a table with many columns, EXCEPT can compare all selected columns without writing a separate comparison for each one. A join would require explicitly checking the columns we want to compare. We can also combine both approaches: use EXCEPT to identify changed records, then join those records back to their original tables to retrieve the details.

So far, we have used one set operator at a time.

Real queries may combine UNION, INTERSECT and EXCEPT in the same statement. When this happens, the order in which SQL evaluates the operators becomes important.

Consider a request from Marketing: Find customers from India who have either a paid order in the current system or any order in the legacy system.

An analyst writes three queries:

1. Customers with paid current orders.
2. Customers with legacy orders.
3. Customers from India.

Then combines them using UNION and INTERSECT.

SELECT customer_id
FROM orders
WHERE status = 'paid'
UNION
SELECT customer_id
FROM legacy_orders
INTERSECT
SELECT customer_id
FROM customers
WHERE country = 'India'
ORDER BY customer_id;

Why does this happen?

In DuckDB, INTERSECT has higher precedence than UNION. This means SQL evaluates the INTERSECT portion first, even though UNION appears earlier in the statement. The query is interpreted as:

All customers with paid current orders
UNION
Customers who appear in legacy orders AND are from India

Let’s look at the two parts.

• The paid current orders contain customer IDs: 1, 2, 3, NULL
• The legacy orders contain: 1, 3, 4, NULL
• The customers from India are: 1, 4
• The INTERSECT between legacy customers and Indian customers gives: 1, 4

Finally, UNION combines those values with the paid current customers: 1, 2, 3, 4, NULL

The problem is that the country condition was applied only to the legacy customers, not to the entire combined result.

The fix: Use parentheses

The requirement is to first combine both groups of customers and then keep only those from India. We can make that order explicit using parentheses:

(
    SELECT customer_id
    FROM orders
    WHERE status = 'paid'
 
    UNION
 
    SELECT customer_id
    FROM legacy_orders
)
INTERSECT
SELECT customer_id
FROM customers
WHERE country = 'India'
ORDER BY customer_id;

The result now contains only Asha and Dev.

Parentheses tell SQL to:

1. Combine customers with paid current orders and customers with legacy orders.
2. Compare the combined result with customers from India.
3. Keep only IDs that appear in both results.

The query now matches the requirement.

Another problem: Combining two EXCEPT results

Suppose we want to find every order record that differs between the legacy and current systems. We need two comparisons:

• Rows in legacy_orders but not in orders.
• Rows in orders but not in legacy_orders.

Someone combines both comparisons without parentheses:

SELECT order_id, status
FROM legacy_orders
 
EXCEPT
 
SELECT order_id, status
FROM orders
 
UNION ALL
 
SELECT order_id, status
FROM orders
 
EXCEPT
 
SELECT order_id, status
FROM legacy_orders
ORDER BY order_id;

The result contains only rows unique to the current system. The legacy-only rows have disappeared. Why?

For this query, the operators are evaluated from left to right. SQL first finds the legacy-only rows, then adds all current rows using UNION ALL. But the final EXCEPT removes rows that also exist in the legacy table. That removes the legacy-only rows we initially wanted to keep, along with matching current rows. The result contains only the current-side differences.

The fix: Group each comparison separately

We can use parentheses to keep the two EXCEPT operations independent, then combine their results. Let’s also add a side column to show where each unmatched row came from.

(
    SELECT
        order_id,
        status,
        'legacy only' AS side
    FROM legacy_orders
 
    EXCEPT
 
    SELECT
        order_id,
        status,
        'legacy only'
    FROM orders
)
UNION ALL
(
    SELECT
        order_id,
        status,
        'current only'
    FROM orders
 
    EXCEPT
 
    SELECT
        order_id,
        status,
        'current only'
    FROM legacy_orders
)
ORDER BY order_id, side;

Now both comparisons are preserved. Notice that order 1004 appears twice:

• (1004, 'paid') exists only as that combination in the legacy system.
• (1004, 'cancelled') exists only as that combination in the current system.

This tells us that order 1004 exists in both systems but has different statuses. Order 1001 does not appear because its selected values match across both tables.

Why use the same source label inside each EXCEPT? Both queries within each EXCEPT must use the same label. Otherwise, identical orders would have different side values and would no longer be considered duplicates. The label is added only after deciding which comparison each row belongs to.

Does every database use the same precedence?

No. Set-operator precedence can differ between SQL engines. DuckDB, PostgreSQL, SQL Server and MySQL give INTERSECT higher precedence than UNION and EXCEPT. SQLite and some Oracle versions apply different precedence rules. For this reason, it is better to use parentheses whenever a statement contains different kinds of set operations, rather than relying on an engine’s default evaluation order.

Set-operation queries often run successfully even when they produce incorrect results. You might see totals that are too high or too low, unexpected duplicate rows, missing records, or values appearing in the wrong columns.

Use this table to identify common problems:

Three checks before trusting the result

When a set-operation query produces unexpected results, start with these checks.

1. Compare row counts. For UNION ALL, the result should contain the sum of the input row counts. For UNION, check which rows were removed and why.
2. Check the selected columns. Every query must return the same number of columns, with the same meaning in corresponding positions. Remember that the first query determines the result’s column names.
3. Check where sorting, limiting and filtering happen. Confirm whether each WHERE, ORDER BY and LIMIT applies to one query or the combined result. Use parentheses when mixing different set operators.

We have now learned three ways to work with data from related tables.

Each is suited to a different type of question.

• Set operations combine or compare complete query results.
• JOIN connects matching rows and can return columns from both tables.
• EXISTS and NOT EXISTS check whether matching rows exist without multiplying the outer rows.

The best choice depends on what the requirement asks us to return.

Which approach should you use?

Three important differences

1. What does the query return?

Set operations return the columns selected by their queries. For example, using EXCEPT to find customers who have never ordered may return only customer ID 5. If we also need Esha’s name and other details, we would need to retrieve those columns separately or join the result back to customers. With NOT EXISTS, we can select Esha’s details directly from the outer customers table.

2. How are NULL values handled?

Set operations treat two NULL values as matching when comparing rows. A regular join using = does not match NULL with another NULL. For example, INTERSECT may return a shared NULL customer ID, while an INNER JOIN using equality would not match the corresponding rows. When a join should treat two NULL values as matching, use a NULL-safe comparison such as IS NOT DISTINCT FROM, where supported.

3. Which columns determine a match?

Set operations compare every selected column. A join or an EXISTS subquery compares only the conditions we specify. For example, two orders may have the same order_id but different statuses. A join on order_id matches those records, allowing us to compare their statuses. An EXCEPT query selecting both order_id and status treats those rows as different because their selected values do not match.

What about performance?

The duplicate-removing set operations, such as UNION, INTERSECT and EXCEPT, generally require extra work to identify matching rows, often using sorting or hashing. UNION ALL avoids that duplicate-removal step, making it a good choice when duplicates should be kept or have already been handled. However, the best-performing query depends on the database engine, indexes, data distribution and execution plan.

A rule of thumb is to choose the approach that correctly expresses the requirement first. Measure performance when needed.

Combining JOIN with UNION ALL

Set operations and joins can also work together. For example, MySQL does not support FULL JOIN directly. However, we can produce an equivalent result using LEFT JOIN, RIGHT JOIN and UNION ALL.

The requirement is to show every order from both systems, with its current and legacy status side by side.

Orders appearing in only one system must also remain in the result.

SELECT
    o.order_id,
    o.status AS current_status,
    l.status AS legacy_status
FROM orders AS o
LEFT JOIN legacy_orders AS l
    ON l.order_id = o.order_id
 
UNION ALL
 
SELECT
    l.order_id,
    o.status,
    l.status
FROM orders AS o
RIGHT JOIN legacy_orders AS l
    ON l.order_id = o.order_id
WHERE o.order_id IS NULL
ORDER BY order_id;

How does this work?

The first query uses a LEFT JOIN to keep every current order. If a matching legacy order exists, its status is included. Otherwise, legacy_status is NULL. However, this first query cannot return orders that exist only in legacy_orders, such as 998 and 999. The second query uses a RIGHT JOIN to keep legacy orders and checks: WHERE o.order_id IS NULL

This selects only legacy orders that have no matching current order. Finally, UNION ALL combines the two results. Because the second query adds only orders missing from the current system, no order is duplicated between the two results. The result is equivalent to a FULL JOIN on order_id, with the order ID taken from whichever table contains it.

Set operations let us combine and compare the results of separate SQL queries without joining their rows side by side.

• UNION ALL keeps every row, while UNION removes identical rows based on the selected columns.
• INTERSECT finds rows present in both results, while EXCEPT finds rows present in the first result but missing from the second.
• Set operations match columns by position, treat NULL values as matching during row comparison, and can produce unexpected results when records are duplicated or their selected values differ.

Before using a set operation, consider what makes two records the same, whether duplicates should remain, and which result should take priority when records overlap.

Next, in P10, we will learn how to structure larger queries using subqueries and common table expressions (CTEs). These let us break complex SQL problems into smaller, readable steps and reuse intermediate results.