Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Recap and review the SQL Set Operations we learned.
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 this stage, we've learned about the SQL set operations and their four variants.
[SOUND] Each set operation has the same basic structure.
The first query, then the set operation, then the second query.
You can do anything inside of your queries that you'd normally do.
Including, joining multiple tables together and
filtering using a WHERE clause.
You can select any number of columns, so long as you select the same number of
columns in both your first and second query.
[SOUND] You can also add an ORDER BY clause, but since you're ordering
the final result set you only need to include it once, right at the very end.
Unions combine all records from both query result sets into a distinct list.
If there are any duplicates they are eliminated from the final result set.
Like union, union all combines all records from both query result sets.
However, duplicate rows are shown.
Intersect compares the result sets from two queries and
returns only the records that exist in both.
Except compares the result sets from two queries and returns just
the rows from the first query except for the rows that are also in the second.
Set operations are structured in a very similar way and work by combining
different data sets in various ways, but they perform very different functions.
These SQL set operations are very useful tools to keep handy in your SQL tool belt.
Once again it's time for you to practice.
Open up the SQL Playground with this video and go through the various challenges.
We're in the library database again but
now we have two locations, North and South.
Each location has books and loans associated with it.
For example, books_north has all books in the library's northern location.
The loans_south table contains all loan information for the southern location.
Good luck and I'll see you in the next stage.
You need to sign up for Treehouse in order to download course files.Sign up