Hello Friends, In this article, we will discuss the EXIST and NOT EXIST operators in SQL. Use EXISTS to identify the existence of a relationship without regard for quantity. For example, EXISTS returns true if the subquery returns single rows, and NOT EXISTS returns true if the subquery returns no rows.
EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
Examples:
Consider the following two relations “Employee” and “Projects”.
Employee_Master :
Projects:
1. Using EXISTS condition with SELECT statement
The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records and false if no row is selected.
To fetch the employee who is working on the “White Card”.
SELECT ID,NAME,DEPARTMENT,DESIGNATION
FROM dbo.Employee_Master EM
WHERE EXISTS (SELECT *
FROM dbo.Projects P
WHERE P.PROJECT_NAME='White Card' AND P.EMPLOYEE_ID = EM.ID);
2. Using NOT with EXISTS
The NOT EXISTS operator negates the logic of the EXISTS operator. The NOT EXISTS operator returns true if the subquery returns no record. However, if a single record is matched by the inner subquery, the NOT EXISTS operator will return false.
To fetch the employee who is not working on the “White Card”.
SELECT ID,NAME,DEPARTMENT,DESIGNATION
FROM dbo.Employee_Master EM
WHERE NOT EXISTS (SELECT *
FROM dbo.Projects P
WHERE P.PROJECT_NAME='White Card' AND P.EMPLOYEE_ID = EM.ID);
3. Using EXISTS condition with UPDATE statement
To update the salary of the employee who is working on the “COVID-19 Hospital : Test & Booking Application”.
UPDATE EM SET EM.SALARY = EM.SALARY+(EM.SALARY*12/100)
FROM dbo.Employee_Master EM
WHERE EXISTS (SELECT *
FROM dbo.Projects P
WHERE P.PROJECT_NAME='COVID-19 Hospital : Test & Booking Application' AND P.EMPLOYEE_ID = EM.ID);
SELECT * FROM dbo.Employee_Master
4. Using EXISTS condition with DELETE statement
To delete the salary of the employee who is working on the “Food Wastage Reduction”.
DELETE
FROM dbo.Employee_Master
WHERE EXISTS (SELECT *
FROM dbo.Projects P
WHERE P.PROJECT_NAME='Food Wastage Reduction' AND P.EMPLOYEE_ID = dbo.Employee_Master.ID);
SELECT * FROM dbo.Employee_Master
I hope this article helps you and you will like it.
