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

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.

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

the table will look like:

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:

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

Observe the below query:

Output:

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.

Now, we are going to insert VALUES into it.

So, the table will be in the form of:

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

Output:

Example 3:

Consider employee table below having records that are displayed below:

Here, we will remove the records of employees with the names Shreya Singh and Raj Pawar:

Output:

EXAMPLE 4:

Consider below Subscription table to exclude few records using NOT IN operator.

Let's exclude all the records holding "Premium" value in Subscription_name column:

Output: