NOT EXISTS Operator
In SQL, NOT EXISTS Operator return boolean values. It is used for testing that if any records exists or not. If the any subquery do not results any values or return 0 then only the parent query will execute. In simple words, if the child query returns 0 then only the parent query will execute.
Syntax of NOT EXISTS Operator:
SELECT column01, column02,..........
FROM table_name
WHERE NOT EXISTS (subquery);
Example of NOT EXISTS Operator
In this example, we are going to display all the rows of the w3wiki table where there should not exist any record where the id of the geeeksforgeeks table is equal to id of the courses table and course is ‘DSA’.
Query:
SELECT id,name
FROM w3wiki
WHERE NOT EXISTS (SELECT 1 FROM courses
WHERE courses.id = w3wiki.id and courses.course = 'DSA' );
Output:
w3wiki – NOT EXISTS
Explanation: In the above query, we have specified in a subquery that there should not be any course named ‘DSA’ in our courses table. If there are not any courses that means our subquery will return 0. Therefore, NOT EXISTS, being a negation operator will display all the rows from w3wiki table.
NOT IN vs NOT EXISTS in SQL
Structured Query Language (SQL) is a domain-specific language used in managing and manipulating data in a relational database. In SQL, we use these two operators i.e. NOT IN and NOT EXISTS to filter out and efficiently retrieve our data from a table.
Both of these operators are negations of IN and EXISTS operators respectively. In short, they perform exact opposite types of operations compared to IN and EXISTS operators.
In this article, we are going to compare NOT IN and NOT EXISTS operators. We are going to discuss their syntax, working, and differences between them. We will also see examples related to them.