Combining Results with UNION

A join puts tables side by side: the columns of one next to the columns of the other. UNION puts the results of two queries one below the other: first the rows of the first query, then the rows of the second one. What's new in the shop since August 2025, new customers and new games in one list:

SELECT joined AS day, 'new customer' AS event, name AS detail
FROM customers
WHERE joined >= '2025-08-01'
UNION ALL
SELECT released, 'new game', title
FROM games
WHERE released >= '2025-08-01'
ORDER BY day;

Both queries must have the same number of columns. The columns are matched by their position, not by their names: the first column of the second query goes under the first column of the first query, and so on. The names of the columns come from the first query, so that's the only one that needs aliases. With a different number of columns, you get an error:

Error on line 1: SELECTs to the left and right of UNION do not have the same number of result columns

Note the columns 'new customer' and 'new game': a text in quotes gives the same value in every row, and here it tells where each row came from.

UNION ALL keeps all rows. UNION without ALL removes duplicate rows from the result, as DISTINCT does. The games that someone has ordered or reviewed, SELECT game_id FROM order_items UNION SELECT game_id FROM reviews, give 17 rows, each game once. With UNION ALL, it's 67 rows, one for every item of every order and one for every review. When you know there can't be any duplicates, or you want to keep them, use UNION ALL: the database doesn't have to look for duplicates, so it's faster.

ORDER BY comes only once, at the very end, and sorts the whole result; the same goes for LIMIT. It uses the column names of the first query, here day. An ORDER BY after the first query ends with ORDER BY clause should come after UNION not before.

Typical mistake: columns in a different order. If the second query had SELECT title, 'new game', released, SQLite wouldn't complain: it would put the titles under the dates without a word. Check that the columns of both queries mean the same thing.