Preview
Video Player
00:00
00:00
Except Operations
2:37EXCEPT uses the same format as INTERSECT, but outputs only the records that are not in the latter table.
Hello again, so far in this stage
0:00
we've learned about set operations:
Union, Union All and Intersect.
0:02
In this video, we'll introduce
our last set operation, EXCEPT.
0:07
The EXCEPT operation is a handy SQL tool to
remember, it will give you all records from
0:12
a query except the rows that are
a match in the second query.
0:17
Let's look at our sample data again for
a minute.
0:22
If we look at our electronics and
0:24
furniture data,
notice that both tables have TV stand.
0:26
If we were to write a query for
0:30
just electronic products that are not
also furniture, we could use EXCEPT,
0:32
because EXCEPT gives you only
the records that aren't in both tables.
0:37
TV stand is removed from the result set.
0:42
If you picture except in a Venn Diagram
form it would look like this.
0:45
Now what see the SQL in action.
0:50
In the SQL playground with this video
we still have our car database.
0:52
Let's see if we can find all car makes
that are not in the ForeignMake table.
0:57
We can use the EXCEPT
operation to do this.
1:02
Or we could run a query that will
show us the cars in the ForeignMake
1:13
table that we don't have yet
to sell by reversing the order in which
1:18
we supply the queries on either
side of the EXCEPT keyword.
1:23
Just like the Union and
Intersect operations,
1:28
EXCEPT requires both queries to have
the same number of columns defined.
1:31
If we add the ID column to
the first part of the query and
1:36
not the second, we get an error.
1:40
We can fix that by making the columns
match up properly like so.
1:47
That's better.
1:57
Take a look at the results though.
1:58
Is that right?
2:00
Why do we get all of the rows
from the ForeignMake even though
2:02
we had the EXCEPT operation included?
2:06
It's because even though the MakeNames
match from one table to another,
2:09
the IDs are different. Remember
that set operations in SQL
2:14
take into account the values from
all columns provided in your query.
2:18
So the database treats
those rows as different and
2:22
thus does not remove them when
applying the EXCEPT clause.
2:25
Like with the operations,
the EXCEPT keyword
2:30
is used between two queries that you'd
like to perform the operation on.
2:33