EXISTS condition is used along with the subquery in SELECT statement. If subquery returns at least one record, then EXISTS condition executed otherwise it won't get execute the parent query.

EXISTS condition works with SELECT, UPDATE and DELETE statements.

SELECT column1, column2, …, columnN
FROM table_name
[WHERE [NOT] EXISTS (subquery)]; 

UPDATE table_name
SET column1 = value1, column2 = value2, …, columnN = valueN
WHERE [NOT] EXISTS (subquery); 

DELETE FROM table_name
WHERE [NOT] EXISTS (subquery); 

• column1, column2, …, columnN - Specifies the column names from table.
• table_name – Specifies the name of the table.

Let us consider the employee_details and department_details tables are as follows -

emp_id	emp_name	designation	manager_id	date_of_hire	salary	dept_id
001	Employee1	Director		2019-07-11	45000.00	1000
002	Employee2	Director		2019-07-11	40000.00	2000
003	Employee3	Manager	Employee1	2019-07-11	27000.00	1000
004	Employee4	Manager	Employee2	2019-10-08	25000.00	2000
005	Employee5	Analyst	Employee3	2019-07-11	20000.00	1000
006	Employee6	Analyst	Employee3	2019-10-08	18000.00	1000
007	Employee7	Clerk	Employee3	2019-07-11	15000.00	1000
008	Employee8	Salesman	Employee4	2019-09-09	14000.00	2000
009	Employee9	Salesman	Employee4	2019-10-08	13000.00	2000
010	Employee10	Clerk	Employee4	2019-11-12	15000.00	3000

dept_id	dept_name	dept_location
1000	Office	Location1
2000	Sales	Location2

Scenario – Fetch rows using EXISTS condition.

Requirement- Fetch details of employees who are belongs department of Sales. The query was as follows –

SELECT * FROM employee_details
WHERE EXISTS (SELECT dept_id 
    FROM department_details 
    WHERE dept_name = "Sales"); 

By executing above query, we can get results as shown below –

emp_id	emp_name	designation	manager_id	date_of_hire	salary	dept_id
002	Employee2	Director		2019-07-11	40000.00	2000
004	Employee4	Manager	Employee2	2019-10-08	25000.00	2000
008	Employee8	Salesman	Employee4	2019-09-09	14000.00	2000
009	Employee9	Salesman	Employee4	2019-10-08	13000.00	2000

Scenario – Fetch rows using NOT EXISTS condition.

Requirement- Fetch details of employees who are not belongs to department of Sales. The query was as follows –

SELECT * FROM employee_details
WHERE NOT EXISTS (SELECT dept_id 
   FROM department_details
   WHERE dept_name = "Sales"); 

By executing above query, we can get results as shown below –

emp_id	emp_name	designation	manager_id	date_of_hire	salary	dept_id
001	Employee1	Director		2019-07-11	45000.00	1000
003	Employee3	Manager	Employee1	2019-07-11	27000.00	1000
005	Employee5	Analyst	Employee3	2019-07-11	20000.00	1000
006	Employee6	Analyst	Employee3	2019-10-08	18000.00	1000
007	Employee7	Clerk	Employee3	2019-07-11	15000.00	1000
010	Employee10	Clerk	Employee4	2019-11-12	15000.00	3000

Scenario – Update rows with EXISTS condition.

Requirement – Update manager as "Employee1" for the employees who are not belongs to the department in department_details table. The query was as follows –

UPDATE employee_details SET manager_id = "Employee1"
 WHERE NOT EXISTS (SELECT dept_id FROM department_details); 

By executing above query, we can update manager as "Employee1" for the employees who are not belongs to the department in department_details table. The table after updation was as follows –

emp_id	emp_name	designation	manager_id	date_of_hire	salary	dept_id
001	Employee1	Director		2019-07-11	45000.00	1000
002	Employee2	Director		2019-07-11	40000.00	2000
003	Employee3	Manager	Employee1	2019-07-11	27000.00	1000
004	Employee4	Manager	Employee2	2019-10-08	25000.00	2000
005	Employee5	Analyst	Employee3	2019-07-11	20000.00	1000
006	Employee6	Analyst	Employee3	2019-10-08	18000.00	1000
007	Employee7	Clerk	Employee3	2019-07-11	15000.00	1000
008	Employee8	Salesman	Employee4	2019-09-09	14000.00	2000
009	Employee9	Salesman	Employee4	2019-10-08	13000.00	2000
010	Employee10	Clerk	Employee1	2019-11-12	15000.00	3000

Scenario – Delete the rows using EXISTS condition.

Requirement - Delete the employees who are not belongs to department of "Sales" or "office". The query was as follows –

DELETE FROM employee_details WHERE EXISTS (SELECT dept_id FROM 
 department_details); 

By executing above query, we can delete the rows employees who are not belongs to department of "Sales" or "office". The table after updation was as follows –

emp_id	emp_name	designation	manager_id	date_of_hire	salary	dept_id
010	Employee10	Clerk	Employee4	2019-11-12	15000.00	3000