Set Operation

UNION

To use union, the two queries must have:

1. Same number and order of columns
2. Same data type in both columns

UNION Operator will remove all duplicate rows from the combined dataset

UNION ALL to retain the duplicate row

postgres@localhost:dvdrental> select * from top_rated_films;
+--------------------------+--------------+
| title                    | release_year |
|--------------------------+--------------|
| The Shawshank Redemption | 1994         |
| The Godfather            | 1972         |
| The Dark Knight          | 2008         |
| 12 Angry Men             | 1957         |
+--------------------------+--------------+
SELECT 4
Time: 0.006s
postgres@localhost:dvdrental> select * from most_popular_films;
+--------------------+--------------+
| title              | release_year |
|--------------------+--------------|
| An American Pickle | 2020         |
| The Godfather      | 1972         |
| The Dark Knight    | 2008         |
| Greyhound          | 2020         |
+--------------------+--------------+
SELECT 4
Time: 0.006s
postgres@localhost:dvdrental> select * from top_rated_films union select * from most_popular_films;
+--------------------------+--------------+
| title                    | release_year |
|--------------------------+--------------|
| An American Pickle       | 2020         |
| The Dark Knight          | 2008         |
| Greyhound                | 2020         |
| The Shawshank Redemption | 1994         |
| The Godfather            | 1972         |
| 12 Angry Men             | 1957         |
+--------------------------+--------------+
SELECT 6
Time: 0.004s

This one it already filter out the duplicated. If using union all, it will be

postgres@localhost:dvdrental> select * from top_rated_films union all select * from most_popular_films;
+--------------------------+--------------+
| title                    | release_year |
|--------------------------+--------------|
| The Shawshank Redemption | 1994         |
| The Godfather            | 1972         |
| The Dark Knight          | 2008         |
| 12 Angry Men             | 1957         |
| An American Pickle       | 2020         |
| The Godfather            | 1972         |
| The Dark Knight          | 2008         |
| Greyhound                | 2020         |
+--------------------------+--------------+
SELECT 8
Time: 0.008s

Intersect

SELECT select_list FROM A INTERSECT SELECT select_list FROM B;

postgres@localhost:dvdrental> select * from most_popular_films;
+--------------------+--------------+
| title              | release_year |
|--------------------+--------------|
| An American Pickle | 2020         |
| The Godfather      | 1972         |
| The Dark Knight    | 2008         |
| Greyhound          | 2020         |
+--------------------+--------------+
SELECT 4
Time: 0.005s
postgres@localhost:dvdrental> select * from top_rated_films;
+--------------------------+--------------+
| title                    | release_year |
|--------------------------+--------------|
| The Shawshank Redemption | 1994         |
| The Godfather            | 1972         |
| The Dark Knight          | 2008         |
| 12 Angry Men             | 1957         |
+--------------------------+--------------+
SELECT 4
Time: 0.005s

postgres@localhost:dvdrental> select * from most_popular_films intersect select * from top_rated_films;
+-----------------+--------------+
| title           | release_year |
|-----------------+--------------|
| The Godfather   | 1972         |
| The Dark Knight | 2008         |
+-----------------+--------------+
SELECT 2
Time: 0.006s

Except

SELECT select_list FROM A EXCEPT SELECT select_list FROM B ORDER BY sort_expression;

postgres@localhost:dvdrental> select * from top_rated_films;
+--------------------------+--------------+
| title                    | release_year |
|--------------------------+--------------|
| The Shawshank Redemption | 1994         |
| The Godfather            | 1972         |
| The Dark Knight          | 2008         |
| 12 Angry Men             | 1957         |
+--------------------------+--------------+
SELECT 4
Time: 0.006s
postgres@localhost:dvdrental> select * from most_popular_films;
+--------------------+--------------+
| title              | release_year |
|--------------------+--------------|
| An American Pickle | 2020         |
| The Godfather      | 1972         |
| The Dark Knight    | 2008         |
| Greyhound          | 2020         |
+--------------------+--------------+
SELECT 4
Time: 0.007s

postgres@localhost:dvdrental> select * from top_rated_films except select * from most_popular_films;
+--------------------------+--------------+
| title                    | release_year |
|--------------------------+--------------|
| The Shawshank Redemption | 1994         |
| 12 Angry Men             | 1957         |
+--------------------------+--------------+
SELECT 2