SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL what-are-single-row-and-multiple-row-subqueries">What are single row and multiple row subqueries?

SQL NOT Operator

SQL NOT is a Boolean operator used with the WHERE clause. NOT operator shows the records if the expression is false. When we use the NOT operator, we fetch only those data from the Table that do not meet the criteria in the given expression.

We can use the NOT operator with the SELECT statement, the UPDATE statement, and the DELETE statement. We can use NOT operator with other operators like AND operator, OR operator, BETWEEN operators, IN operator, LIKE operator, and other operators.

Syntax of NOT operator:

SELECT Column_name1, Column_name2, Column_name3 FROM Table_name WHERE Column_name NOT expression;

The Table shows the result of the NOT operator:

 

NOT

True

False

False

True

Null

Null

Let’s understand the SQL NOT operator concept with the help of examples.

Consider the existing tables, which have the following records:

Table 1: Employees

EMPLOYEEID

FIRST_NAME

LAST_NAME

SALARY

CITY

DEPARTMENT

MANAGERID

1001

VAIBHAVI

MISHRA

65500

PUNE

ORACLE

1

1002

VAIBHAV

SHARMA

60000

NOIDA

C#

5

1003

NIKHIL

VANI

50500

JAIPUR

FMW

2

2001

PRACHI

SHARMA

55500

CHANDIGARH

ORACLE

1

2002

BHAVESH

JAIN

65500

PUNE

FMW

2

2003

RUCHIKA

JAIN

50000

MUMBAI

C#

5

3001

PRANOTI

SHENDE

55500

PUNE

JAVA

3

3002

ANUJA

WANRE

50500

JAIPUR

FMW

2

3003

DEEPAM

JAUHARI

58500

MUMBAI

JAVA

3

4001

RAJESH

GOUD

60500

MUMBAI

TESTING

4

4002

ASHWINI

BAGHAT

54500

NOIDA

JAVA

3

4003

RUCHIKA

AGARWAL

60000

DELHI

ORACLE

1

5001

ARCHIT

SHARMA

55500

DELHI

TESTING

4

5002

SANKET

CHAUHAN

70000

HYDERABAD

JAVA

3

5003

ROSHAN

NEHTE

48500

CHANDIGARH

C#

5

6001

RAHUL

NIKAM

54500

BANGALORE

TESTING

4

6002

ATISH

JADHAV

60500

BANGALORE

C#

5

6003

NIKITA

INGALE

65000

HYDERABAD

ORACLE

1

Table 2: Manager

managerid

manager_name

manager_department

1

Snehdeep Kaur

ORACLE

2

Kirti Kirtane

FMW

3

Abhishek Manish

JAVA

4

Anupam Mishra

TESTING

5

Akash Kadam

C#

Example 1: Write a query to display the employees' details where the city is NOT Pune.

SELECT * FROM EMPLOYEES WHERE NOT CITY = 'PUNE';

We display employees' details where the city should not be 'Pune'. We used the NOT operator followed by the city column in the WHERE clause. The NOT operator means only those employee details to be displayed whose city is not Pune.

The output of the above query:

EMPLOYEEID

FIRST_NAME

LAST_NAME

SALARY

CITY

DEPARTMENT

MANAGERID

1002

VAIBHAV

SHARMA

60000

NOIDA

C#

5

1003

NIKHIL

VANI

50500

JAIPUR

FMW

2

2001

PRACHI

SHARMA

55500

CHANDIGARH

ORACLE

1

2003

RUCHIKA

JAIN

50000

MUMBAI

C#

5

3002

ANUJA

WANRE

50500

JAIPUR

FMW

2

3003

DEEPAM

JAUHARI

58500

MUMBAI

JAVA

3

4001

RAJESH

GOUD

60500

MUMBAI

TESTING

4

4002

ASHWINI

BAGHAT

54500

NOIDA

JAVA

3

4003

RUCHIKA

AGARWAL

60000

DELHI

ORACLE

1

5001

ARCHIT

SHARMA

55500

DELHI

TESTING

4

5002

SANKET

CHAUHAN

70000

HYDERABAD

JAVA

3

5003

ROSHAN

NEHTE

48500

CHANDIGARH

C#

5

6001

RAHUL

NIKAM

54500

BANGALORE

TESTING

4

6002

ATISH

JADHAV

60500

BANGALORE

C#

5

6003

NIKITA

INGALE

65000

HYDERABAD

ORACLE

1

SQL NOT OPERATOR

As we can see, only those records are displayed where the city is not Pune.

Example 2: Write a query to display the employees’ details where the Department is NOT C#.

SELECT * FROM EMPLOYEES WHERE NOT DEPARTMENT = 'C#';

From the above query, we display employees' details where Department should not be 'C#'. Only those employee details to be displayed whose Department is not C#. We used the NOT operator followed by the city column in the WHERE clause.

The output of the above query:

EMPLOYEEID

FIRST_NAME

LAST_NAME

SALARY

CITY

DEPARTMENT

MANAGERID

1001

VAIBHAVI

MISHRA

65500

PUNE

ORACLE

1

1003

NIKHIL

VANI

50500

JAIPUR

FMW

2

2001

PRACHI

SHARMA

55500

CHANDIGARH

ORACLE

1

2002

BHAVESH

JAIN

65500

PUNE

FMW

2

3001

PRANOTI

SHENDE

55500

PUNE

JAVA

3

3002

ANUJA

WANRE

50500

JAIPUR

FMW

2

3003

DEEPAM

JAUHARI

58500

MUMBAI

JAVA

3

4001

RAJESH

GOUD

60500

MUMBAI

TESTING

4

4002

ASHWINI

BAGHAT

54500

NOIDA

JAVA

3

4003

RUCHIKA

AGARWAL

60000

DELHI

ORACLE

1

5001

ARCHIT

SHARMA

55500

DELHI

TESTING

4

5002

SANKET

CHAUHAN

70000

HYDERABAD

JAVA

3

6001

RAHUL

NIKAM

54500

BANGALORE

TESTING

4

6003

NIKITA

INGALE

65000

HYDERABAD

ORACLE

1

SQL NOT OPERATOR

As we can see, only those records are displayed where Department is not C#.

Example 3: Write a query to display employee details where the city is not ‘Mumbai’, 'Pune', and ‘Delhi’.

SELECT * FROM EMPLOYEES WHERE CITY NOT IN ('PUNE', 'MUMBAI', 'DELHI'); 

From the above query, we display the employees' details where city is not 'Pune', 'Mumbai', and 'Delhi'. We have used IN operator with the NOT operator. NOT IN operator allow only those records whose values are not present in the IN operator parameter.

The output of the above query:

EMPLOYEEID

FIRST_NAME

LAST_NAME

SALARY

CITY

DEPARTMENT

MANAGERID

1002

VAIBHAV

SHARMA

60000

NOIDA

C#

5

1003

NIKHIL

VANI

50500

JAIPUR

FMW

2

2001

PRACHI

SHARMA

55500

CHANDIGARH

ORACLE

1

3002

ANUJA

WANRE

50500

JAIPUR

FMW

2

4002

ASHWINI

BAGHAT

54500

NOIDA

JAVA

3

5002

SANKET

CHAUHAN

70000

HYDERABAD

JAVA

3

5003

ROSHAN

NEHTE

48500

CHANDIGARH

C#

5

6001

RAHUL

NIKAM

54500

BANGALORE

TESTING

4

6002

ATISH

JADHAV

60500

BANGALORE

C#

5

6003

NIKITA

INGALE

65000

HYDERABAD

ORACLE

1

SQL NOT OPERATOR

As we can see, only those records are displayed where city is not 'Pune', 'Mumbai', and 'Delhi.'

Example 4: Write a query to display employee details where the city is not ‘Mumbai’ or Department is not ‘Oracle’.

SELECT * FROM EMPLOYEES WHERE NOT CITY = 'MUMBAI' AND NOT DEPARTMENT = 'ORACLE';

From the above query, we have displayed the employees' details where the city is not 'Mumbai' or Department is not 'Oracle'. The above query excludes all those records whose city is 'Mumbai' and Department is 'Oracle’.

The output of the above query:

EMPLOYEEID

FIRST_NAME

LAST_NAME

SALARY

CITY

DEPARTMENT

MANAGERID

1002

VAIBHAV

SHARMA

60000

NOIDA

C#

5

1003

NIKHIL

VANI

50500

JAIPUR

FMW

2

2002

BHAVESH

JAIN

65500

PUNE

FMW

2

3001

PRANOTI

SHENDE

55500

PUNE

JAVA

3

3002

ANUJA

WANRE

50500

JAIPUR

FMW

2

4002

ASHWINI

BAGHAT

54500

NOIDA

JAVA

3

5001

ARCHIT

SHARMA

55500

DELHI

TESTING

4

5002

SANKET

CHAUHAN

70000

HYDERABAD

JAVA

3

5003

ROSHAN

NEHTE

48500

CHANDIGARH

C#

5

6001

RAHUL

NIKAM

54500

BANGALORE

TESTING

4

6002

ATISH

JADHAV

60500

BANGALORE

C#

5

SQL NOT OPERATOR

As we can see, only those records are displayed where the city is not 'Mumbai' and Department is not 'Oracle'.

Example 5: Write a query to display employee details where the salary is not greater than 60000.

SELECT * FROM EMPLOYEES WHERE NOT SALARY > 60000;

From the above query, we displayed the employees' details where salary is not greater than 60000. Here, we didn't use less than the operator. We used NOT operator with greater than the operator.

The output of the above query:

EMPLOYEEID

FIRST_NAME

LAST_NAME

SALARY

CITY

DEPARTMENT

MANAGERID

1002

VAIBHAV

SHARMA

60000

NOIDA

C#

5

1003

NIKHIL

VANI

50500

JAIPUR

FMW

2

2001

PRACHI

SHARMA

55500

CHANDIGARH

ORACLE

1

2003

RUCHIKA

JAIN

50000

MUMBAI

C#

5

3001

PRANOTI

SHENDE

55500

PUNE

JAVA

3

3002

ANUJA

WANRE

50500

JAIPUR

FMW

2

3003

DEEPAM

JAUHARI

58500

MUMBAI

JAVA

3

4002

ASHWINI

BAGHAT

54500

NOIDA

JAVA

3

4003

RUCHIKA

AGARWAL

60000

DELHI

ORACLE

1

5001

ARCHIT

SHARMA

55500

DELHI

TESTING

4

5003

ROSHAN

NEHTE

48500

CHANDIGARH

C#

5

6001

RAHUL

NIKAM

54500

BANGALORE

TESTING

4

SQL NOT OPERATOR

As we can see, only those records are displayed where the salary is not greater than 60000.

Example 6: Write a query to display employees' details where the city is not Bangalore and Department is not FMW or Testing.

SELECT * FROM EMPLOYEES WHERE NOT CITY = 'BANGALORE' AND NOT (DEPARTMENT = 'FMW' OR DEPARTMENT = 'TESTING');

From the above query, we displayed the employees' details where the city is not Bangalore, and neither Department is FMW or Testing. Explanation of the above query how actually query works, first the bracket part will get executed i.e.

Step1: (DEPARTMENT = ‘FMW’ OR DEPARTMENT = ‘TESTING’); the output of this query part is that only employee's details whose Department are either FMW or Testing are selected.

Step 2: WHERE NOT CITY = ‘BANGALORE’AND NOT (output of the query); the output of this query part will terminate all those employees' details where the city is Bangalore and output of step 1.

Step 3: SELECT * FROM EMPLOYEES; the output of this final query is the records that are left from excluding the records from the above query part.

The output of the above query:

EMPLOYEEID

FIRST_NAME

LAST_NAME

SALARY

CITY

DEPARTMENT

MANAGERID

1001

VAIBHAVI

MISHRA

65500

PUNE

ORACLE

1

1002

VAIBHAV

SHARMA

60000

NOIDA

C#

5

2001

PRACHI

SHARMA

55500

CHANDIGARH

ORACLE

1

2003

RUCHIKA

JAIN

50000

MUMBAI

C#

5

3001

PRANOTI

SHENDE

55500

PUNE

JAVA

3

3003

DEEPAM

JAUHARI

58500

MUMBAI

JAVA

3

4002

ASHWINI

BAGHAT

54500

NOIDA

JAVA

3

4003

RUCHIKA

AGARWAL

60000

DELHI

ORACLE

1

5002

SANKET

CHAUHAN

70000

HYDERABAD

JAVA

3

5003

ROSHAN

NEHTE

48500

CHANDIGARH

C#

5

6003

NIKITA

INGALE

65000

HYDERABAD

ORACLE

1

SQL NOT OPERATOR

As we can see from the output, only those records are displayed where the city is not 'Bangalore' and Department is not 'FMW', or Department is not 'Testing'.

Example 7: Write a subquery to display employees' details where the manager id is not 2 from the manager table.

SELECT * FROM EMPLOYEES WHERE NOT MANAGERID = (SELECT MANAGERID FROM MANAGER WHERE MANAGERID = 2); 

We display the employees' details from the above query where the manager id is not 2. We have used a subquery in the above query.

Explanation of the above query how the actual query works:

Step 1: (SELECT MANAGERID FROM MANAGER WHERE MANAGERID = 2);

First, the inner query will get executed. The output of this query is only manager details selected where manager id = 2. The output of the query:

SQL NOT OPERATOR

Step 2: SELECT * FROM EMPLOYEES WHERE NOT MANAGERID = 2;

2 is the output of the inner query, which will now use as input for the main query to display the final output of the entire query. The output of the above query will be those employees' details where the manager id is not 2 in the employees' Table.

The output of the above query:

EMPLOYEEID

FIRST_NAME

LAST_NAME

SALARY

CITY

DEPARTMENT

MANAGERID

1001

VAIBHAVI

MISHRA

65500

PUNE

ORACLE

1

1002

VAIBHAV

SHARMA

60000

NOIDA

C#

5

2001

PRACHI

SHARMA

55500

CHANDIGARH

ORACLE

1

2003

RUCHIKA

JAIN

50000

MUMBAI

C#

5

3001

PRANOTI

SHENDE

55500

PUNE

JAVA

3

3003

DEEPAM

JAUHARI

58500

MUMBAI

JAVA

3

4001

RAJESH

GOUD

60500

MUMBAI

TESTING

4

4002

ASHWINI

BAGHAT

54500

NOIDA

JAVA

3

4003

RUCHIKA

AGARWAL

60000

DELHI

ORACLE

1

5001

ARCHIT

SHARMA

55500

DELHI

TESTING

4

5002

SANKET

CHAUHAN

70000

HYDERABAD

JAVA

3

5003

ROSHAN

NEHTE

48500

CHANDIGARH

C#

5

6001

RAHUL

NIKAM

54500

BANGALORE

TESTING

4

6002

ATISH

JADHAV

60500

BANGALORE

C#

5

6003

NIKITA

INGALE

65000

HYDERABAD

ORACLE

1

SQL NOT OPERATOR

As we can see in the output, only those records are displayed where the manager id is not 2.

Example 8: Write a query to display employee id, first name and last name, the city from the employees' Table, and manager id from the manager table where the city is not 'Hyderabad'.

SELECT E.EMPLOYEEID, CONCAT (E.FIRST_NAME, CONCAT (" ", E.LAST_NAME)) AS NAME, E.CITY, M.MANAGERID FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID = M.MANAGERID WHERE NOT E.CITY = 'HYDERABAD';           

We join two tables from the above query and display the employees' details where the city is not 'Hyderabad'. We also did concatenation for First Name and Last Name as Name. And records are displayed in ascending order.

The output of the above query:

EMPLOYEEID

NAME

CITY

MANAGERID

1001

VAIBHAVI MISHRA

PUNE

1

2001

PRACHI SHARMA

CHANDIGARH

1

4003

RUCHIKA AGARWAL

DELHI

1

1003

NIKHIL VANI

JAIPUR

2

2002

BHAVESH JAIN

PUNE

2

3002

ANUJA WANRE

JAIPUR

2

3001

PRANOTI SHENDE

PUNE

3

3003

DEEPAM JAUHARI

MUMBAI

3

4002

ASHWINI BAGHAT

NOIDA

3

4001

RAJESH GOUD

MUMBAI

4

5001

ARCHIT SHARMA

DELHI

4

6001

RAHUL NIKAM

BANGALORE

4

1002

VAIBHAV SHARMA

NOIDA

5

2003

RUCHIKA JAIN

MUMBAI

5

3003

ROSHAN NEHTE

CHANDIGARH

5

6002

ATISH JADHAV

BANGALORE

5

SQL NOT OPERATOR

 As we can see in the output, only those records are displayed where the city is not 'Hyderabad'.

Example 9: Write a query to display employees’ details where salary is not between 50000 and 60000.

SELECT * FROM EMPLOYEES WHERE SALARY IS NOT BETWEEN 50000 AND 60000;

The above query displays employees' details where employee salary is not between 50000 and 60000. We used BETWEEN operator with NOT operator to select salary between 50000 and 60000.

The output of the above query:

EMPLOYEEID

FIRST_NAME

LAST_NAME

SALARY

CITY

DEPARTMENT

MANAGERID

1001

VAIBHAVI

MISHRA

65500

PUNE

ORACLE

1

2002

BHAVESH

JAIN

65500

PUNE

FMW

2

4001

RAJESH

GOUD

60500

MUMBAI

TESTING

4

5002

SANKET

CHAUHAN

70000

HYDERABAD

JAVA

3

5003

ROSHAN

NEHTE

48500

CHANDIGARH

C#

5

6002

ATISH

JADHAV

60500

BANGALORE

C#

5

6003

NIKITA

INGALE

65000

HYDERABAD

ORACLE

1

SQL NOT OPERATOR

 As we can see in the output, only those records are displayed where the salary is not between 50000 and 60000.