How can I combine results from two SELECT queries with UNION?

Put the two SELECT statements in one compound query and place UNION between them. For this example, assume both tables have columns named item_id and item_name; those are illustrative names, so replace them with your actual table and column names.

SELECT item_id, item_name
FROM current_items
UNION
SELECT item_id, item_name
FROM archived_items;

Each SELECT must return the same number of columns. Here, each returns two: the item ID and the item name. [S1] Selecting corresponding fields in the same order makes the combined output easier to interpret. This query returns rows from both sides while removing duplicate result rows. [S1]

With UNION, duplicate checking applies to the complete result row formed from the selected columns. [S1] So if both queries select item_id and item_name, (102, 'Pen') in each result is a duplicate and only one copy remains. But (102, 'Pen') and (102, 'Blue Pen') are different result rows: the selected names differ, so UNION does not treat those complete rows as duplicates. [S1]

That distinction matters if you mean “one row per item ID.” Selecting the ID and name with UNION does not ensure one row per ID when the names differ. Decide which values should define the rows you want to deduplicate before choosing the selected columns.

Assume, just for illustration, that the current table contains (101, 'Notebook') and (102, 'Pen'), while the archived table contains (102, 'Pen') and (103, 'Folder').

Using the same SELECTs with UNION:

SELECT item_id, item_name FROM current_items
UNION
SELECT item_id, item_name FROM archived_items;

The result contains these three distinct rows: (101, 'Notebook'), (102, 'Pen'), and (103, 'Folder'). The repeated (102, 'Pen') appears once.

Change only the operator to UNION ALL:

SELECT item_id, item_name FROM current_items
UNION ALL
SELECT item_id, item_name FROM archived_items;

That result contains all four input rows, including two copies of (102, 'Pen'). UNION ALL retains duplicates; UNION removes duplicate result rows. [S1]

Choose UNION when repeated complete rows should appear once; choose UNION ALL when every row from each query should be kept. [S1] If you expected a repeated item to disappear, compare every selected value on both sides. A difference in a selected value means the complete result rows are not duplicates. [S1]

Also check that both SELECTs return the same number of columns, as required for a compound SELECT. [S1] Confirm that each query selects the fields you need and that the corresponding fields are in the intended order. The key distinction is that duplicate IDs alone are not enough to make rows duplicates if other selected values differ. Try a small sample whose rows you can inspect.