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
.
SELECT column_name FROM table_nameWHERE EXISTS(SELECT column_name FROM table_name WHERE condition);
It returns a Boolean value, either True
or False
.
In the code snippet below, we have an Employee
table, which contains Employee_id
, first_name
, last_name
, salary
, and department
.
-- Creating a worker tableCREATE 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 tableCREATE table bonus(Employee_id int,bonus_amt int,FOREIGN KEY(Employee_id) REFERENCES Employee(Employee_id));-- Inserting record in Employee tableINSERT INTO Employeevalues (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 tableINSERT INTO bonusvalues (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 5500SELECT Employee_id FROM EmployeeWHERE EXISTS (SELECT Employee_id FROM bonus WHEREbonus.Employee_id = Employee.Employee_idAND bonus_amt < 5500);
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.bonus
table that has Employee_id
as a foreign key and bonus_amt
as class attributes.Employee
and bonus
tables.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
.