Learning Postgres: Union, Union all (Part 11)
UNION
and UNION ALL
are operators in SQL that combine the results of two or more queries into one result set. These operators are typically used when we want to merge the results of queries from different tables or conditions into a single resultant table.
Union
UNION
removes duplicate rows from the result set. If you have the same rows in both queries, UNION
will display each row only once.
UNION
requires us to have the same number of columns in all SELECT
statements and for those columns to be of the same type. It is not possible to, for example, perform an AVG
on a column where part of it is an integer and part is a string.
Let's say we have a list of countries and want to find countries with an area greater than 3 million km² and a population greater than 25 million. This can be resolved using WHERE
but also using UNION
.
DROP TABLE IF EXISTS countries;
CREATE TABLE countries (
id bigserial PRIMARY KEY,
"name" TEXT NOT NULL,
continent TEXT NOT NULL,
area INT NOT NULL,
population BIGINT NOT NULL,
gdp BIGINT NOT NULL
);
INSERT INTO countries ("name", continent, area, population, gdp) VALUES
(
'Afghanistan', 'Asia', 652230, 25500100, 20343000000
),
(
'Albania', 'Europe', 28748, 2831741, 12960000000
),
(
'Algeria', 'Africa', 2381741, 37100000…