SQL NOT Operator

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

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 conditionCopy

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.

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;Copy

Try it

Output:

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

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
  );Copy

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
...Copy

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.

Quiz #