SQL NOT IN

This article will help you in providing a clear demonstration of SQL NOT IN operator with the help of various examples. Before moving on to examples, let us understand what is SQL NOT IN operator and what is its SQL syntax.

What is SQL NOT IN?

The SQL NOT IN operator is used for excluding rows from a SQL query based on a predefined set of VALUES. It is frequently used in the SELECT statement's WHERE clause. The NOT IN operator's general syntax is as follows

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);

If the VALUES specified by the IN operator are not satisfied, the result is filtered using a NOT IN SQL query or the NOT IN operator. That indicates that the NOT IN SQL query compares the VALUES to the supplied expression or column name. If a match is found, the SELECT statement will not include that specific record in the result of the SQL query.

The NOT IN operator in SQL is exactly the reverse of the IN operator. Additionally, the logical operator NOT exists in SQL and can be used to pick the rows for which a conditional statement is false. Similar to this, when the WHERE clause and NOT IN operator are combined, the VALUES specified in the WHERE clause are not included in the output of the NOT IN SQL query.

Observe below examples to clearly understand how the SQL NOT IN operator works and how it works when it is used with various conditional clauses like WHERE.

EXAMPLE 1:

In order to apply NOT IN operator first, let us create a table named Player_info which contains basic information related to the player and insert VALUES into it.

CREATE TABLE Player_info(ID INT(10),
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Year_Of_Birth INT,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	);

Now, we are going to insert values into this table by using the INSERT INTO statement.

INSERT INTO Player_info VALUES(1, 'MS', 'Dhoni', 1981,  'Ranchi', 'India');
INSERT INTO Player_info VALUES(2, 'FAF', 'Duplessis', 1979, 'CapeTown', 'SouthAfrica');
INSERT INTO Player_info VALUES(3, 'Ruturaj', 'Gaikwad', 1994, 'Mumbai', 'India');
INSERT INTO Player_info VALUES(4, 'Virat', 'Kohli', 1990, 'Delhi', 'India');
INSERT INTO Player_info VALUES(5, 'Karn', 'Sharma', 1987, 'Haryana', 'India');
INSERT INTO Player_info VALUES(6, 'Ravindra', 'Jadeja', 1988,  'Nagpur', 'India');
INSERT INTO Player_info VALUES(7, 'Sam', 'Curran', 1998, 'Manchester', 'England');

the table will look like:

ID	First_Name	Last_Name	Year_Of_Birth	Place_Of_Birth	Country
1	MS	Dhoni	1981	Ranchi	India
2	FAF	Duplessis	1979	CapeTown	SouthAfrica
3	Ruturaj	Gaikwad	1994	Mumbai	India
4	Virat	Kohli	1990	Delhi	India
5	Karn	Sharma	1987	Haryana	India
6	Ravindra	Jadeja	1988	Nagpur	India
7	Sam	Curran	1998	Manchester	England

Now, we will apply NOT IN operator to exclude some of the records in the table using a specific condition.

This query will exclude the rows where ever the Place_Of_Birth is Manchester. So, the last record will be excluded and remaining all records are returned by the select statement.

Output:

ID	First_Name	Last_Name	Year_Of_Birth	Place_Of_Birth	Country
1	MS	Dhoni	1981	Ranchi	India
2	FAF	Duplessis	1979	CapeTown	SouthAfrica
3	Ruturaj	Gaikwad	1994	Mumbai	India
4	Virat	Kohli	1990	Delhi	India
5	Karn	Sharma	1987	Haryana	India
6	Ravindra	Jadeja	1988	Nagpur	India

Multiple VALUES can be passed to NOT IN operator so that more records can be excluded.

Observe the below query:

Output:

ID	First_Name	Last_Name	Year_Of_Birth	Place_Of_Birth	Country
1	MS	Dhoni	1981	Ranchi	India
3	Ruturaj	Gaikwad	1994	Mumbai	India
4	Virat	Kohli	1990	Delhi	India
5	Karn	Sharma	1987	Haryana	India
6	Ravindra	Jadeja	1988	Nagpur	India
3	Ruturaj	Gaikwad	1994	Mumbai	India

EXAMPLE 2:

Let us see another example on NOT IN operator

Consider a table called product_desc which consists of the selling price, and buying price of the product.

CREATE TABLE Product_desc(
   Product_id INT(10),  
   Product_name VARCHAR(100),
   Buying_price INT(20),
   Selling_price INT(10),
);

Now, we are going to insert VALUES into it.

INSERT INTO Product_desc VALUES (1, 'Wireless mouse', 5000, 6000);
INSERT INTO Product_desc VALUES (2,'Laptop', 60000, 65000);
INSERT INTO Product_desc VALUES (3, 'Tablet', 40000, 50000);
INSERT INTO Product_desc VALUES (4, 'Speaker', 2000, 2500);
INSERT INTO Product_desc VALUES (5, 'Earphones', 1000, 1200);
INSERT INTO Product_desc VALUES (6, 'SSD_disk', 7500, 9000);

So, the table will be in the form of:

Product_id	Product_name	Buying_price	Selling_price
1	Wireless_mouse	5000	6000
2	Laptop	60000	65000
3	Tablet	40000	50000
4	Speaker	2000	2500
5	Earphones	1000	1200
6	SSD_disk	7500	9000

Here, we will exclude the records of products Speaker and SSD_disk using the NOT IN operator.

Output:

Product_id	Product_name	Buying_price	Selling_price
1	Wireless_mouse	5000	6000
2	Laptop	60000	65000
3	Tablet	40000	50000
5	Earphones	1000	1200

Example 3:

Consider employee table below having records that are displayed below:

emp_id	emp_name	City	Age	salary
1	Suresh	Hyderabad	30	20000
2	Shreya Singh	Lucknow	27	25000
3	Kavitha Kulkarni	Delhi	33	60000
4	Raj Pawar	Amritsar	30	50000
5	Jay Sharma	Bangalore	25	40000
6	Sravan Kumar	Chennai	30	20000