Set operators are one of the most powerful constructs of SQL and relational algebra. Unfortunately, they are rarely used even though they can overcome many common challenges elegantly and efficiently. In this video, learn about INTERSECT, EXCEPT, and UNION, and explore practical use cases that you can use to simplify query logic.
- [Instructor] You've heard me often complain … about features of SQL that are being abused. … Set operators are on the opposite side. … They seem to be underutilized, and that's a shame, … because they're one of the most powerful features of SQL, … yet they're very easy to use and understand. … The ANSI SQL standard defines three Set operators; … UNION, EXCEPT and INTERSECT. … Each of these has two variants; ALL and DISTINCT. … Many of you have used … or at least heard of UNION and UNION ALL, … but did you know that UNION … is just a shortcut for UNION DISTINCT? … And how about EXCEPT or INTERSECT ALL? … All means that the result may contain duplicates. … The default is DISTINCT, … which groups or eliminates duplicates. … Side note: The DINSTINCT set quantifier … used in the SELECT clause also has an old variant. … For SELECT, ALL is the default. … And you can try it, SELECT ALL * FROM Foo, … and see for yourself. … Moreover, the ANSI SQL standard defines an additional … MULTISET variant for all Set operators. …
This course was created by Ami Levin. We are pleased to offer this training in our library.