SQL EXISTS Operator – Syntax and Examples

SQL EXISTS Operator

The SQL EXISTS operator is used to check if a subquery returns any records. It returns TRUE if the subquery contains any rows and FALSE if it does not.

The EXISTS operator is often used in WHERE clauses to filter results based on the presence of related records in another table.

In this tutorial, we will go through EXISTS Operator in SQL, its syntax, and how to use this operator in SQL statements, with well detailed examples.

---

Syntax of SQL EXISTS Operator

The basic syntax of the SQL EXISTS operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);

Each part of this syntax has a specific purpose:

• SELECT: Specifies the columns to retrieve from the table.
• FROM: Specifies the table from which to retrieve data.
• WHERE EXISTS: Checks whether the specified subquery returns any rows.
• subquery: A query within the main query that determines whether any rows are present.

If the subquery returns at least one row, EXISTS returns TRUE and the outer query proceeds. If no rows are returned, EXISTS returns FALSE, excluding the rows from the main query’s result set.

---

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the EXISTS operator in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality along with a grades table that records student grades.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Create a grades table:

CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    grade CHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

5. Insert sample data into the students and grades tables:

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

INSERT INTO grades (student_id, subject, grade)
VALUES
(1, 'Math', 'A'),
(2, 'Math', 'B'),
(3, 'Math', 'C'),
(3, 'Science', 'A'),
(4, 'Math', 'B');

---

Examples: Using EXISTS Operator in Queries

Now, let’s explore different scenarios of using the EXISTS operator with these tables.

---

1. Checking for Existence of Related Records

To select students who have at least one grade recorded in the grades table:

SELECT name FROM students
WHERE EXISTS (SELECT 1 FROM grades WHERE grades.student_id = students.id);

This query returns students with at least one related record in the grades table, displaying names like Alice, Bob, Charlie, and David.

---

2. Using EXISTS with a Condition

To select students who have an “A” grade in any subject:

SELECT name FROM students
WHERE EXISTS (SELECT 1 FROM grades WHERE grades.student_id = students.id AND grades.grade = 'A');

This query finds students with an “A” grade in at least one subject, returning records for Alice and Charlie.

---

3. Using EXISTS with NOT for Exclusions

To select students who do not have any grades recorded in the grades table:

SELECT name FROM students
WHERE NOT EXISTS (SELECT 1 FROM grades WHERE grades.student_id = students.id);

This query returns students who have no records in the grades table, displaying only the name Eva.

We have used SQL NOT Operator.

---

4. Using EXISTS to Verify Matching Conditions Across Tables

To select students in “Northside” locality who have received a grade in “Science”:

SELECT name FROM students
WHERE locality = 'Northside' AND EXISTS (SELECT 1 FROM grades WHERE grades.student_id = students.id AND grades.subject = 'Science');

This query returns students from “Northside” who have a grade in “Science”, displaying the name Charlie.

---

FAQs for SQL EXISTS

1. What does the SQL EXISTS operator do?

The EXISTS operator checks if a subquery returns any rows. If it does, EXISTS returns TRUE, allowing you to filter results based on the presence of related records.

2. Can I use EXISTS with other operators in SQL?

Yes, you can use EXISTS with NOT to exclude records or combine it with AND and OR for complex filtering.

3. What is the difference between EXISTS and IN?

EXISTS checks if any rows exist in a subquery, while IN checks if specific values from one set match values in another. EXISTS is often more efficient with large datasets.

4. Can I use EXISTS with aggregate functions?

Yes, EXISTS can be used with aggregate functions inside a subquery to filter records based on aggregate conditions.

5. Does EXISTS return any data from the subquery?

No, EXISTS only checks for the presence of rows in a subquery. It does not return any data from the subquery.