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

SELECT column_name FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
Syntax for EXISTS in SQL

Return value

It returns a Boolean value, either True or False.

Example

In the code snippet below, we have an Employee table, which contains Employee_id, first_name, last_name, salary, and department.

-- 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.