Analistable

How to combine two tables in SQL

By the Analistable team · Updated · 5 min read

Use a JOIN to put columns from two tables side by side, matched on a key: SELECT … FROM orders o JOIN customers c ON c.customer_id = o.customer_id. Use UNION ALL to stack rows from two tables with the same columns: SELECT … FROM orders_2025 UNION ALL SELECT … FROM orders_2026. UNION (without ALL) also removes duplicate rows.

Part of our guide: How to merge data in pandas, R and SQL

JOIN or UNION?

Choosing how to combine
TablesYou wantUse
Orders and CustomersOrder rows with customer namesJOIN
Orders 2025 and Orders 2026One list of all ordersUNION ALL
Two mailing listsOne list without repeatsUNION

JOIN: side by side

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

LEFT JOIN keeps every order; INNER JOIN keeps only orders with a known customer. More tables and pitfalls: joining multiple tables.

UNION ALL: stacked

SELECT order_id, customer_id, amount, '2026' AS source FROM orders
UNION ALL
SELECT order_id, customer_id, amount, '2025' AS source FROM orders_2025;
  • Both SELECTs need the same number of columns, in the same order, with compatible types. Columns are matched by position, not name.
  • Column names come from the first SELECT.
  • A literal column ('2025' AS source) records where each row came from.

UNION vs UNION ALL

orders (4 rows) combined with orders_2025 (2 rows, one identical to O-9001)
OperatorRows returnedNotes
UNION ALL6Keeps every row, faster
UNION5Removes the duplicate O-9001 row; sorts or hashes to do it

Use UNION ALL unless you specifically need duplicates removed — UNION does extra work and can hide data problems.

pandas equivalents: merge for JOIN, concat for UNION ALL — see merging DataFrames in pandas.

Worked example

orders UNION ALL orders_2025 (with a source column)
order_idcustomer_idamountsource
O-9001C-1011202026
O-9002C-103752026
O-9003C-101802026
O-9004C-104602026
O-8001C-102902025
O-9001C-1011202025

With the source column, the two O-9001 rows are no longer identical, so even UNION would keep both. Add the source column only after deciding how duplicates should be treated.

Combine and then join

A common pattern is to stack first and join second: UNION ALL the yearly order tables into one, then LEFT JOIN customers once. Write it with a common table expression:

WITH all_orders AS (
  SELECT order_id, customer_id, amount FROM orders
  UNION ALL
  SELECT order_id, customer_id, amount FROM orders_2025
)
SELECT c.name, SUM(a.amount) AS total
FROM all_orders a
LEFT JOIN customers c ON c.customer_id = a.customer_id
GROUP BY c.name;

Other ways to combine: INTERSECT and EXCEPT

When both tables have the same columns, two more set operators answer reconciliation questions directly:

-- Rows that appear in both tables
SELECT order_id, customer_id, amount FROM orders_2025
INTERSECT
SELECT order_id, customer_id, amount FROM orders;
-- O-9001 | C-101 | 120

-- Rows in orders_2025 that are not in orders
SELECT order_id, customer_id, amount FROM orders_2025
EXCEPT
SELECT order_id, customer_id, amount FROM orders;
-- O-8001 | C-102 | 90

Both compare whole rows, so a single changed amount makes a row count as different. Oracle has traditionally used MINUS for EXCEPT; DuckDB, PostgreSQL, SQLite and SQL Server use EXCEPT.

When the columns don't line up

UNION ALL matches columns by position, so list them explicitly instead of using SELECT *, and fill gaps with typed NULLs or constants:

SELECT order_id, customer_id, amount, NULL AS discount FROM orders_2025
UNION ALL
SELECT order_id, customer_id, amount, discount FROM orders;
  • If one table calls the column cust_id, alias it: SELECT cust_id AS customer_id.
  • If types differ (amount stored as text in an import), CAST it so both sides agree: CAST(amount AS DECIMAL(10,2)).
  • DuckDB also offers UNION ALL BY NAME, which matches columns by name and fills missing ones with NULL.

Checking the combined result

Run quick counts before using a combined table:

-- Rows per source should match each table's own count
SELECT source, COUNT(*) AS rows, SUM(amount) AS total
FROM (SELECT amount, '2026' AS source FROM orders
      UNION ALL
      SELECT amount, '2025' AS source FROM orders_2025) x
GROUP BY source;
Result
sourcerowstotal
20252210
20264335

Then look for keys that now appear twice: SELECT order_id, COUNT(*) FROM all_orders GROUP BY order_id HAVING COUNT(*) > 1 returns O-9001, the order present in both tables. Decide whether it's a genuine duplicate before summing, because the stacked-then-joined query above counts its 120 twice: Bakery Lune totals 320 (120 + 80 + 120), Hart & Co 90, Nordic Supply 75, and 60 sits against a NULL name for unknown customer C-104.

Common mistakes

Symptom, cause and fix
SymptomCauseFix
“each UNION query must have the same number of columns”One SELECT lists an extra columnAdd NULL AS col to the shorter SELECT
Values land in the wrong columnColumns listed in a different orderList columns explicitly in the same order
Fewer rows than expectedUNION removed duplicatesUse UNION ALL
Row count multipliesA JOIN on a key that repeats in both tablesCheck uniqueness of the key, aggregate first

The same decision in spreadsheets, stacking versus matching, is covered in joining spreadsheets.

Which method when

Ask what one row of the result should represent. If it is still one order, but with more information about it, you need a JOIN. If it is one order from either table, you need UNION ALL. If it is one key that exists in both or only in one, INTERSECT or EXCEPT (or an anti join with LEFT JOIN … WHERE b.key IS NULL) answers it directly. Monthly exports with identical columns are the classic UNION ALL case; a customer list plus an order list is the classic JOIN.

Frequently asked questions

How do I combine two tables with the same columns in SQL?
Use UNION ALL between two SELECT statements that list the same columns in the same order.
What is the difference between UNION and UNION ALL?
UNION removes duplicate rows from the combined result; UNION ALL keeps them all and is faster.
How do I combine two tables without a common column?
If you want every combination, use CROSS JOIN. If they share columns but not a key, stack them with UNION ALL.
How do I find rows that are in one table but not the other?
Use EXCEPT between two SELECTs with the same columns, or LEFT JOIN on the key and keep rows where the right table's key IS NULL.

Related guides