Combining Query Results in SQL

Introduction

Applications rarely store every useful fact in one table. Employee data may be divided by region, orders may be separated into current and archive tables, customers may arrive from online and physical stores, and migration teams may need to compare a source system with a target system. SQL provides several ways to turn those separate datasets into one meaningful result.

Combining query results means merging, comparing, enriching, or organizing the rows returned by two or more queries. Set operators combine compatible results vertically. Joins combine related columns horizontally. Subqueries and common table expressions let one result become an input to another operation. The correct method depends on row grain, business intent, duplicate policy, and the relationship between datasets.

This tutorial connects those methods into a practical workflow. It explains syntax, compatibility rules, null and duplicate behavior, filtering, aggregation, ordering, pagination, performance, portability, testing, and real project scenarios.

What Is Combining Query Results?

Combining query results is the process of integrating data returned by separate SELECT statements into a unified result for reporting, analysis, comparison, reconciliation, or application processing. A simple example appends employees from two regional tables:

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

The result has the same three-column shape as each input and contains rows from both. This is a vertical combination because one result is placed below the other.

Why Applications Combine Results

Organizations combine results to consolidate regional records, join current and historical data, compare systems, identify overlap, locate missing records, prepare organization-wide reports, calculate cross-source totals, validate migrations, and analyze logs or test runs. The technique is not limited to tables in one schema. Subject to database capabilities and permissions, inputs may come from different schemas, databases, linked servers, foreign tables, or staged external files.

The output should still represent a clearly stated row grain. If one branch returns one row per employee and another returns one row per employee assignment, appending them creates a mixed-grain result that is difficult to interpret even when the SQL executes.

Main Combination Methods

MethodPurposeResult shape
UNIONAppend compatible rows and remove duplicatesVertical
UNION ALLAppend every compatible rowVertical
INTERSECTReturn rows common to both resultsVertical comparison
EXCEPT or MINUSReturn rows found only in the first resultVertical comparison
JOINMatch related rows and add columnsHorizontal
Subquery or CTEUse one result inside a larger queryDepends on outer query

Vertical and Horizontal Combination

Set operators combine rows vertically. Every branch must return the same number of columns in the same semantic order, and corresponding data types must be compatible. The final result has one shared column structure.

Joins combine columns horizontally by matching rows through a relationship such as customer_id. A join can return customer columns beside order columns. A set operator cannot do that because it compares or appends complete row positions rather than following relationships.

Using UNION

UNION appends results and removes complete duplicate rows from the combined output.

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

If employee 103 and the same name appear in both branches, only one selected tuple remains. Duplicate elimination compares every output column, not a primary key automatically. If salary differs between branches, both rows remain because the complete selected rows are different.

Using UNION ALL

UNION ALL appends all occurrences without a global duplicate-removal step.

SELECT order_id, customer_id, total_amount
FROM current_orders
UNION ALL
SELECT order_id, customer_id, total_amount
FROM archived_orders;

This is usually the correct choice when branches are known to be disjoint or when repeated values represent separate facts. Transactions, logs, API requests, and test executions can be equal in visible columns while still representing different events.

UNION vs UNION ALL

Choose UNION when the requirement is a distinct combined set. Choose UNION ALL when every occurrence must remain or when uniqueness is already guaranteed. UNION ALL is commonly faster because it can avoid sorting or hashing the complete result, but correctness comes before speed.

Do not add UNION merely to hide unexplained overlap. Determine whether the overlap is valid, caused by branch predicates, or evidence of duplicate stored data.

Using INTERSECT

INTERSECT returns distinct rows present in both query results.

SELECT customer_id, customer_name
FROM online_customers
INTERSECT
SELECT customer_id, customer_name
FROM store_customers;

This can identify customers present in both channels or records that match between source and target systems. Product support varies. MySQL commonly uses joins or EXISTS for equivalent logic, while PostgreSQL, SQL Server, and Oracle support intersection syntax.

Using EXCEPT and MINUS

EXCEPT returns distinct rows from the first query that are absent from the second. Oracle traditionally uses MINUS for the same directional set-difference idea.

SELECT customer_id, customer_name
FROM online_customers
EXCEPT
SELECT customer_id, customer_name
FROM store_customers;

Direction matters. Reversing the branches answers a different question. Run both directions during reconciliation when you need source-only and target-only records.

Set Operator Compatibility Rules

Each participating query must return the same number of columns. Corresponding columns are matched by position, not by alias or source name. Their types must be compatible or explicitly converted. Output names normally come from the first branch, so use clear aliases there.

SELECT employee_id AS entity_id,
       employee_name AS entity_name,
       CAST(salary AS DECIMAL(12, 2)) AS amount
FROM employees_us
UNION ALL
SELECT contractor_id,
       contractor_name,
       CAST(contract_rate AS DECIMAL(12, 2))
FROM contractors;

This query is structurally compatible because both branches return identifier, name, and numeric amount in matching positions. A database cannot detect that two compatible text columns were accidentally reversed, so semantic review remains necessary.

Using Constant Source Columns

A literal column can preserve lineage after several sources are combined.

SELECT customer_id, customer_name, 'ONLINE' AS source_system
FROM online_customers
UNION ALL
SELECT customer_id, customer_name, 'STORE' AS source_system
FROM store_customers;

Source labels help audit, filter, aggregate, and troubleshoot the unified data. They also affect duplicate behavior: rows that were identical without the label become different once the source value is selected.

Using NULL Placeholders

Sources do not always expose identical attributes. A typed null can align the structures without inventing a value.

SELECT employee_id, employee_name, salary,
       CAST(NULL AS VARCHAR(30)) AS contract_type
FROM employees
UNION ALL
SELECT contractor_id, contractor_name,
       contract_rate, contract_type
FROM contractors;

Explicit typing improves portability because an untyped null may be inferred differently across products or contexts.

Filtering Branches Before Combination

Each branch can have its own WHERE clause. Filtering early reduces rows passed to the combination step and makes branch intent visible.

SELECT order_id, order_date, total_amount, 'CURRENT' AS source_name
FROM current_orders
WHERE order_date >= DATE '2026-01-01'
UNION ALL
SELECT order_id, order_date, total_amount, 'ARCHIVE' AS source_name
FROM archived_orders
WHERE order_date >= DATE '2026-01-01';

Date literal and function syntax varies by product. Use the target database's supported form and avoid applying functions to indexed date columns when a range predicate can be used.

Filtering the Combined Result

When one condition must apply after combination, wrap the set operation in a derived table or CTE.

WITH all_orders AS (
    SELECT order_id, customer_id, total_amount
    FROM domestic_orders
    UNION ALL
    SELECT order_id, customer_id, total_amount
    FROM international_orders
)
SELECT order_id, customer_id, total_amount
FROM all_orders
WHERE total_amount > 1000;

This separates source retrieval from final business filtering and gives the combined result a reusable name.

Combining Aggregate Results

Branches can aggregate before they are appended. This is useful for regional summaries, but summing branch aggregates may be necessary to obtain an overall total.

WITH regional_sales AS (
    SELECT region, SUM(total_amount) AS revenue
    FROM domestic_orders
    GROUP BY region
    UNION ALL
    SELECT region, SUM(total_amount) AS revenue
    FROM international_orders
    GROUP BY region
)
SELECT region, SUM(revenue) AS total_revenue
FROM regional_sales
GROUP BY region;

The outer aggregation matters when the same region can occur in several branches. Without it, the output has one summary row per source-region pair rather than one row per region.

Using HAVING in Combined Queries

HAVING filters groups within a branch or after a combined aggregation. Place it according to the intended level. A branch-level condition removes small source groups before union, while an outer condition evaluates the consolidated total.

Combining Three or More Results

Set operators can connect any practical number of compatible queries.

SELECT product_id, quantity, 'NORTH' AS warehouse
FROM inventory_north
UNION ALL
SELECT product_id, quantity, 'SOUTH'
FROM inventory_south
UNION ALL
SELECT product_id, quantity, 'WEST'
FROM inventory_west;

As branch count grows, use consistent formatting and explicit source labels. If the same structure is repeated across many physical tables, reconsider partitioning or data modeling because operational complexity can grow quickly.

Set Operator Precedence

Mixed set operators can be difficult to read and product precedence rules may differ. Parenthesize logical stages or use CTEs instead of depending on memory.

WITH active_people AS (
    SELECT person_id FROM employees
    UNION
    SELECT person_id FROM contractors
), blocked_people AS (
    SELECT person_id FROM blocked_accounts
)
SELECT person_id FROM active_people
EXCEPT
SELECT person_id FROM blocked_people;

This form clearly communicates that employees and contractors are combined before blocked people are removed.

Combining Results with JOINs

A join is appropriate when separate tables describe related parts of one row.

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

The result is horizontal: customer and order columns appear together. One customer may appear several times because the result grain is one row per matching order. That multiplication is expected unless the requirement specifies one row per customer.

UNION vs JOIN

UNION requires compatible projections and appends rows. JOIN follows a relationship and adds columns. Use set operations for same-shaped populations such as current and archive orders. Use joins to enrich orders with customer names, products, or payment details.

Some tasks use both: join within each branch to prepare a common shape, then append the prepared results.

Using EXISTS and NOT EXISTS

EXISTS tests whether a related row is present without multiplying the outer result.

SELECT c.customer_id, c.customer_name
FROM online_customers AS c
WHERE EXISTS (
    SELECT 1
    FROM store_customers AS s
    WHERE s.customer_id = c.customer_id
);

This can express intersection by a key rather than by a complete selected tuple. NOT EXISTS expresses directional difference and handles nullable join keys more safely than many NOT IN patterns.

INTERSECT vs EXISTS

INTERSECT compares complete projected rows and returns a distinct set. EXISTS correlates on an explicit condition and can return any columns from the outer row. Use intersection for symmetric set comparison and EXISTS when relationship logic or broader outer details are needed.

EXCEPT vs NOT EXISTS

EXCEPT is concise for comparing compatible results. NOT EXISTS is often more flexible when matches use several predicates, transformations, effective dates, or nullable values. Product support and execution plans also influence the choice.

Subqueries, Derived Tables, and CTEs

A derived table puts a combined result directly in the FROM clause. A CTE gives it a name for the duration of one statement. Neither automatically stores data or guarantees materialization; the optimizer may transform the query.

Use CTEs to clarify stages such as extraction, standardization, combination, enrichment, aggregation, and final filtering. Recursive CTEs solve hierarchical or iterative problems, but they are not a substitute for a simple union of independent sources.

Adding Calculated and CASE Columns

Every branch may calculate values as long as corresponding output types remain compatible. A CASE expression can standardize categories from different systems.

SELECT order_id,
       total_amount,
       CASE WHEN total_amount >= 1000 THEN 'HIGH' ELSE 'STANDARD' END AS value_band
FROM domestic_orders
UNION ALL
SELECT order_id,
       total_amount,
       CASE WHEN total_amount >= 1000 THEN 'HIGH' ELSE 'STANDARD' END
FROM international_orders;

For long transformations, standardize each source in separate CTEs so business mappings are testable before combination.

Ordering Combined Results

SQL does not guarantee output order without ORDER BY. In a set query, the final ordering normally appears once, after the last branch, and applies to the whole result.

SELECT employee_id, employee_name, 'US' AS region
FROM employees_us
UNION ALL
SELECT employee_id, employee_name, 'INDIA'
FROM employees_india
ORDER BY region, employee_name, employee_id;

Use output column names or supported ordinal positions. Ordering inside a branch is generally irrelevant unless it supports a branch-specific limiting operation permitted by the database.

Pagination and Window Functions

Paginate only after constructing and deterministically ordering the complete result. A stable unique tiebreaker is essential; ordering by a non-unique date alone can cause rows to move between pages.

WITH all_events AS (
    SELECT event_id, event_time, event_type FROM api_events
    UNION ALL
    SELECT event_id, event_time, event_type FROM archived_api_events
), numbered AS (
    SELECT event_id, event_time, event_type,
           ROW_NUMBER() OVER (ORDER BY event_time DESC, event_id DESC) AS row_num
    FROM all_events
)
SELECT event_id, event_time, event_type
FROM numbered
WHERE row_num BETWEEN 101 AND 150
ORDER BY row_num;

Keyset pagination may scale better for deep pages because it resumes from the last sort key instead of skipping an ever-growing offset.

NULL and Duplicate Behavior

Set operators compare complete rows according to database equality and set semantics. Distinct set operators generally collapse duplicate rows, including rows with nulls in corresponding positions. UNION ALL preserves every occurrence. Collation, character padding, numeric conversion, and timestamp precision can affect whether values compare as equal.

Normalize only when the business rule requires it. Lowercasing case-sensitive identifiers or trimming meaningful spaces can merge distinct facts.

Current and Archive Tables

A common production pattern places recent data in an active table and older data in an archive. UNION ALL reconstructs history efficiently when ranges do not overlap. Define and test the boundary so the same date or identifier cannot occur in both sources unexpectedly.

If overlap is temporarily possible during archival movement, expose a source column and reconcile it rather than relying on UNION to hide the issue.

Migration and Reconciliation

Migration validation often needs three outputs: rows common to source and target, source-only rows, and target-only rows. Use INTERSECT plus both directions of EXCEPT, or equivalent joins and existence predicates. Compare canonical business columns rather than volatile audit timestamps when those values are expected to change during migration.

Row-count equality is not enough. Reconcile distinct keys, financial totals, null counts, status distributions, checksums where appropriate, and sampled record details.

API and Software Testing Scenarios

Test teams may append API execution results from environments, compare expected and actual datasets, or identify failed tests not present in a rerun. Preserve execution IDs and source labels because two identical statuses can represent separate runs. A distinct operation can erase evidence of repeated requests or retries.

For expected-versus-actual validation, run differences in both directions and compare relevant fields. A one-way difference detects missing expected rows but not unexpected extras.

Financial and Banking Scenarios

Financial reports may combine credits and debits by converting them to a common signed amount, then aggregate the unioned rows. Never use distinct elimination on transactions merely because amounts and dates match; separate transactions can legitimately have equal values. Include a transaction identity and source ledger.

WITH ledger AS (
    SELECT account_id, credit_date AS entry_date, amount
    FROM credits
    UNION ALL
    SELECT account_id, debit_date, -amount
    FROM debits
)
SELECT account_id, SUM(amount) AS net_amount
FROM ledger
GROUP BY account_id;

Inventory, Education, and Log Scenarios

Inventory systems append warehouse balances and then group by product. Education systems combine enrollments across terms while preserving term and program. Log systems append active and archived events, preserve event IDs, and may aggregate by severity or service. In each case, a source or period column protects meaning after combination.

Persisting Combined Results

A combined query can feed a view, temporary table, or INSERT INTO ... SELECT. A view keeps logic reusable but computes according to product behavior when queried. A temporary table can stabilize an intermediate result, support indexes, and prevent repeated computation within a session. Inserting into a permanent table requires idempotency, constraints, lineage, and a strategy for reruns.

Do not assume a CTE is cached. If an expensive combined result is referenced repeatedly and the optimizer does not reuse it effectively, a staged table may be more predictable.

Cross-Schema and Cross-Database Results

Schema-qualified objects can be combined when the user has access. Cross-database access depends on the product and may use database qualifiers, database links, linked servers, or foreign data wrappers. Network latency, remote filtering, transaction consistency, collation, and type mapping become part of query design.

For large remote datasets, move only required rows and columns. Sometimes staging each source locally is safer than repeatedly combining data across live systems.

Designing a Shared Data Contract

Reliable combination begins with a shared output contract. For every position, document the business meaning, output name, data type, precision, nullability, unit, time zone, code system, and row grain. A column named amount is not truly compatible if one branch returns dollars and another returns cents. A timestamp is not aligned if one source stores UTC and another stores local wall time without an offset.

Standardize each branch before the set operation. Convert currency using an approved rate and effective date, normalize timestamps to one zone, map source statuses to a governed set of values, and choose a precision that does not silently truncate data. Keep raw source values when audit or troubleshooting requires them.

WITH standardized_online AS (
    SELECT order_id,
           CAST(total_cents / 100.0 AS DECIMAL(14, 2)) AS amount_usd,
           created_at_utc AS event_time_utc,
           'ONLINE' AS source_name
    FROM online_orders
), standardized_store AS (
    SELECT order_id,
           CAST(total_amount AS DECIMAL(14, 2)),
           store_time_utc,
           'STORE'
    FROM store_orders
)
SELECT * FROM standardized_online
UNION ALL
SELECT * FROM standardized_store;

Explicit column lists are safer than SELECT * in production because schema changes can break positional alignment or, worse, keep the query valid while changing meaning. The compact example above is readable only because both CTE contracts are shown together; long-lived code should list final columns explicitly as well.

Preventing Mixed-Grain Results

Two branches can have compatible types while representing different facts. A monthly customer summary and a row-level transaction table might both return customer ID, date, and amount, yet one row represents a month while the other represents an event. Appending them creates totals that double count and filters that compare unlike records.

State the grain of each branch in a comment or design document. If grains differ, transform them to a common level first. Aggregate transactions to customer-month before combining with monthly summaries, or expand the summary only if a defensible allocation rule exists. Add a record_grain or source column when one result intentionally contains several record types, and ensure downstream consumers understand that contract.

Counting Rows and Unique Entities

Counts answer different questions depending on where they are calculated. Counting each branch measures source volume. Counting after UNION ALL measures total occurrences. Counting after UNION measures distinct complete tuples. COUNT(DISTINCT customer_id) measures unique non-null customer identifiers and ignores differences in other selected attributes.

WITH all_customers AS (
    SELECT customer_id, 'ONLINE' AS source_name FROM online_customers
    UNION ALL
    SELECT customer_id, 'STORE' FROM store_customers
)
SELECT COUNT(*) AS source_occurrences,
       COUNT(DISTINCT customer_id) AS unique_customers
FROM all_customers;

Report both values when overlap is meaningful. Their difference reveals repeated customer presence across sources, although it does not by itself prove bad duplicate data. For composite identity, count a supported composite expression or group by all key columns rather than concatenating values without collision-safe delimiters.

Validation Before Production Release

Validate each branch independently before validating the combined statement. Record branch row counts, key uniqueness, null rates, minimum and maximum dates, and control totals. Then verify the combined count against the expected relationship: for UNION ALL, the output count should equal the sum of branch counts; for distinct set operators, the reduction should be explainable by measured overlap.

Review representative rows at source boundaries, especially archive cutoff dates, regional partitions, and migration batches. Confirm that aliases, types, units, and source labels remain correct after deployment. Add automated tests for a new source, an empty source, duplicated keys, null values, and unexpected status codes so later schema changes fail visibly rather than corrupting a consolidated report.

Operational monitoring should track branch counts, final counts, execution time, spill or remote-transfer indicators, and rejected conversions. A sudden change in one branch can signal a missing feed or altered predicate even when the combined query still returns rows.

Performance Considerations

Cost depends on scans, branch filters, joins, type conversions, duplicate elimination, sorting, aggregation, row width, memory, and remote movement. UNION ALL can stream or concatenate branches, while distinct set operators usually need additional comparison work. Large sorts or hash sets may spill to temporary storage.

Filter early, project only necessary columns, avoid unnecessary conversions, keep predicates searchable, and ensure join or filter keys have suitable indexes. An index on each source helps that branch; there is no single index on an unmaterialized union result.

Reading Execution Plans

Use the product's EXPLAIN tools to inspect scans, seeks, concatenation, append operations, sorts, hash aggregates, joins, memory grants, spills, remote transfers, and estimated versus actual rows. Compare UNION with UNION ALL only when both are semantically valid.

Poor cardinality estimates can lead to inadequate memory or inefficient join choices. Refresh statistics and test realistic data distribution, including highly uneven branches.

Common Mistakes

  • Returning different column counts or incompatible data types.
  • Matching columns by assumed names instead of position.
  • Using UNION when repeated events must be preserved.
  • Using UNION ALL when a distinct population is required.
  • Assuming UNION compares primary keys only.
  • Reversing EXCEPT branches and answering the wrong question.
  • Mixing operators without making precedence explicit.
  • Assuming output order without final ORDER BY.
  • Combining incompatible row grains because the types happen to match.
  • Using distinct elimination to hide overlap, join errors, or bad data.
  • Paginating before the complete result is assembled.
  • Ignoring null, collation, precision, and cross-product portability.

Testing Combined Queries

Test empty branches, overlap, no overlap, exact duplicates, near duplicates, nulls, type boundaries, large and uneven inputs, tied sort values, and records at archive or date boundaries. Verify row grain, column meaning, source lineage, duplicate policy, and deterministic ordering.

For reconciliation, test both difference directions. For financial output, compare independent totals. For pagination, fetch adjacent pages repeatedly while data is stable and confirm that no row is skipped or repeated.

Best Practices

  1. Write down the intended row grain and business question.
  2. Choose vertical set operations or horizontal joins deliberately.
  3. Align columns by semantic meaning, position, and compatible type.
  4. Prefer UNION ALL when duplicates are valid or sources are disjoint.
  5. Use source labels when lineage matters.
  6. Filter branches early and select only required columns.
  7. Use CTEs to make multi-stage logic readable and testable.
  8. Parenthesize or separate mixed set operations.
  9. Apply one deterministic final ordering before pagination.
  10. Review plans and validate with production-like volume.
  11. Document product-specific syntax and null behavior.
  12. Reconcile important results with independent controls.

Interview Questions and Answers

What does combining query results mean?

It means integrating rows produced by multiple queries through set operators, joins, subqueries, CTEs, or related techniques.

What is the difference between UNION and JOIN?

UNION appends compatible rows vertically. A join matches related rows and adds columns horizontally.

What is the difference between UNION and UNION ALL?

UNION removes complete duplicate output rows. UNION ALL preserves all occurrences and normally requires less processing.

Can more than two queries be combined?

Yes. Chain compatible set operations or organize branches with CTEs.

Can WHERE and GROUP BY be used with UNION?

Yes. Each branch can filter and aggregate independently, and an outer query can filter or aggregate the combined result.

Where does ORDER BY belong?

A final ORDER BY normally follows the entire set expression and orders the complete output.

Does UNION remove duplicates by primary key?

No. It compares all projected columns in the combined result.

How are common or missing records found?

Use INTERSECT or EXISTS for common records, and EXCEPT, MINUS, or NOT EXISTS for missing records.

Technique Selection Guide

RequirementPreferred starting point
Append every row from compatible sourcesUNION ALL
Create one distinct combined populationUNION
Find complete rows common to two resultsINTERSECT
Find first-result rows absent from the secondEXCEPT or MINUS
Enrich a row with related columnsJOIN
Test relationship presence without multiplicationEXISTS
Organize a multi-stage combined queryCTE
Reuse a costly intermediate result repeatedlyConsider a temporary table

Quick Reference

-- Append and preserve every occurrence
SELECT key_col, value_col FROM source_a
UNION ALL
SELECT key_col, value_col FROM source_b;

-- Append and return distinct complete rows
SELECT key_col, value_col FROM source_a
UNION
SELECT key_col, value_col FROM source_b;

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

-- Rows in A but not B
SELECT key_col FROM source_a
EXCEPT
SELECT key_col FROM source_b;

Key Takeaway

Combining query results is not a single SQL feature. Set operators append or compare compatible row sets, joins enrich related rows, and subqueries or CTEs organize results into larger operations. The right choice follows from the business question and required row grain.

Define column meaning, duplicate policy, source lineage, null behavior, and ordering before writing the final query. Then filter early, preserve legitimate events, make set direction and precedence explicit, review execution plans, and test edge cases. These practices produce combined results that are understandable, portable, performant, and trustworthy.