How Do You Use SQL Exists?
In previous SQL tutorials, we’ve talked about using:
- SQL Inner Join to select matching records in all joined tables
- SQL Outer Join to select records that exist in one table, regardless of their existence in another table
When it comes to learning SQL, these joins are the basic operations on database tables. But there are times that they’re not enough to get you the right result sets. For example, what if we only want records that exist in one table BUT NOT in another table? In this tutorial, you’ll learn the SQL operator Exists.
Take a look at these two tables.
The MATH_CLASS1 table contains students who are in the math level 1 class, while the MATH_CLASS2 table contains students who are in the math level 2 class.
A couple of students, Kevin and Annie, are in both classes.
If we want to select students who are in both classes, we can use SQL Inner Join:
SELECT MATH_CLASS1.Student as Student, MATH_CLASS1.Mark as Math_mark1, MATH_CLASS2.Mark as Math_mark2 FROM MATH_CLASS1 JOIN MATH_CLASS2 ON MATH_CLASS1.Student = MATH_CLASS2.Student;
Here’s a little quiz. What does this SQL statement return?
SELECT MATH_CLASS1.Student as Student, MATH_CLASS1.Mark as Math_mark1, MATH_CLASS2.Mark as Math_mark2 FROM MATH_CLASS1 LEFT OUTER JOIN MATH_CLASS2 ON MATH_CLASS1.Student = MATH_CLASS2.Student;
Answer: It returns records of students who are in the math level 1 class, and if these students are also in the level 2 class, include their marks for that class.
If you want the marks of students who are in the math level 1 class but not in the level 2 class, how do you do that?
One way to do that is to use SQL Exists… NOT Exists to be exact.
SELECT MATH_CLASS1.Student as Student, MATH_CLASS1.Mark as Math_mark FROM MATH_CLASS1 WHERE NOT EXISTS (SELECT * FROM MATH_CLASS2 WHERE MATH_CLASS2.Student = MATH_CLASS1.Student);
The WHERE clause of this SQL statement excludes students who are in the math level 1 class AND in the level 2 class.
Let’s look at another example.
Instead of having one table for each level of math class, this version of the MATH_CLASS table keeps track of the students in all levels of math class.
How do you select the students who are in both math level 1 and level 2 using SQL Exists?
SELECT * FROM MATH_CLASS a WHERE Level = “1” AND EXISTS (SELECT * FROM MATH_CLASS WHERE Student = a.Student AND Level = “2”);
Let’s break the SQL statement down into two parts:
The red SQL statement selects all students who are in math level 1. The blue statement applies an additional rule on the result set of the red statement, i.e. include those who are in level 2.
Note that I have given the first occurrence of the MATH_CLASS table an alias (a) because I have to use the same table in the subquery (a query nested within another query).
If you can’t use SQL Exists, you can use SQL Join to produce the same result set.
SELECT a.* FROM MATH_CLASS a JOIN MATH_CLASS b ON a.Student = b.Student WHERE a.Level = “1” AND b.Level = “2”;
Although there is only one MATH_CLASS table, you refer to it twice as if they are two separate tables.
Originally published at www.ilearnsql.com.