Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
EXCEPT uses the same format as INTERCEPT, but outputs only the records that are not in the latter table.
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.
Hello again, so fine this day,
we've learned about set operations: Union, Union All and Intersect.
In this video we'll introduce our last set operation, Except,
the Except operation is a handy SQL tool to remember, it will give you all records from
a query except the rows that are a match in the second query.
Let's look at our sample data again for a minute.
If we look at our electronics and
furniture data, notice that both tables have TV stand.
If we were to write a query for
just electronic products that are not also furniture, we could use except,
because except gives you only the records that aren't in both tables.
TV stand is removed from the result set.
If you picture except in a Venn Diagram form it would look like this.
Now what see the SQL in action.
In the SQL playground with this video we still have our car database.
Let's see if we can find all car makes that are not in the foreign make table.
We can use the except operations to do this.
Or we could run a query that will show us the cars in the ForeignMake
table that we don't have yet to sell by reversing the order in which
we supply the queries on either side of the accept key word.
Just like the Union and Intersect operations
except requires both queries to have the same number of columns defined.
If we add the ID column to the first part of the query and
not the second, We get an error.
We can fix that by making the columns match up properly like so.
Take a look at the results though.
Is that right?
Why do we get all of the rows from the ForeignMake even though
we had the EXCEPT operation included?
It's because even though the MakeNames match from one table to another,
the IDs is different remember that sets operations in SQL
take into account the values from all columns provided in your query.
So the database treats those rows as different and
thus does not remove them when applying the except clause.
Like with the operations the except keyword
is used between two queries that you'd like to perform the operation on.
You need to sign up for Treehouse in order to download course files.Sign up