Video Transcription

Welcome back everyone. And in this video, we’re going to be talking about sets. So everything that we’ve been doing so far in databases has been primarily working with sets. If you remember from data structures, sets are pretty much just an unordered list. But operations on sets work a little bit differently when comparing to other data structures. So set operators that we’ll be covering today, take in on the left side, a query and the right side a query. And so the input query one input query to this is just a fully executed query results. So if we just run a select star from Table B, and set operators select star from Table A, we can combine all those results in a certain way based off of which set operator we use. And that of course, there’s also an optional order by so remember, sets are just unordered lists. And so our query results unless they are actually ordered, are not guaranteed in any particular sorted order. But let’s talk about the general processing order here with our set operators. So first off, our input queries are going to be actually executed before the operator gets a chance to actually execute, of course, because we need our operands in full first, so the input query on the left will be executed first, and then the input query two is going to be executed. And then those two are then combined using the set operator. Now, all the normal logical operating phases here are relatively normal as you would expect, with the individual input queries, except with the order by order by is a little bit different here and inside of or utilizing a set operator. So it doesn’t really matter at all what order the input queries here are to the set operator. Because sets in nature are an unordered data structure. And so order matters not for running our set operators like Union intersect and except that we’ll cover here in a little bit.

And so input query one and two should really don’t need an ORDER BY clause as part of them. But you can order the results of the set operator by adding order by after the set operator actually finishes executing. So along with the set operators in SQL Server, we have also a set of multi set operators. Now this is also this also exists inside standard SQL as well. So really, what’s the difference here between a set operator and a multi set operator? Well, generally speaking, set operators are typically concerned with just the existence of a row in a set. So if I’m combining a set, set a was set B, what is in set A what rows are in set A what rows are in set B, and then I work on combining those based off of the existence of a value in one set or the other, a multiset operator is going to be concerned with the number of occurrences of that value in each set. So that’s the very minute difference here. But the benefit that the multiset operators actually allow us to have is that if we have things like duplicates, we can actually include those duplicates as part of our results if a multiset operator exists for the set operator we’re actually working with. So I do want to very briefly here, just kind of refresh everyone’s memory for what a set on paper will look like. You should have already seen this with data structures. But I wanted to highlight a couple of differences here. So we’re familiar with this, right? We have 123 here. So that is a list, right? So this is a list. And then we have this notion with parentheses. So we have 123 here, this is a tuple and then our sets are typically denoted using curly brackets 123. So I really just wanted to write this up here very quick like primarily because if you look for any information about sets, whether it be in your math class or online, searching for set operators, things like that, you will see this In this style of writing here when defining a set, so just keep that in mind. But most of the time, most of the work that we’re actually doing with databases is just the result set of a query. But in the following videos, we’re going to be talking about a couple of the set operators that we have access to in SQL Server.