Combines result sets from two or more SELECT statements into a single result set.
The order and number of the columns in the select list of all queries must be the same
-- load sample data
DROP TABLE IF EXISTS top_rated_films;
CREATE TABLE top_rated_films(
title VARCHAR NOT NULL,
release_year SMALLINT
);
DROP TABLE IF EXISTS most_popular_films;
CREATE TABLE most_popular_films(
title VARCHAR NOT NULL,
release_year SMALLINT
);
INSERT INTO
top_rated_films(title,release_year)
VALUES
('The Shawshank Redemption',1994),
('The Godfather',1972),
('12 Angry Men',1957);
INSERT INTO
most_popular_films(title,release_year)
VALUES
('An American Pickle',2020),
('The Godfather',1972),
('Greyhound',2020);
Union Query
SELECT * FROM top_rated_films
UNION
SELECT * FROM most_popular_films;
title | release_year
--------------------------+--------------
An American Pickle | 2020
Greyhound | 2020
The Shawshank Redemption | 1994
The Godfather | 1972
12 Angry Men | 1957
Union All Query
This will not avoid duplicate values
SELECT * FROM top_rated_films
UNION ALL
SELECT * FROM most_popular_films;
title | release_year
--------------------------+--------------
The Shawshank Redemption | 1994
-- The Godfather | 1972
12 Angry Men | 1957
An American Pickle | 2020
-- The Godfather | 1972
Greyhound | 2020
UNION with ORDER BY
SELECT * FROM top_rated_films
UNION ALL
SELECT * FROM most_popular_films
ORDER BY title;
title | release_year
--------------------------+--------------
12 Angry Men | 1957
An American Pickle | 2020
Greyhound | 2020
The Godfather | 1972
The Godfather | 1972
The Shawshank Redemption | 1994
Intersect
The INTERSECT operator returns any rows that are available in both result sets.
SELECT *
FROM most_popular_films
INTERSECT
SELECT *
FROM top_rated_films;
title | release_year
---------------+--------------
The Godfather | 1972
Except
The EXCEPT operator returns distinct rows from the first (left) query that are not in the output of the second (right) query.\
SELECT * FROM top_rated_films
EXCEPT
SELECT * FROM most_popular_films;
title | release_year
--------------------------+--------------
The Shawshank Redemption | 1994
12 Angry Men | 1957
