Summary -

In this topic, we described about the NOT Condition with detailed example.

NOT condition is used to fetch the rows that are not satisfy the specified condition in WHERE clause. NOT condition is used in the SELECT, UPDATE, DELETE statements.

Syntax -

SELECT column1, column2, …, columnN
  FROM table_name
  WHERE NOT condition; 
  • column1, column2, …, columnN – Specifies the column names that are used to fetch from table.
  • table_name – Specifies the name of the table.
  • condition – Specifies the condition that is with WHERE clause.

Example –

Let us consider the employee_details table as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetch rows that are not satisfy the specified condition.

Requirement1 – Fetch employee details whose dept_id is not 1000. The query was as follows –

SELECT * FROM employee_details WHERE NOT dept_id = 1000; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
002 Employee2 Director 2019-07-11 40000.00 2000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 15000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 15000.00 2000

Requirement2 – Fetch employees details whose salary not 15000 or 25000. The query was as follows –

SELECT * FROM employee_details WHERE  NOT salary IN (15000, 25000); 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement3 – Fetch all details of employees whose emp_id is in between 004 and 007. The query was as follows –

SELECT * FROM employee_details WHERE  emp_id NOT BETWEEN 004 AND 007; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement4 – Fetch all details of employees whose designation is not start with "man". The query was as follows –

SELECT * FROM employee_details WHERE designation NOT LIKE "man%"; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement5 – Fetch all details of employees whose manager_id is not NULL. The query was as follows –

SELECT * FROM employee_details WHERE  manager_id IS NOT NULL; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Updating the rows by using NOT condition.

Requirement – Update the employee salary as 20000 whose designations are not "Director" or 'Manager". The query was as follows –

UPDATE employee_details 
SET salary = 17000
WHERE designation NOT IN ("Director", "Manager"); 

By executing above query, we can update salary with 20000. The updated table is as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 20000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 20000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 20000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 20000.00 2000

Scenario – Delete the rows using NOT condition.

Requirement - Delete employee details whose joining date not in "2019-07-11", "2019-10-08". The query was as follows –

  DELETE FROM employee_details WHERE date_of_hire NOT IN 
("2019-07-11", "2019-10-08"); 

By executing above query, we can delete the satisfied rows from employee_details table. The updated table is as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000