1. Queries with UNION and INTERSECT
In this lesson we will look at the functions UNION and INTERSECT.
2. UNION
Let's first review how the set operator UNION works. We have two tables, table 1 has the elements A, B, C, and D and table 2 the elements D, E, and F. The union of those tables are the elements A, B, C, D, E, and F. So, the union is the set of all values that are either in table 1 or in table 2 or in both. We don't have any duplicates in the UNION of two sets or tables.
3. Example - UNION
Here we have an example of the UNION of two tables. The first table consists of title, genre and renting price of all movies with a renting price higher than 2.8. The second table consists of the title, genre, and renting price of all movies with genre Actions and Adventure. Note that it is crucial for the UNION operator that the same columns are selected in both tables.
4. Example - UNION
The result of this UNION are all movies which have either a renting price higher than 2.8 or are Action and Adventure movies as we can see from the first three rows.
5. INTERSECT
To review how INTERSECT works, let's have a look again at the two tables, table 1 with the elements A, B, C, and D and table 2 with D, E, and F. The intersect of those tables consists only of D. The intersect is the set of all values that appear in both tables.
6. Example - INTERSECT
This is an example of the INTERSECT of two tables. We take the same two tables as in the previous example but instead of the UNION we create the INTERSECT of those tables.
The result of this INTERSECT is only the movie Astro Boy which has a renting price higher than 2.8 and is an Action and Adventure movie.
7. Let's practice!
Now, let's work with the set operators UNION and INTERSECT.