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…