SQL Exists
SQL Exists
This tutorial covers SQL Exists operator.
The “exists” operator is part of the logical operators.
This operator tests for the existence of row records, by specifying subqueries.
The concept of “exists” can work similarly to a statement of subqueries with the “in” operator.
For more information on operators here.
Here we use two tables. First is the table “student_info” from the previous tutorials, and the second is a table named “grade“, linked to the first table with a foreign key.
| id | firstName | lastName | age |
|---|---|---|---|
| 1 | John | Smith | 21 |
| 2 | Patrick | Casey | 26 |
| 3 | Sam | Adams | 19 |
| 4 | Mike | Garcia | NULL |
| 5 | Patrick | Dumont | 21 |
| id | subjectName | grade | passed | studentID |
|---|---|---|---|---|
| 1 | History | C | 1 | 1 |
| 2 | Physics | A | 1 | 2 |
| 3 | History | F | 0 | 3 |
| 4 | Biology | A | 1 | NULL |
| 5 | Biology | E | 1 | 5 |
Exists
The “exists” logical operator tests for existence of rows.
Let’s see how it operates with two conditions. For instance, we want to check and list all student first names that have connections to the “grade” table (through the id/studentID and passed columns).
In other words, we check and list all student first names that have passed the subject.
SELECT firstName FROM student_info WHERE EXISTS (SELECT passed FROM grade WHERE student_info.id = grade.studentID AND passed=1);
| firstName |
|---|
| John |
| Patrick |
| Patrick |
The query outputs 3 student records (one student has not passed and the other has a NULL value for studentID).
The “where” statement in the subquery checks whether students have passed the subject (passed=1). In addition, it tests if ids match in both tables.
Next: SQL Any
by
Related Posts
