NOT IN Sql query

NOT IN Sql query

Blog Bustle 19/03/2020

Sql NOT IN operator is a short way to write multiple not conditions. When we combine multiple not conditions using AND operator, sql query becomes long. So we use not in sql query. Sql NOT IN operator is used with WHERE clause. We can use Sql NOT IN operator with sql select, delete and update query.

NOT IN Sql query syntax

1.Sql NOT IN operator with select query

select column_1, column_2, column_3, ....
from table_name
where column_x NOT IN(value_1,value_2,value_3,....);

2.Sql NOT IN operator with update query

update table_name
set column_1 = value_1,
column_2 = value_2,
column_3 = value_3,
....
where column_x NOT IN(value_1,value_2,value_3,....);

3.Sql NOT IN operator with delete query

delete column_1, column_2, column_3, ....
from table_name
where column_x NOT IN(value_1,value_2,value_3,....);

Let's look some examples to understand NOT IN sql query.

I have employee table.

id name city country
1 justin New york US
2 robin Binghamton US
3 jessie Nottingham England
4 chris Manchester England

I want to find those employee records who do not belong to New york and Nottingham city and not in sql query will be

select * from employee where not city = 'New york' and not city = 'Nottingham';

or

select * from employee where city not in('New york','Nottingham');

After running this not in sql query, result will be

id name city country
2 robin Binghamton US
4 chris Manchester England

Now i want to find those employees id who do not belong to Nottingham, Manchester and Binghamton and not in sql query will be

select id from employee where city not in('Nottingham','Manchester','Binghamton');

After running this not in sql query, result will be

id
1

Always remember that first we select database before running any sql query related to that database otherwise we will get 'no database selected' error.

After looking these examples, we can easily use NOT IN sql query. Hope you like this tutorial.

Other important links

Sql IN operator

Sql NOT operator

Sql OR Operator

Sql AND Operator

Sql LIKE Operator

NOT IN Sql query searches:

Tags: #mysql