Common Mistakes in SQL Set Operators

Introduction

SQL set operators look simple because they connect complete SELECT statements with a small number of keywords. Yet incorrect assumptions about duplicates, column position, nulls, ordering, identity, and direction can produce results that are syntactically valid but operationally wrong. Those defects are more dangerous than obvious syntax errors because reports and reconciliations may appear plausible.

UNION combines results and removes duplicate complete rows. UNION ALL preserves every occurrence. INTERSECT returns common distinct rows, while EXCEPT or Oracle MINUS returns rows in the first result that are absent from the second. Correct use depends on business grain and the exact output projection.

This tutorial explains common structural, semantic, data-quality, portability, and performance mistakes. Each section describes why the mistake occurs, how it affects results, and how to correct or test it.

Why Set Operator Mistakes Matter

A mistake can remove valid transactions, duplicate campaign recipients, misstate revenue, hide failed migrations, report false environment drift, or consume unnecessary memory and temporary storage. The right debugging question is not only whether the SQL executes, but whether every output row has the intended meaning.

Mistake: Confusing UNION with UNION ALL

Developers sometimes assume the operators are interchangeable. They are not. If employee 103 appears in two regional systems, UNION removes an identical selected row while UNION ALL preserves both occurrences.

-- Distinct combined population
SELECT employee_id FROM employees_us
UNION
SELECT employee_id FROM employees_india;

-- Every source occurrence
SELECT employee_id FROM employees_us
UNION ALL
SELECT employee_id FROM employees_india;

Choose based on the requirement. A unique company roster may need UNION; assignments, transactions, logs, and test executions usually need UNION ALL with source context.

Mistake: Using UNION ALL for a Unique Population

The opposite error occurs when a campaign, customer count, or access list must contain each business identity once. UNION ALL can repeat people present in multiple branches. Use UNION, or append first and deduplicate by the documented business key when complete-row equality is not the correct identity rule.

Mistake: Expecting UNION to Deduplicate One Column

UNION compares every projected column. Two rows with the same customer ID but different names or statuses remain different.

SELECT customer_id, customer_name, status
FROM online_customers
UNION
SELECT customer_id, customer_name, status
FROM store_customers;

If one row per customer is required, first define how conflicting attributes are resolved. Plain union cannot choose the newest, verified, or highest-quality record.

Mistake: Removing Valid Transactions

Two payments may have equal customer, amount, date, and status while remaining separate transactions. A projection that omits transaction ID followed by UNION can erase a legitimate event. Preserve stable event identity and use UNION ALL for ledgers unless a governed replay-removal rule says otherwise.

Mistake: Returning Different Column Counts

Every branch must return the same number of columns. This fails:

SELECT employee_id, employee_name
FROM employees_us
UNION
SELECT employee_id, employee_name, department
FROM employees_india;

Align projections intentionally. Do not add an arbitrary value merely to satisfy syntax; every output position needs one shared meaning.

Mistake: Using Incompatible Data Types

Corresponding positions must resolve to compatible types. Combining a numeric employee ID with a free-form department name may fail or trigger undesirable implicit conversion. Cast deliberately to a type that preserves precision, length, and indexing behavior.

SELECT CAST(employee_id AS VARCHAR(30)) AS entity_code
FROM employees
UNION ALL
SELECT contractor_code
FROM contractors;

A cast makes SQL legal, but it does not prove that employee IDs and contractor codes belong to the same business domain.

Mistake: Ignoring Positional Matching

Set operators match columns by position, not alias. Swapping name and city can still execute when both are text:

SELECT customer_name, city FROM online_customers
UNION ALL
SELECT city, customer_name FROM store_customers;

The output is corrupted semantically even though the database accepts it. Format branches vertically and review each position as one shared contract.

Mistake: Using SELECT *

SELECT * hides position and makes the query dependent on schema order. A source adding a column can break the statement or change data placement. Explicit projections document intent, avoid unnecessary width, and protect long-lived queries from unrelated schema changes.

Mistake: Trusting Aliases from Later Branches

Final output names normally come from the first SELECT. Aliases in later branches do not rename final columns. Put clear canonical aliases in the first branch and ensure later positions match their meaning.

Mistake: Assuming UNION Sorts the Result

A plan may sort internally to eliminate duplicates, but SQL does not guarantee presentation order without a final ORDER BY. Physical plans can change, and hash-based duplicate elimination may produce another order entirely.

SELECT employee_id, employee_name FROM employees_us
UNION
SELECT employee_id, employee_name FROM employees_india
ORDER BY employee_name, employee_id;

Mistake: Placing ORDER BY in Every Branch

In ordinary set queries, one final ORDER BY controls the complete result. Branch ordering is usually invalid, ignored, or meaningful only inside a parenthesized limiting operation supported by the product. Do not sort intermediate rows unless it changes semantics or supports a verified plan benefit.

Mistake: Applying LIMIT without Deterministic Order

A limit without ORDER BY returns an arbitrary qualifying subset. Ordering by a non-unique value is still unstable for pagination. Add a unique tiebreaker:

ORDER BY event_time DESC, event_id DESC
FETCH FIRST 50 ROWS ONLY;

For deep pages, consider keyset pagination rather than repeatedly skipping a large offset.

Mistake: Assuming UNION ALL Preserves Source Order

UNION ALL preserves row occurrences, not table or branch presentation order. Parallel execution and optimizer choices can interleave or reorder results. Add a source label and final ordering when source grouping matters.

Mistake: Reversing EXCEPT

Set difference is directional. Source minus target finds missing target records, while target minus source finds unexpected target records.

-- Missing from target
SELECT business_key FROM source_data
EXCEPT
SELECT business_key FROM target_data;

-- Unexpected in target
SELECT business_key FROM target_data
EXCEPT
SELECT business_key FROM source_data;

Run both directions for reconciliation and label each output clearly.

Mistake: Confusing INTERSECT with UNION

UNION returns rows found in either input, while INTERSECT returns rows common to both. Confusing them can turn a shared-customer segment into the entire customer population. Translate the business wording into set language before coding.

Mistake: Assuming INTERSECT Preserves Duplicates

Standard intersection returns a distinct common set unless a product-specific INTERSECT ALL is intentionally supported and used. If occurrence counts matter, aggregate each side and define how counts combine, often using the smaller occurrence count for multiset intersection.

Mistake: Assuming EXCEPT Preserves Duplicates

Standard EXCEPT returns distinct differences. It is suitable for missing keys, not for measuring unmatched event occurrences. Use identifiers, grouped counts, or a supported multiset operator when multiplicity matters.

Mistake: Misunderstanding NULL in Set Operations

Distinct set operations generally treat rows with nulls in corresponding positions as duplicates for set processing. This differs from ordinary NULL = NULL, which is unknown. A rewrite using joins, EXISTS, or NOT IN can therefore produce different results unless null handling is made equivalent.

Mistake: Treating EXCEPT and NOT IN as Identical

A null returned by a NOT IN subquery can cause the predicate to evaluate as unknown for every candidate. EXCEPT and NOT EXISTS have different null mechanics.

SELECT s.customer_id
FROM source_customers AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM target_customers AS t
    WHERE t.customer_id = s.customer_id
);

Choose a formulation whose null and duplicate behavior matches the requirement, then test nullable keys explicitly.

Mistake: Assuming INTERSECT and INNER JOIN Are Identical

An inner join can multiply rows when either side contains repeated keys. INTERSECT returns distinct common projected rows. A join also supports non-equality conditions and can return columns from both sides. They may answer similar membership questions, but they are not automatically equivalent.

Mistake: Assuming EXCEPT and LEFT JOIN Are Identical

A left anti-join can emulate set difference by checking for no match, but duplicate preservation, null-safe equality, and projection differ. If the right-side key itself can be null, testing an unrelated nullable column for absence can misclassify rows. Use a non-null match key or NOT EXISTS.

Mistake: Ignoring Operator Precedence

Mixed UNION, INTERSECT, and EXCEPT expressions can be read differently by people and products. Make stages explicit with parentheses or CTEs:

WITH eligible_people AS (
    SELECT person_id FROM employees
    UNION
    SELECT person_id FROM contractors
)
SELECT person_id FROM eligible_people
EXCEPT
SELECT person_id FROM blocked_people;

Mistake: Combining Different Business Attributes

Compatible data types do not prove compatible meaning. Appending customer IDs to product IDs, salaries to quantities, or local timestamps to UTC timestamps creates a misleading set. Every position needs one business definition, unit, and domain.

Mistake: Comparing Different Row Grain

A monthly customer summary and a transaction row can expose customer, date, and amount while representing different facts. Combining them causes invalid counts and totals. Transform branches to one common grain first or retain an explicit record type and prevent inappropriate aggregation.

Mistake: Ignoring Source Identity

After sources are combined, investigators may need to know where a row originated. Add a source column for event histories and reconciliation:

SELECT transaction_id, amount, 'ONLINE' AS source_system
FROM online_transactions
UNION ALL
SELECT transaction_id, amount, 'STORE'
FROM store_transactions;

Remember that the source column becomes part of complete-row equality and can prevent UNION from collapsing otherwise identical rows.

Mistake: Ignoring Case, Spaces, and Collation

Text equality depends on collation and stored values. ALICE@example.com, alice@example.com, and a value with trailing spaces may compare differently across products or types. Normalize according to field semantics, not as a blanket fix. Case can be significant in account codes, hashes, and external identifiers.

Mistake: Ignoring Dates, Precision, and Time Zones

A date and timestamp do not describe identical precision. Two systems can store the same instant in different time zones or round fractional seconds differently. Convert to one documented temporal standard before comparison and preserve original values for audit. Avoid truncating to a date when time is part of business identity.

Mistake: Ignoring Currency and Units

Combining dollar amounts with cents or USD with EUR creates valid-looking but false totals. Standardize unit, currency, exchange-rate source, and effective time before appending. Include currency in the grain when no approved conversion is applied.

Mistake: Counting the Wrong Thing

COUNT(*) after UNION ALL counts source occurrences. COUNT(*) after UNION counts distinct complete rows. COUNT(DISTINCT customer_id) counts unique non-null customer IDs. These answer different questions.

WITH all_customers AS (
    SELECT customer_id FROM online_customers
    UNION ALL
    SELECT customer_id FROM store_customers
)
SELECT COUNT(*) AS occurrences,
       COUNT(DISTINCT customer_id) AS unique_customers
FROM all_customers;

Mistake: Combining Aggregates Incorrectly

Appending regional totals does not automatically create a company total. If the same grouping key can appear in several branches, aggregate again outside:

WITH branch_totals AS (
    SELECT region, SUM(amount) AS revenue FROM online_sales GROUP BY region
    UNION ALL
    SELECT region, SUM(amount) FROM store_sales GROUP BY region
)
SELECT region, SUM(revenue) AS total_revenue
FROM branch_totals
GROUP BY region;

Averages require special care: average branch averages are generally wrong unless branch counts are equal. Combine sums and counts, then calculate the weighted average.

Mistake: Using UNION Instead of JOIN

Set operators append or compare compatible rows vertically. Joins relate entities horizontally. Use a join to place customer names beside orders; use a union to append current and archived orders. Trying to replace one concept with the other creates incorrect shape or multiplication.

Mistake: Using UNION for a Simple OR Condition

Two queries against the same table can sometimes be one query with OR or IN. The formulations can differ in duplicate behavior and optimizer choices, so rewrite only after proving equivalence. A union can remain clearer when branches need different joins or projections.

Mistake: Adding DISTINCT around UNION

UNION already returns distinct complete rows. Wrapping it in another DISTINCT is usually redundant and obscures intent. If deduplication belongs at a different business-key level, express that level explicitly with grouping or window functions.

Mistake: Ignoring Database Portability

Oracle traditionally uses MINUS, while SQL Server and PostgreSQL use EXCEPT. Support for INTERSECT ALL, EXCEPT ALL, ordering, limit syntax, and parenthesized branches varies by product and version. Do not copy syntax without confirming the target platform.

Mistake: Assuming CTEs Improve Performance

A CTE primarily improves query organization. The optimizer may inline, materialize, or reevaluate it according to product rules. Reusing an expensive combined result may benefit from a temporary table with indexes and statistics, but staging also adds writes and lifecycle complexity. Measure both approaches.

Mistake: Ignoring Filtering Opportunities

Filtering only after a massive combination can send unnecessary rows into sorting, hashing, aggregation, or network transfer. Push valid predicates into branches while preserving semantics. Verify predicate pushdown in the execution plan when views, expressions, or remote sources are involved.

Mistake: Selecting Too Many Columns

Wide rows increase scan I/O, memory, comparison cost, temporary storage, and network transfer. For membership questions, project only stable keys. Retrieve descriptive details after identifying the relevant population.

Mistake: Ignoring Execution Plans

Set operations may use append, sort, hash, semi-join, anti-join, temporary storage, or parallel exchanges. Inspect actual row counts, estimates, memory grants, spills, and branch access paths. A query that performs well on sample data can fail under production volume or skew.

Mistake: Ignoring Data Quality Before Comparison

Set operators faithfully compare what they receive. Invalid mappings, malformed identifiers, duplicate business keys, inconsistent units, and stale reference data can make technically correct SQL produce misleading conclusions. Profile each branch before trusting the difference or overlap.

Mistake: Comparing Live Systems at Different Times

Source and target can change during a reconciliation. Without a shared cutoff, batch ID, or snapshot, legitimate new records appear as differences. Record temporal boundaries and account for approved processing lag before escalating exceptions.

Mistake: Treating Set Operations as Data Modification

Set operators return query results; they do not merge, delete, or repair source rows. They can feed an INSERT, update workflow, or deletion process, but those operations require transactions, review, audit, and recovery controls. Keep detection separate from remediation.

Mistake: Confusing Technical and Business Duplicates

Complete-row duplicates, repeated technical IDs, and repeated business identities are different problems. Two rows with different surrogate IDs can represent the same customer, while two equal transaction values can represent separate events. UNION only knows whether the selected tuples compare as equal; it does not understand customer identity or event intent.

Define the business key before deduplicating. For a tenant-aware customer table, identity might be tenant plus normalized email. For a payment, identity should normally include a provider transaction ID or idempotency key. Once the key is known, use grouping or a window function to detect and select records rather than assuming complete-row distinctness solves the problem.

WITH ranked AS (
    SELECT customer_id,
           ROW_NUMBER() OVER (
               PARTITION BY tenant_id, normalized_email
               ORDER BY is_verified DESC, updated_at DESC, customer_id DESC
           ) AS duplicate_rank
    FROM customers
)
SELECT customer_id
FROM ranked
WHERE duplicate_rank > 1;

This query identifies business-key duplicates and preserves a deterministic winner rule. It should still be reviewed before any data-changing action.

Mistake: Combining Local Identifiers without Identity Mapping

Customer 105 in an online platform may be unrelated to customer 105 in a store system. A union or intersection of those local IDs can create false overlap. The same risk applies to employee numbers, product codes, supplier IDs, and environment-specific test IDs.

Map each source identity to a governed master key before comparing populations. Store the source system, local ID, master ID, mapping method, effective dates, and review state. Ambiguous mappings should remain unresolved rather than being guessed from a name or email alone. Set operators can then compare trustworthy master identities.

SELECT m.master_customer_id
FROM online_customers AS c
JOIN customer_identity_map AS m
  ON m.source_system = 'ONLINE'
 AND m.source_customer_id = c.customer_id
INTERSECT
SELECT m.master_customer_id
FROM store_customers AS c
JOIN customer_identity_map AS m
  ON m.source_system = 'STORE'
 AND m.source_customer_id = c.customer_id;

Mistake: Hiding Attribute Conflicts with a Narrow Projection

Comparing only customer ID can prove that an identity exists on both sides, but it cannot prove that name, status, consent, balance, or ownership matches. Teams sometimes declare a migration successful after a key-only intersection while important attributes are wrong.

Use layered validation. First compare keys to classify missing and extra records. Next compare canonical business attributes for shared keys. Finally reconcile independent totals and distributions. This separation produces clear diagnostics: identity coverage, attribute correctness, and aggregate control are measured independently.

Do not simply add every attribute to one huge EXCEPT. Volatile audit timestamps and source-specific metadata can create noise. Document which fields are expected to match and why.

Mistake: Over-Normalizing Comparison Values

Lowercasing, trimming, removing punctuation, and converting blanks to null can reduce formatting noise, but aggressive normalization can merge distinct values. Case may be significant for account codes, spaces may matter in fixed identifiers, and punctuation can distinguish product versions.

Normalize by field semantics and keep the original value available. Version the normalization rule when it supports migration or reconciliation, because changing the rule changes set membership. Profile how many values are altered and manually review representative collisions before using a normalized expression as a business key.

Mistake: Ignoring Conflicts during Migration

When the same business key appears several times in a source, UNION may hide only exact repeated tuples while preserving conflicting versions. Loading those results into a unique target can fail or, with an unsafe upsert, overwrite data unpredictably. Migration design needs an explicit conflict policy.

Profile duplicate groups before loading. Decide whether to reject the group, select a trusted source, keep the latest valid record, merge non-conflicting attributes, or send the case for manual review. Record the losing IDs and winner reason. After repair, enforce the target uniqueness rule so the same defect cannot re-enter unnoticed.

SELECT business_key,
       COUNT(*) AS row_count,
       COUNT(DISTINCT status) AS status_versions
FROM migration_stage
GROUP BY business_key
HAVING COUNT(*) > 1;

Mistake: Benchmarking Non-Equivalent Queries

A common performance demonstration compares UNION with UNION ALL and concludes that the latter is better because it runs faster. The comparison is invalid when the required output must be distinct. Removing work by changing the answer is not optimization.

Benchmark only equivalent results with production-like volume, overlap, skew, nulls, row width, and concurrency. Capture output counts or checksums along with elapsed time, CPU, reads, memory, spills, and network transfer. Run enough iterations to separate stable behavior from cache and background-load noise.

When testing rewrites such as INTERSECT versus EXISTS, verify duplicate and null behavior first. Then compare actual plans and metrics instead of relying on general rules about which syntax is faster.

Mistake: Debugging Only the Final Result

When a set query returns an unexpected count, adding more predicates to the final statement can hide the cause. Debug branch by branch. Capture each branch count, distinct business keys, null rates, date range, and a small deterministic sample. Then measure overlap and each directional difference.

For a two-source comparison, the relationship can be described by source-only, target-only, and common populations. If those counts do not reconcile with source totals, duplicates exist inside a branch or the projections use inconsistent grain. This decomposition makes the problem observable before complex joins or aggregates are added.

Persist a stable exception set for long investigations. Rerunning against changing live data can make records appear and disappear, obscuring whether the query or source process changed.

Mistake: Omitting Ownership and Audit Evidence

A difference query can identify an exception without explaining who should resolve it. Production reconciliation needs a run ID, snapshot or cutoff, rule version, source counts, exception type, business impact, owner, status, and resolution evidence. Otherwise the same differences are repeatedly rediscovered without closure.

Classify expected differences separately from defects, but require an approved reason and review date. Broad permanent exclusion predicates create blind spots. Monitoring should track new exceptions, unresolved age, closure rate, and sudden changes by source or batch.

Mistake: Forgetting Downstream Semantics

A technically correct combined dataset can be misused downstream. A reporting tool may sum rows that include both detail and summary grain, an API may paginate without stable order, or a consumer may treat a source occurrence count as unique customers. Publish the output contract with the query or view.

Document row grain, key, duplicate policy, refresh time, units, time zone, source lineage, and ordering guarantees. Add consumer-facing tests for totals and pagination. Data correctness includes how the result is interpreted, not only how it is produced.

Debugging Workflow

  1. Write the expected row grain and duplicate policy.
  2. Run each branch independently and verify columns, types, counts, and samples.
  3. Confirm positional meaning and explicit casts.
  4. Measure overlap, source-only rows, and target-only rows separately.
  5. Test nulls, case, spaces, time zones, precision, and units.
  6. Add deterministic final ordering when output order matters.
  7. Compare expected and actual results with edge-case fixtures.
  8. Inspect actual execution plans and runtime metrics.
  9. Validate business totals and downstream impact.
  10. Document the corrected rule and add regression tests.

Testing Checklist

Test empty inputs, one empty branch, complete overlap, no overlap, partial overlap, duplicates within each branch, duplicate business keys with conflicting attributes, nulls, mixed case, trailing spaces, boundary timestamps, currency and unit differences, large skewed groups, and several product versions where portability matters.

For migration, security, and configuration comparisons, run differences in both directions. For transaction and audit data, prove that every event occurrence remains. For pagination, repeat adjacent pages and verify no rows are skipped or repeated.

Prevention Practices

Use shared data contracts, stable cross-system identifiers, explicit projections, source labels, database constraints, documented time-zone and unit standards, consistent snapshot boundaries, and code review checklists. Automate result-count and control-total assertions for critical jobs.

Monitor overlap and difference trends after deployment. A sudden change can reveal a missing feed, new schema, altered collation, mapping failure, or replay problem before users notice incorrect reports.

Code Review Questions

Before approving a set query, ask what one row represents, whether duplicate occurrences are valid, and whether every position has the same meaning in every branch. Confirm that identifiers are shared across systems, casts preserve data, source lineage remains available, and date or snapshot boundaries are aligned.

Review null behavior, comparison direction, operator precedence, final ordering, and pagination stability. Check whether a join, existence predicate, grouping operation, or window function better expresses the requirement. Finally, require edge-case tests, independent control totals, and an actual execution-plan review for high-volume workloads. These questions catch semantic defects that formatting and syntax validation cannot detect.

Interview Questions and Answers

What is the most common UNION mistake?

Choosing UNION or UNION ALL without defining whether duplicate occurrences are valid.

Does UNION remove duplicates by primary key?

No. It compares all selected output columns.

Why is EXCEPT direction important?

First minus second and second minus first answer different missing-record questions.

Why can NOT IN differ from EXCEPT?

Nulls in a NOT IN subquery can make comparisons unknown, while set difference follows its own null semantics.

Why is SELECT * risky with set operators?

It hides positional meaning, increases row width, and makes the query vulnerable to schema changes.

Does UNION guarantee sorted output?

No. Only a final ORDER BY guarantees presentation order.

Are INTERSECT and INNER JOIN equivalent?

No. Joins can multiply matches and return both-side columns, while intersection returns distinct common projected rows.

How should set operator defects be debugged?

Validate branches, contracts, grain, duplicates, nulls, direction, edge cases, and actual execution plans in that order.

Mistake Summary

SymptomLikely causeCorrection
Missing valid eventsUNION used on occurrence dataUse UNION ALL with stable IDs
Repeated audience membersUNION ALL used for a distinct populationUse the correct business-key deduplication
Values under wrong headingsColumn positions differAlign explicit projections
Unexpected migration gapsDirection, mapping, or snapshot mismatchCompare both directions with one cutoff
Unstable pagesMissing deterministic orderAdd final order and unique tiebreaker
Slow distinct set queryWide rows, late filters, spillsReduce inputs and inspect the plan

Quick Reference

-- Distinct combined population
SELECT business_key FROM source_a
UNION
SELECT business_key FROM source_b;

-- Preserve every occurrence
SELECT event_id, event_time FROM source_a
UNION ALL
SELECT event_id, event_time FROM source_b;

-- Common rows
SELECT business_key FROM source_a
INTERSECT
SELECT business_key FROM source_b;

-- Directional difference
SELECT business_key FROM source_a
EXCEPT
SELECT business_key FROM source_b;

Key Takeaway

Most set operator mistakes come from treating compatible syntax as proof of compatible meaning. The correct operator depends on row grain, business identity, duplicate policy, comparison direction, null behavior, and output ordering.

Use explicit projections, align types and semantics, preserve valid occurrences, compare differences in both directions, standardize only with documented rules, and test realistic edge cases. Then inspect execution plans and business controls. This process prevents both obvious SQL failures and the more dangerous plausible-looking wrong result.