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.

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;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | salary
------------+---------
 Shelli     | 2900.00
 Sigal      | 2800.00
 Guy        | 2600.00
 Karen      | 2500.00
 Irene      | 2700.00Code language: SQL (Structured Query Language) (sql)

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
  );Code language: SQL (Structured Query Language) (sql)

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
...Code language: SQL (Structured Query Language) (sql)

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 #