- [Narrator] Hello, and welcome back. Now let's examine and get familiar with the EXISTS predicate. I wanna show you how to use this EXISTS predicate. Now, you might remember, I was showing you how we could get departments that didn't have salespeople, and we went through the fact that that would work fine for some departments without salespeople, or salespeople without sales, and companies without contacts. So, in order to find contacts without sales, a contact has two parts to the primary key. Apart from the identifier, there is also our foreign key. So what I can do is, rather than using this form of subquery, what I could do is use an EXISTS test. I want to get data from the contact table where it exists. Now the EXISTS predicate is designed specifically for subqueries, and it is true if the subquery returns data, it sends a true or false. So here, if I have a query that selects one, I get select one back. In Oracle, you have to do select one from DUAL, and that will return a result, and therefore, for every row in this contact table, it will return a result. I can also do not exists, and then if it returns data, I get no rows. So basically it's a switch, it's a binary on-off. If I did something, like, where one equals zero, that would return no data, and then because this is not exists, I will get a response. So that's switching the results on and off. What I can do in here is I can select something from the sales table, and in my subquery, I want to go and look to see if I can find this sale for a specific contact. This form of subquery is going to work completely differently from the ones we looked at previously, where the subquery comes first to generate recessive data, which gets caged in memory, and then used by the main query. What I'm about to show you now is what's called a correlated subquery. What will happen is it will start by running through the main query. Now that will say, okay, I'm looking for contact in the contacts table, and now we'll run the subquery to see whether it returns yes or no. Now in the subquery, I'm going to look to see if I can find a sale for the contact which is currently under investigation. I need to go and see if there's a sale where the company number on the sale is equal to the contact's company number, and the contact code on the sale is equal to the contact's contact code. What we will have here is a list of contacts to whom we have not made a sale, and that is a correlated subquery. One of the consequences of being correlated is that I simply cannot run that part of the query, it was related to the main query. There's a link between the two, it's logically a kind of join. Now this is the EXISTS predicate, which is used with subqueries, but I would also suggest correlated subqueries is what it's really here for. You see, not in, or in, or not in, so this is salespeople who have made the sale, and this is salespeople who haven't sold. Okay, so that brings to a end our lecture on the EXISTS predicate.