Except is very nice when filling filter tables and you don't want to insert rows that already exists in the table. Using it inside the exists is really neat and then the output columns don't have to match all the parts. No one is using exists either so that it is really useful in combination with exists is probably another reason why they aren't much seen.
I find that I forgot both INTERSECT and EXCEPT after learning semi-joins. This video is great evidence that I should reconsider, but it all just comes up too rarely.
I'm one of the few who use INTERSECT in production systems (yay). I most often make use of it when merging data from one table into another, especially when updating data, as I don't want to see any rows updated if I run the same query again. Works pretty well when using the merge syntax too... WHEN MATCHED AND NOT EXISTS (SELECT target.* INTERSECT SELECT source.*) THEN UPDATE ...
Except is very nice when filling filter tables and you don't want to insert rows that already exists in the table. Using it inside the exists is really neat and then the output columns don't have to match all the parts. No one is using exists either so that it is really useful in combination with exists is probably another reason why they aren't much seen.
Yes, they're rather complex query expressions. Every time I have to use them it takes me a couple tries to get it all right.
I find that I forgot both INTERSECT and EXCEPT after learning semi-joins. This video is great evidence that I should reconsider, but it all just comes up too rarely.
If you keep them in mind, you tend to find more interesting uses for them.
I'm one of the few who use INTERSECT in production systems (yay). I most often make use of it when merging data from one table into another, especially when updating data, as I don't want to see any rows updated if I run the same query again. Works pretty well when using the merge syntax too...
WHEN MATCHED AND NOT EXISTS (SELECT target.* INTERSECT SELECT source.*) THEN UPDATE ...
Yes! That’s a very good use for it.
Should I take this as a hint to subscribe to Paul White?
Everyone should.