1. Set operators
Now that you've got the basics of joins down, let's move on to set operators.
2. What are set operators?
Set operators take the output of two or more SELECT queries and combine them into one result.
We spent the last few videos discussing JOINS. Both Joins and set operators combine data so it's important to understand their differences. Joins combine tables, while set operators combine queries. This makes joins column-oriented and set operators row-oriented.
3. Types of set operators
There are four types of set operators. The first is union and it returns all rows with no duplicates. Second is UNION ALL which returns all rows including duplicated. Thirdly, intersect returns rows outputted by both queries. And finally, minus returns distinct rows in the first query that are not in the second query.
Let's look at some real-life examples to help break this down.
4. Union
Customers have two types of addresses on record: their mailing address and their billing address. If we wanted to get all the cities associated with clients, we can use a union to combine rows from the city columns in the customer and invoice table.
The syntax of a set operator clause is simple. The two queries to be combined are separated by the set operator clause, in our case a UNION.
Because we are using UNION, the output won't contain duplicates.
5. Union all
On the other hand we could use union all to better analyze which cities occur the most within the client base.
Here we see that there are indeed duplicates included.
6. Intersect
Some tracks available for sale are also in a playlist. These tracks are in the PlaylistTrack table. We can use the intersect operator to see which songs for a specific composer are in a playlist.
In our example, let's say we are specifically looking at the composer, Miles Davis. We need to add a conditional clause for this. Our query would look like this. As queries to be joined get longer, it's useful to use parenthesis to indicate the start and end of a query.
7. Minus
You may have noticed in the dataset there are composers and artists. Composers write the songs and artists perform them. A lot of times the artists are also composers. To get a list of artists who don't compose music, we can use the minus operator.
We get a list of all the composers in our library and minus it from a list of all the artists in our library. If we want to order our output in reverse alphabetical order, we can use the ORDER BY clause at the end and use 1 to denote the first and only column.
8. Let's practice!
Ok, now it's your turn to try out set operators!