One of the more common set operations in SQL is the UNION statement. Use to combine two data sets into one, stacked one on top of the other, unlike an inner join which puts data together side by side.
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.
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.
We're about to dive into our set operation, the union operator. 0:00 Unions combine data from two tables all sets into one result set. 0:05 This may sound a lot like the inner join that we learned about recently. 0:10 But there's a key difference to point out, 0:13 if you recall an inner join uses the foreign key relationship to merge data. 0:16 Into rows side by side where they match on common key values. 0:21 In this case the coming key values are the ID's, 0:27 a union also brings two data sets together into one result set. 0:30 But it doesn't use a foreign key relationship at all and 0:35 it doesn't place data side by side. 0:38 The union operations stacks data vertically. 0:41 Because the data is being stacked in this manner, 0:44 the column definitions of both queries have to match. 0:47 If they don't have the same number of columns, you'll get an error 0:51 If we picture the union with a Venn diagram, It looks like this. 0:54 Now, let's see the union in action, shall we? 0:59 In our car sales database we have a make table and 1:02 a table containing only foreign makes. 1:06 Here they are in two result sets. 1:09 There are some makes in the main make table. 1:17 That's ans in the foreign make table and the rest sum in the foreign make table. 1:21 That's ans the main make table. 1:25 Lets say we want to see all makes in our database together in one list. 1:28 We can do that by adding the union keyword between two statements. 1:33 Let's also select the make name. 1:42 When we hit run we get a result set with one column 1:54 containing a distinct list of all car makes, perfect. 1:58 What if we wanted to see the ID columns along with the names? 2:03 Let's add make ID and hit run. 2:11 Notice we got an error, 2:16 that's because we specified to two columns in our first select statement. 2:19 And only one in our second. 2:24 Let's fix that, let's add foreign make ID, 2:28 That's better, notice now that we include the ID fields. 2:41 We see a few models more than once. 2:45 Why is that? 2:48 That's because the union statement returns a distinct set based on the column 2:53 specified. 2:58 In the case for the ID's for BMW and Honda 2:59 the IDs are different in the make and form make table. 3:06 So, it displays both instances when we exclude the ID 3:11 field the database only returns the one row for each make. 3:15 Might say we only care about car manufacturers 3:29 that start with what is a B or C. 3:32 We can filter down each portion of the union statement with the workloads. 3:35 In the where section, we can use the less than operator with the text to get 3:43 anything that's alphabetically less than D. 3:47 And finally let's order that alphabetically. 3:58 When ordering a union statement, you only need to supply it once in the last query. 4:08 To recap you write a union statement first by writing a SQL query, 4:24 then the keyword union, and then your second query. 4:29 Unions require the number of columns in your data set to be the same in your first 4:35 and second queries. 4:39
You need to sign up for Treehouse in order to download course files.Sign up