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. 0:01 In this video we're gonna take a look at union all. 0:03 This is very similar to union but there is one key difference. 0:07 The union all will return all of us from each select, even if there are duplicates. 0:11 As we saw previously, the union operates a will return a distinct list of results. 0:17 In other words, if the row exists in both subsets of the union, 0:23 the result will only contain one row. 0:27 Here we see two lists of colors union together. 0:30 The colors blue and purple exist in both lists. 0:33 But what we perform a union, the result set contains only one instance of each. 0:37 With the union all operator, 0:43 the result set will display the duplicates instead of eliminating them. 0:45 Let's take a look at union all in the database. 0:50 First let's remind ourselves of what the union of make and foreign make look like. 0:54 Now let's run that as a union all. 0:59 It's kind of hard to tell how many duplicates there are and how many aren't. 1:06 Let's order the results and it should be a little more clear. 1:10 Now we can see pretty clearly BMW Honda, 1:19 Kia and Toyota are duplicated to 1:25 recap You write a union all statement 1:30 first by writing a sequel query. 1:35 Then the keywords union all and then your second query. 1:40 Unions require the number of columns in your data set to be the same for 1:44 your first and second queries. 1:49
You need to sign up for Treehouse in order to download course files.Sign up