SQL NOT IN Operator

In SQL Server, the NOT IN operator replaces a set of arguments with the <> or != operator that is combined with the AND operator. This operator can be used to make code more readable and easier to understand for SQL statements like SELECT, UPDATE, and DELETE. In most cases, it won’t affect performance.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (list_of_values);

Let’s create a database to understand NOT IN Operator in SQL.

Query

CREATE TABLE Emp(
   EmpID INT PRIMARY KEY,
   Name VARCHAR(50),
   Country VARCHAR(50),
   Age int(2),
Salary int(10) );
-- Insert some sample data into the Customers table
INSERT INTO Emp (EmpID, Name, Country, Age, Salary)
    VALUES (1, 'Alok',  'India', '23', '30000'),
                  (2, 'Ankit ',  'Australia',  '21', '45000'),
                  (3, 'Aditya', 'Sri lanka', '24', '40000'),
                  (4, 'Anamika',  'Austria', '25', '35000'),
                  (5, 'Aarohi', 'Spain', '22', '25000');
 Select * from Emp; 

Output

Emp Table

NOT IN Operators with Number

Example: Find the Name of the Employees who have not a Age equal to 23, 22, or 21.

Query

SELECT Name
FROM Emp
WHERE age NOT IN (23, 22, 21);

Output

output

NOT IN Operator With Duplicate Values

In SQL, the NOT IN operator is used to filter out a row in a database table if the specified column’s value isn’t in the specified list of values. The NOT IN operator doesn’t mean that duplicate values are ignored. Instead, it treats each of the values in the list as an independent value to check against the column’s values.

Example

SELECT * FROM Emp WHERE age NOT IN (21, 23, 25, 23, 36);

Output

output

The above query will be equivalent to the below query:

SELECT * FROM Emp WHERE age NOT IN (21, 23, 25, 26);

Output

output

NOT IN Operator with Strings

NOT IN operator is used to find out the data that is not present in the table. We can also use NOT IN to exclude the rows in your list. We should note that any kind of duplicate entry will be retained.

Example: Find the Name of all the Employees who are not belong to not Australia or Austria

Query

SELECT name FROM Emp WHERE country NOT IN ('Australia', 'Austria');

Output

output

NOT IN Operator with SELECT Statement

We can also use the NOT IN operator with a subquery of the WHERE Clause, We can return a list of records from the main query that are not present in the subquery of WHERE clause.

Query

SELECT * from Emp Where EMP_id NOT IN (SELECT Emp-Id from Account_details );

The above Query will return a list of records from the Emp table that are present in the the Account_details table.

Conclusion

In summary, the NOT IN operator in SQL is useful for filtering out rows that match a particular set of values. The NOT IN operator can be used for a variety of filtering and querying operations, but it is important to understand how it behaves, particularly when dealing with NULLs, and to think about the performance implications when dealing with large data sets.

FAQs on NOT-IN Operator in SQL

Q.1: How does SQL NOT IN Operator work?

Answer:

The NOT IN operator in SQL filters out rows in a table according to a condition that defines the values that shouldn’t be in a certain column. The NOT IN operator works by filtering out rows where the column value specified in the condition is in a list of values, or in a subquery.

Q.2: What is the Syntax of the SQL NOT IN Operator?

Answer:

The syntax of the SQL-IN Operator is as follows:

SELECT column_name(s) FROM table_name
WHERE column_name NOT IN (list_of_values);

Q.3: Is SQL NOT-IN Operator Used for only numeric data?

Answer:

No, SQL NOT-IN Operator is used for both numeric and non-numeric data. such as Strings, It will work fine for any data type.