Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Another SQL set operation, UNION ALL, is almost the same as UNION, but will not eliminate duplicates.
As with the other topics we’ve covered in this series, there is a wealth of information available on the topic of sets, databases, and SQL.
Check out this great read on Set Theory and SQL: “SQL-99 Complete, Really”, by Peter Guzman & Trudy Pelzer hosted on MariaDB
The link above contains more than just UNION, INTERSECT and EXCEPT, but there is a section towards the end that applies those operations to the larger discussion. This is a wonderful article!
Other good reads about Set Operations include:
Union and Union All:
See all of the SQL used in Querying Relational Databases in the Querying Relational Databases Cheat Sheet.
In our last video, we learned about unions.
In this video we're gonna take a look at union all.
This is very similar to union but there is one key difference.
The union all will return all of us from each select, even if there are duplicates.
As we saw previously, the union operates a will return a distinct list of results.
In other words, if the row exists in both subsets of the union,
the result will only contain one row.
Here we see two lists of colors union together.
The colors blue and purple exist in both lists.
But what we perform a union, the result set contains only one instance of each.
With the union all operator,
the result set will display the duplicates instead of eliminating them.
Let's take a look at union all in the database.
First let's remind ourselves of what the union of make and foreign make look like.
Now let's run that as a union all.
It's kind of hard to tell how many duplicates there are and how many aren't.
Let's order the results and it should be a little more clear.
Now we can see pretty clearly BMW Honda,
Kia and Toyota are duplicated to
recap You write a union all statement
first by writing a sequel query.
Then the keywords union all and then your second query.
Unions require the number of columns in your data set to be the same for
your first and second queries.
You need to sign up for Treehouse in order to download course files.Sign up