SQL NOT Operator

Summary: in this tutorial, you will learn how to use the SQL NOT operator to negate a Boolean expression.

Introduction to the SQL NOT operator 

The NOT operator allows you to negate a condition. It returns the opposite of the condition’s result:

NOT conditionCode language: SQL (Structured Query Language) (sql)

If the condition is true, the NOT operator makes it false and vice versa. However, if the condition is NULL, the NOT operator returns NULL.

The following table shows the results of the NOT operator.

condition	NOT condition
true	false
false	true
NULL	NULL

In practice, you’ll find the NOT operator helpful in the following cases:

• To filter rows that match a specific condition.
• To combine with other logical operators (AND and OR) to form complex queries.

SQL NOT operator examples 

We’ll use the employees table from the sample database for the following examples.

Negating a condition 

The following query uses the NOT operator to retrieve employees who do not have a salary greater than or equal 3000:

SELECT
  first_name,
  salary
FROM
  employees
WHERE
  NOT salary >= 3000;

Try it

Output:

 first_name | salary
------------+---------
 Shelli     | 2900.00
 Sigal      | 2800.00
 Guy        | 2600.00
 Karen      | 2500.00
 Irene      | 2700.00

In this example:

• The condition salary >= 3000 filter employees who have salaries greater than or equal to 3000.
• The NOT operator fetch employee earning 3000 or less.

Using the NOT operator with other logical operators 

The following query finds the employees who are not in IT (10) and HR (20) departments:

SELECT
  first_name,
  last_name,
  department_id
FROM
  employees
WHERE
  NOT (
    department_id = 10
    OR department_id = 20
  );

Try it

Output:

 first_name |  last_name  | department_id
------------+-------------+---------------
 Steven     | King        |             9
 Neena      | Kochhar     |             9
 Lex        | De Haan     |             9
 Alexander  | Hunold      |             6
 Bruce      | Ernst       |             6
 David      | Austin      |             6

In this example:

• The condition department_id = 10 OR department_id = 20 retrieves employees in the department id 10 (IT) and 20 (HR).
• The NOT operator excludes them.

Summary 

• Use the NOT operator to negate a Boolean expression.