From the course: Advanced SQL: Logical Query Processing, Part 2

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Set operators

Set operators - SQL Tutorial

From the course: Advanced SQL: Logical Query Processing, Part 2

Start my 1-month free trial

Set operators

“

- [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…

Contents