What is the SQL exists operator?

Overview

In SQL, EXISTS is a logical operator that allows users to check the existence of any record in a subquery. It returns True when an exist returns one or more records. Otherwise, it returns False.

Syntax

-- Creating a worker table
CREATE table Employee(
    Employee_id int,
    first_name varchar(100), 
    last_name varchar(100),
    salary int,
    department varchar(100),
PRIMARY KEY(Employee_id));
-- Creating a bonus table
CREATE table bonus(
    Employee_id int,
    bonus_amt int,
FOREIGN KEY(Employee_id) REFERENCES Employee(Employee_id));
-- Inserting record in Employee table
INSERT INTO Employee
values (1, "Charlie", "Arora", 100000, "Engineering"),
       (2, "Niharika", "Verma", 80000, "Admin"),
       (3, "Thomas", "Martin", 300000, "HR"),
       (4, "William", "O'Brien", 500000, "Admin"),
       (5, "Vivek", "Bhati", 500000, "Admin"),
       (6, "Vipul", "Diwan", 200000, "Account"),
       (7, "Satish", "Kumar", 75000, "Account"),
       (8, "Anderson", "Lee", 90000, "Admin");
-- Inserting record in bonus table
INSERT INTO bonus 
values (1, 5000),
       (2, 5500),
       (3, 4000),
       (1, 4500),
       (2, 3500);
-- This nested query will return unique employee ids 
-- where employee ids in bonus table equals to employee
-- ids in employee table and bonus amount less than 5500
SELECT Employee_id FROM Employee
WHERE EXISTS (SELECT Employee_id FROM bonus WHERE
              bonus.Employee_id = Employee.Employee_id
              AND bonus_amt < 5500);

Explanation

Lines 2–9: We create an Employee table that has the fields: Employee_id, first_name, last_name, salary, and department. Moreover, Employee_id is a primary key in the Employee table.

Lines 12–16: We create a bonus table that has Employee_id as a foreign key and bonus_amt as class attributes.
Lines 19–37: We insert data in the Employee and bonus tables.
Lines 42–46: We use the EXIST operator in an SQL query. This nested query returns unique employee IDs, where Employee_id in the bonus table are equal to Employee_id in the Employee table. The bonus table should be less than 5500.

What is the SQL exists operator?

Overview

Syntax

Return value

Example

Explanation