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:

</>
Copy
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:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

</>
Copy
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:

</>
Copy
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:

</>
Copy
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');