Except

Video Transcription

Welcome back everyone. And in this video, we’re going to be taking a look at the Accept set operator. So accept is going to have similar syntax as we’ve seen so far. Again, input to query one on the left input query two on the right with the except operator in between, and an optional order by to end with. So what does the except operator actually do? So the except operator is going to be the difference between two sets. So the result is going to be everything. So a except B is going to be things that belong to a but not to be just like a subtraction operator, essentially. Except all does not exist, just like intersect all does not exist, and T SQL. So again, though, we can use a similar workaround that we’ve done with intersect all. But again, the default behavior for accept is accept distinct, so no duplicates. So what does our Venn diagram look like for the Accept set operator, so the Accept set operator works very much like our subtraction, right, so we have all of a except B, so A, but not B, if we’re talking in Boolean, so all of a but we exclude anything that anything of a that is shared with B. So if a value is in A and B, that value is removed, as part of this except set operator, let’s take a look at an example of this set operator being applied to two sets. So we’re going to draw out the set operator that are the sets that we had previously with Union and intersect. So this would be our one, two, and three for our first set, except which looks like our minus sign.

And then our input set to is going to be three, four, and five. So we have set a 123, set B three, four, or five. So if we have a except B, we have all of a except for values that are shared with set B. So we have one and two here, but three gets removed, because three exists also in set B, so all of a except B. But let’s take a look at some examples of this working in SQL. Alright, so here we have the similar query that we’ve seen. So far, I’m dealing with the email addresses for the primary contact and alternate contacts again, but now we’re doing the Accept. So if we execute this query, we get 597 rows this time around. But what does this actually give us? Right? What does this actually give us? Well, if we have the primary contact emails, except alternate contact emails, this gives us email addresses that are primary contacts, but not alternate contacts. So that’s what the except operator will actually get us here for our email addresses. So fairly expressive thing that is, again, somewhat that more difficult to write as a join, but still possible, still possible, just a little bit more difficult to actually write in other ways, right. But just like what we have with programming languages, right, we can the same, you know, expression, we can write in a same Boolean expression or function or whatever, we can write, or even just tic tac toe, right, we can write tic tac toe 1000s of different ways in Python, or Java or C sharp, but it doesn’t necessarily mean that any one of them is the best way. It’s just that there’s different ways of going about actually achieving that same task. So similar idea that we have in SQL, we can achieve the similar results that we tried last time for getting around the fact that T SQL does not support accept all. So we still have to use the original accepted distinct, but we still, we can still use the window trick. So if we have a window function that applies a unique number to each of the email addresses that are duplicates, we can run this and now we can actually see that we actually get more emails as a result. And then if we scroll down here, we can see there’s our friend Abel, that has it now a duplicate email as as a result of the Accept.

So this example does work very well with the accept all you can kind of see the exploitation that I can achieve here by avoiding by getting around the except operator that pulls out or that removes duplicates. But since these two rows have a unique number now attached to them, those rows are no longer the same, they are different. But same query, same exact query, just tossing all that into a CTE. So we can run just a simple select without that row number and our end query results. That is our accept operator. And that’s actually going to be our last set operator that we’re actually going to be covering as part of this course. So there are some things to consider when you are going to use multiple set operators or chaining set operators together. So, intersect is going to actually take precedence over union and accept. So intersect will happen first, and then union and then accept, although union is not exclusive, union and accept have the same precedence. So if union comes first, so union will happen first if it comes first before accept, or vice versa. But you can just like algebraic equations and other things in SQL, you can enforce order by using parentheses. So we can take a look at a couple of example, or here, let’s just take a look at one example.

And I’ll include more examples as part of the notes. But here is a simple query, where I have a list of brands, champion, Nike Under Armour, and then we have Nike Under Armour down here and then intersect Nike, Adidas. So the end result of this is champion and Under Armour. So let’s break this down real quick. If we do this query here, we get champion so champion, Nike Under Armour, except Nike Under Armour. So that leaves us with just champion left because that’s the one that is in set A but not in set B. And then we have intersect with Nike, Adidas, but that does, that’s not the actual execution order, right? Because we don’t execute the Accept first, because if we did, then champion intersect Nike, Adidas would actually result in an empty set would actually result in an empty set. What actually happens first, is this intersect, right? So the intersect happens, so we run that we get Nike, right? We get Nike, and then champion Nike Under Armour intersect Nike is going to just take out Nike and leave us with champion and Under Armour as a result. So we can actually force that order if we wanted to change things a little bit. So let’s say I wanted to force this, except to happen first, I can actually wrap that in parentheses. And so if I run that, Aha, there we go. Now we get what we expect, right? So this whole statement executes first as part of input query, one for the intersect function. So that is going to be something that we can achieve using parentheses. So just a quick little example to show that precedence of the set operators does matter. So order of operations is still going to apply when you’re utilizing the set operators, much like what you expect with many other things in SQL and your programming languages.