The SQL Server EXISTS predicate is a very handy tool we can use when querying SQL Server databases.
Do you need to know how EXISTS works? You’ve come to the right place!
In this very brief tutorial, we’ll discuss how the EXISTS predicate works and provide examples of how to use it.
We’ll discuss these topics:
- What is the EXISTS predicate?
- The EXISTS predicate operates similarly to a correlated subquery.
- An example of using the SQL Server EXISTS predicate.
- NOT EXISTS is just the opposite of EXISTS
- Tips and tricks
Don’t forget to download your FREE GUIDE:
FREE 1-PAGE Simple SQL Cheat Sheet on the SQL Server EXISTS Predicate!
This guide covers everything you need to know about the EXISTS predicate, condensed into a simple 1-page document. It will be an excellent resource for you to reference in your career as a database professional. Make sure you download it today!
Let’s take it from the top.
1. What is the SQL Server EXISTS predicate?
The EXISTS predicate is used to determine if a subquery returns any rows at all. If the subquery returns rows, the EXISTS predicate returns True. Otherwise, it returns False.
We’ll start with what a subquery is in the first place. A subquery is an inner query that is contained within some outer query. Here is a very simple example of a query that uses a subquery (this query does not use the EXISTS predicate):
SELECT * FROM Orders WHERE CustomerID IN ( SELECT CustID FROM Customers WHERE City = 'Houston' )
This simple example will give us order information for customers who live in the city of Houston.
The inner query is this one: SELECT CustID FROM Customers WHERE City = 'Houston'
The result of this query (which is simply a list of CustID values) is used as input to the IN clause of the outer query.
When it comes to the SQL Server EXISTS predicate, we’ll still be using inner/outer queries, but the way it works is a bit different.
2. The EXISTS predicate operates similarly to a correlated subquery.
If you already know a thing or two about correlated subqueries, you’ll have an easy time understanding the EXISTS predicate. But if not, don’t sweat it. We’ll still go over everything step-by-step.
The inner query of an EXISTS predicate will usually contain a reference to a column value from the outer query. This means the inner query cannot be ran independently.
The simple example above is a noncorrelated subquery, also known as a self-contained subquery. This simply means you can run the subquery by itself without error. But again, when it comes to correlated subqueries and the EXISTS predicate, you cannot run the inner query by itself because it contains a reference to a column in the outer query. The two are linked in that way.
If you’re confused, I understand. The SQL Server EXISTS predicate and correlated subqueries are a bit tricky to understand, but we’ll break it down in the next section.
Speaking of…
3. An example of using the SQL Server EXISTS predicate.
Ok, enough chit chat. Let’s write a query that uses the SQL Server EXISTS predicate and discuss how it works.
For this example, we’ll say you work for a University and you have a table to track the list of Majors offered at your school, and another table to track Student information.
Here are the statements to create these tables and the data in your environment if you want to follow along (recommended):
CREATE TABLE CollegeMajors
(
MajorID INT IDENTITY PRIMARY KEY,
MajorName VARCHAR(30) NOT NULL
)
INSERT INTO CollegeMajors (MajorName)
VALUES
('Computer Science'),
('Information Technology'),
('Snowman Engineering'),
('Graphic Design'),
('Computer Criminology'),
('Basket Weaving'),
('Left Handed Knitting'),
('Biology'),
('Chemistry'),
('Backflip Theory')
GO
CREATE TABLE Students
(
StudentID INT IDENTITY(100,5) PRIMARY KEY,
FirstName VARCHAR(15),
LastName VARCHAR(15),
DeclaredMajor INT NOT NULL
)
INSERT INTO Students (FirstName, LastName, DeclaredMajor)
VALUES
('Jeff','Alanzo', 2),
('Amy','Bell', 1),
('Marla','Bridge', 2),
('Andrew','Bucks', 8),
('Sara','Carlton', 5),
('Kimberly','Cutter', 8),
('Jack','Faun', 4),
('Sam','Green', 1),
('Max','Mills', 9)
GO
Here is our CollegeMajors table:
Some rather interesting Majors, wouldn’t you say?
Then we have the Students table to track student information:
Notice the DeclaredMajor column. The number in this column is an unofficial foreign link to the CollegeMajors.MajorID column (I say it’s unofficial because we didn’t actually create a foreign key constraint. It’s not necessary for this tutorial). We use this column to identify what Major the student has chosen.
Ok, let’s think about something we might want to see. What if we wanted to list out all the Majors where at least one student has declared it as their Major?
Maybe we want to know what Majors people are actually interested in.
Here’s the query, and then we’ll talk about it:
Values from the outer query are passed to the subquery
Earlier, I mentioned that the inner query used with the EXISTS predicate usually contains a reference to a column value from the outer query. That referenced column in our subquery is M.MajorID, which I’ll highlight in red:
When executing this whole query, SQL Server will basically go down the list of rows from the outer query’s result set, one by one, and pass that row’s MajorID value to the subquery so that it can execute successfully and be evaluated.
If the subquery evaluates to True, that row from the outer query is returned to the final result set. If the subquery evaluates to False, the row is not returned to the final result set.
To better understand how this works, let’s walk down the list of outer query rows just like SQL Server will do to see what happens.
Outer rows are evaluated one by one
Let’s take a look at the rows from the outer query:
As I said, SQL Server will go down this list one by one. So let’s do the same. We’ll start with the first row:
Remember, the MajorID value of this row will be passed to the inner subquery. So essentially, when SQL Server is processing this row, it will run the following query:
We can run this inner subquery by itself now to see if any rows get returned (which will tell us if any rows exist in the Students table with this particular MajorID value):
There are definitely rows that exist within the Students table with this particular MajorID value of 1. Therefore, this “Computer Science” outer row will get returned in the final result set.
Let’s move on to the next row from the outer query:
Alright, so let’s plug this row’s MajorID value of 2 into the subquery to see if anything gets returned:
Yep, rows exist for this value also. This outer row will also be seen in the final result set.
Moving right along, let’s look at the next row in the outer query:
Hmmm, a Major of Snowman Engineering? Let’s see if there are any students who have declared this as their Major:
Doesn’t look like it. There does not exist a student who has declared Snowman Engineering as their major. The “Snowman Engineering” row will NOT be in the final result set.
An outer row that evaluates to True will be pulled onto the final result set
Folks, this pattern keeps going for all the rows in the outer result set. The MajorID value for each outer row is passed to the subquery, which checks for the existence of any results. If the subquery returns a result set (aka if rows exist), then the subquery evaluates to True and the outer row is pulled onto the final result set. Otherwise, if the subquery does not have any results (aka if rows don’t exist), the subquery evaluates to False and the outer row is not pulled onto the final result set.
So once again, here is the final result set. This tells us what CollegeMajor values contain at least one reference in the Student table:
4. NOT EXISTS is just the opposite of EXISTS
Folks, the NOT EXISTS predicate is basically the opposite of EXISTS. If the subquery evaluates to False, then the outer row is pulled onto the result set. If the subquery evaluates to True, then the outer row is not pulled onto the final result set.
For example, if we just change the query to say “NOT EXISTS“, it will tell us what Majors contain no references in the Students table at all (in other words, these are Majors that literally nobody is interested in):
Looks like maybe we can drop these Majors from our curriculum. Any future students looking to get their degree in Snowman Engineering will have to enroll somewhere else!
5. Tips and tricks
Here are just a few tips and tricks you should know about the SQL Server EXIST predicate:
Tip # 1: It’s okay to use the star operator (*) in your subquery
Most of the time, it’s a waste of valuable time to use the star operator in the column list of a SELECT statement, which is why it’s usually not recommended that you use it.
However, when it comes to the EXISTS predicate, SQL Server really isn’t interested in the column values of the inner SELECT statement. SQL Server really just wants to know if something got returned. When SQL Server see’s the use of the EXISTS predicate, it’s smart enough to know that we aren’t trying to pull column information. We just want to know if rows freaking exist.
Tip # 2: EXISTS uses two-valued logic, as opposed to three-valued logic
Most tools in SQL Server use three valued logic: True, False, and unknown.
The EXISTS predicate is not one of those tools. If you think about it, a query is either going to return something or it isn’t. There is no “Maybe it will return something?“.
The “unknown” scenario comes into play when working with NULL. Learn more about NULL here:
SQL Server NULL: Are you making these 7 mistakes?
Tip # 3: It’s helpful to hard-code values into your subquery when debugging or testing
That trick we did of hard-coding the MajorID into the subquery is exactly what you should do if you find yourself confused by the results of a query using the EXISTS predicate.
As mentioned earlier, queries with the EXISTS predicate can be difficult to troubleshoot. This is because the inner query cannot be ran on it’s own since it contains a reference to a column in the outer query.
The answer to that is to turn the inner query into something you can run by itself. Turn that inner query into a self-contained query by just temporarily hard-coding the referenced value and see what gets returned!
Next Steps:
Leave a comment if you found this tutorial helpful!
If you found this tutorial helpful, don’t forget to…
Download your FREE 1-page Simple SQL Cheat Sheet on the SQL Server EXISTS Predicate!
As mentioned earlier, the SQL Server EXISTS predicate is similar to correlated subqueries. I have a full tutorial on correlated subqueries you should definitely read next:
Correlated Subqueries: A Beginner’s Guide with Examples
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
