I've got two tables of toys here.
I want to find all the toys that are the same in both tables.
I could do this using a join.
But, there is another way.
Set operators!
Set operators allow you to combine all the rows from both tables with union.
Just those that exist in both tables with intersect.
Or those that in one table but not the other with minus.
To use them, all you have to do is query the tables, and stick the relevant operator between
them.
But to do this the tables must be the same shape.
That is return the same number of columns with the same data types in each position.
If they're different, you'll get an error.
You can overcome this by selecting the columns that are present in both.
Union is likely to be the operator you'll use the most.
You use this to combine tables with similar data together.
For example, say you've got a table of teddies and a table of bricks.
And you want to combine them into one toy table.
To do this, just select the appropriate columns from each and stick union between the two queries
Do this and you'll get one row for each unique toy in your results.
All your duplicate bricks are discarded.
So why is this?
And how can you avoid it?
Well union adds a distinct operator to your results.
This squashes out duplicates.
To see all the rows you need to add the all keyword after union.
So you may need to do this to get the answer you want.
But, even if you're combining tables where you're sure there's no duplicates, it's still
worth using the union all variant.
This is because the distinct operation gives your query more work to do.
If your tables are huge, this can really slow your SQL down.
Another common reason for using set operators is finding all the rows in one table that
aren't in another.
For example, say I want to find all the bricks that are in this table, but don't exist in
the other one.
You can do this with not exists, joining all the columns from the first table to the second
in the subquery.
But this is tedious if the tables have more than a couple of columns.
And null gets in the way again!
If any of the columns could be null, you need to ensure you're handling this correctly.
Minus simplifies all of this.
All you need to do is query both tables and stick minus between them.
This is one of the rare cases where null values are considered equal.
So the database will handle these correctly without any extra effort from you.
Like union, minus removes duplicates.
So if you need to see these, you've got to go with not exists.
Combining union and minus leads to a classic use case: checking if two tables have exactly
the same rows.
Let's say I'm splitting up the bricks between my daughters.
And, to help avoid fights, I want to ensure the two tables here have exactly the same
bricks on them.
To do this, we can first check which bricks exist on this table, but not this one.
And then do the opposite.
Find the ones on here, that don't exist here.
Finally, we can combine the results with union to see all the differences.
With set operators this is simple.
Minus each table from the other and union the results.
But run this and all we get is the second table not in the first.
What's going on?!
In Oracle Database the set operators all have the same priority.
So the query finds all the rows in the first table not in the second.
Then it adds back in the rows from the second.
Before finally removing the rows from the first.
Which is the same as just minusing the first from the second.
You fix this same way as forcing order of precedence in any mathematical operation.
Place brackets around the two minus subqueries.
This forces the database to process these first.
Then combine the results.
While this is the classic way to compare tables, you have to read all the rows from both tables,
twice!
Something you want to avoid, particularly if your tables are massive.
It's better if read the rows from each table just once.
To see if we can do this, let's revisit the requirements.
We want to see we have the same bricks in both tables.
So after we combine them, we should have two copies of each row in the table.
If there's any that appear just once, then we have a mismatch.
We can do this by combining the tables with union all.
Then checking we have two copies of each brick.
A classic group by operation.
And with having clause we can filter out the rows we don't have two copies of, so we see
just the mismatches.
Note this assumes that each table has no duplicates.
If you have two copies of a brick in this table, which don't appear in this table, you'll
still get a count of two and filter them out of your results incorrectly.
What you need to do instead is count how many bricks you have of each type first, then include
this in your comparison.
You can do this by adding two extra columns to each table in your union.
For the top table, the first returns one and the second zero.
And you do the opposite for the bottom table.
Summing these up in your outer query will tell you how many bricks you have of each
type from each table.
So you can find the mismatches by comparing these in the having clause.
As a bonus, you can also see which table has the extra rows.
Do this by comparing the sums in the select and returning the name of the table which
has more.
So, we've covered minus and union.
Now you may be thinking to yourself, if minus is similar to not exists, surely intersect
is similar to plain old exists.
If so, congratulations!
Intersect works the same way as an exists with a distinct.
So you get all the rows that exist in both tables.
You may find great uses for this, but it's rare I've had to use it in real code.
So that was a quick overview of set operators.
They're a handy way to compare tables, particularly if you have a lot of nulls to deal with.
But that have other uses too.
Particularly union which we'll use in our next topic.
Creating SQL trees!
Thanks for watching!
Have you used set operators?
Let us know in the comments.
And remember to subscribe for future updates.