Text copied!
NOT EXISTS operator
In SQL, Operators are special character, symbol or a keyword that is used to perform some specific operations. For example : comparing data
SQL operators are commonly used with SQL statements to retrieve, filter, and manipulate data.
NOT EXISTS operator :
NOT EXISTS operator is used to check whether rows exists in a subquery or not. It retrieves only those rows that doesn't exist in a subquery. When a query is placed inside the 'parentheses ( )' of another query, referred to as 'subquery' or 'nested query'.
The syntax of the NOT EXISTS operator generally looks like this :
SELECT column_name(s)
FROM table_name
WHERE NOT EXISTS (subquery);
• Specify the column(s) name after the 'SELECT' keyword.
• Specify Asterisk (*) symbol to selects all columns from the table after the 'SELECT' keyword.
• Specify the table name after the 'FROM' keyword.
• Specify the conditions after the 'WHERE' keyword.
• Specify the subquery after 'NOT EXISTS' keyword.
Here's an example of how you might use the NOT EXISTS operator :
Let's suppose we have two tables :
[a] Employees Table :
→ It contains columns [Employee_Id], [Employee_Name], [Gender] and [Salary].
[b] Employees_2 Table :
→ It contains all columns same as '[Employees] table' with same data types and order. Except '[Age]' column.
Let's assume you want to check and retrieve employee data from [Employees] table if there are rows that don't have any matching Employee_Id with the [Employees_2] table.
Run below SQL statement :
SELECT *
FROM [Employees]
WHERE NOT EXISTS ( SELECT *
FROM [Employees_2]
WHERE Employees].Employee_Id = [Employees_2].Employee_Id );
Above statement will retrieve employee data from [Employees] table where Employee_Id is 2, 3, 4 and 5, as these are the only unmatched Employee_Id in both the tables.
