Union

Unions are different from joins. Joins are using conditions to combine tables but unions just add two tables on top of the other. To use UNION we will write:

SELECT col1, col2, ... FROM table1
UNION
SELECT col1, col2, ... FROM table2

Both selects must obey the following rules:

• The number of fields should be equal
• Order is important
• The columns in the same place must match the data types

For example, let's assume we have the following tables:

germany_people

england_people

Problem: We want to make one big table of all the names we have.

SELECT name from germany_people
UNION
SELECT name from england_people

Result:

UNION returns only distinct values while UNION ALL return all of the records as-is:

SELECT name from germany_people
UNION ALL
SELECT name from england_people

Result:

You can also combine UNION ALL with aggregate functions, GROUP BY, and ORDER BY to summarize data across multiple tables. The trick is to wrap the UNION ALL inside a subquery, then apply grouping and sorting on top of it.

For example, suppose we want to count how many times each name appears across both tables:

SELECT name, COUNT(*) AS total_count
FROM (
    SELECT name FROM germany_people
    UNION ALL
    SELECT name FROM england_people
) AS combined
GROUP BY name
ORDER BY total_count DESC

Result:

Here, UNION ALL (not UNION) is used so that duplicates like Lena are kept — otherwise they would be removed before counting. The subquery merges all rows, and then GROUP BY + COUNT summarize them. ORDER BY sorts the final result.