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?
| Tables | You want | Use |
|---|---|---|
| Orders and Customers | Order rows with customer names | JOIN |
| Orders 2025 and Orders 2026 | One list of all orders | UNION ALL |
| Two mailing lists | One list without repeats | UNION |
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
| Operator | Rows returned | Notes |
|---|---|---|
| UNION ALL | 6 | Keeps every row, faster |
| UNION | 5 | Removes 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
| order_id | customer_id | amount | source |
|---|---|---|---|
| O-9001 | C-101 | 120 | 2026 |
| O-9002 | C-103 | 75 | 2026 |
| O-9003 | C-101 | 80 | 2026 |
| O-9004 | C-104 | 60 | 2026 |
| O-8001 | C-102 | 90 | 2025 |
| O-9001 | C-101 | 120 | 2025 |
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 | 90Both 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;| source | rows | total |
|---|---|---|
| 2025 | 2 | 210 |
| 2026 | 4 | 335 |
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 | Fix |
|---|---|---|
| “each UNION query must have the same number of columns” | One SELECT lists an extra column | Add NULL AS col to the shorter SELECT |
| Values land in the wrong column | Columns listed in a different order | List columns explicitly in the same order |
| Fewer rows than expected | UNION removed duplicates | Use UNION ALL |
| Row count multiplies | A JOIN on a key that repeats in both tables | Check 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.