SQL Set Operations Cheat Sheet in Common Concepts

 The SQL Set operation is used to combine the two or more SQL queries.

Let us understand common set operators by performing operations on two sets

• cast in "Sherlock Holmes" movie
• cast in "Avengers Endgame" movie

Common Set Operators

INTERSECT

Actors who acted in both Sherlock Holmes and Avengers Endgame

Result: Robert D Jr.

MINUS

Actors who acted in Sherlock Holmes and not in Avengers Endgame

Result: Jude Law, Mark Strong

UNION

Unique actors who acted in Sherlock Holmes or in Avengers Endgame

Result: Jude Law, Mark Strong, Robert D Jr, Chris Evans, Mark Ruffalo

UNION ALL

Doesn't eliminate duplicate results

Result: Jude Law, Mark Strong, Robert D Jr, Robert D Jr, Chris Evans, Mark Ruffalo

Let's learn more about CASE clause using the given database

Database The IMDb dataset which consists of movies, actors and cast. You can refer to the database in the code playground for a better understanding.

Applying Set Operations

We can apply these set operations on the one or more sql queries to combine their results

Syntax

1

2

3

4

5

6

7

8

9

SELECT

c1, c2

FROM

table_name

SET_OPERATOR

SELECT

c1, c2

FROM

table_name;

 

SQL

Basic rules when combining two sql queries using set operations

• Each SELECT statement must have the same number of columns
• The columns must have similar data types
• The columns in each SELECT statement must be in the same order

Examples

• Get ids of actors who acted in both Sherlock Holmes(id=6) and Avengers Endgame(id=15)?

1

2

3

4

5

6

7

SELECT actor_id

FROM cast

WHERE movie_id=6

INTERSECT

SELECT actor_id

FROM cast

WHERE movie_id=15;

 

SQL

Output

actor_id
6

• Get ids of actors who acted in Sherlock Holmes(id=6) and not in Avengers Endgame(id=15)?

1

2

3

4

5

6

7

SELECT actor_id

FROM cast

WHERE movie_id=6

EXCEPT

SELECT actor_id

FROM cast

WHERE movie_id=15;

 

SQL

Output

actor_id
16
21

• Get distinct ids of actors who acted in Sherlock Holmes(id=6) or Avengers Endgame(id=15).

1

2

3

4

5

6

7

SELECT actor_id

FROM cast

WHERE movie_id=6

UNION

SELECT actor_id

FROM cast

WHERE movie_id=15;

 

SQL

Output

actor_id
6
8
16
21
22

• Get ids of actors who acted in Sherlock Holmes(id=6) or Avengers Endgame(id=15).

1

2

3

4

5

6

7

SELECT actor_id

FROM cast

WHERE movie_id=6

UNION ALL

SELECT actor_id

FROM cast

WHERE movie_id=15;

 

SQL

Output

actor_id
6
16
21
6
8
22

Try it Yourself!

1. Get all the movie ids in which actors Robert Downey Jr. (id=6) & Chris Evans(id=22) have been casted
2. Get all the movie ids in which actor Robert Downey Jr. (id=6) is casted and not Chris Evans(id=22)
3. Get all the unique movie ids in which either actor Robert Downey Jr. (id=6) or Chris Evans(id=22) is casted

ORDER BY Clause in Set Operations

ORDER BY clause can appear only once at the end of the query containing multiple SELECT statements.

While using Set Operators, individual SELECT statements cannot have ORDER BY clause. Additionally, sorting can be done based on the columns that appear in the first SELECT query. For this reason, it is recommended to sort this kind of queries using column positions.

Example

Get distinct ids of actors who acted in Sherlock Holmes (id=6) or Avengers Endgame(id=15). Sort ids in the descending order.

1

2

3

4

5

6

7

8

SELECT actor_id

FROM cast

WHERE movie_id=6

UNION

SELECT actor_id

FROM cast

WHERE movie_id=15

ORDER BY 1 DESC;

 

SQL

Try it Yourself!

1. Get all the movie ids in which actor Robert Downey Jr. (id=6) is casted & not Chris Evans(id=22). Sort the ids in the descending order.

Pagination in Set Operations

Similar to ORDER BY clause, LIMIT and OFFSET clauses are used at the end of the list of queries.

Example

Get the first 5 ids of actors who acted in Sherlock Holmes (id=6) or Avengers Endgame(id=15). Sort ids in the descending order.

1

2

3

4

5

6

7

8

9

SELECT actor_id

FROM cast

WHERE movie_id=6

UNION

SELECT actor_id

FROM cast

WHERE movie_id=15

ORDER BY 1 DESC

LIMIT 5;

SQL

Try it Yourself!

1. Get the first 5 unique movie ids in which either actor Robert Downey Jr. (id=6) or Ryan Reynolds(id=7) is casted. Sort ids in the descending order.