SQL NOT

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

Introduction to the SQL NOT operator

You have learned how to use various logical operators such as AND, OR, LIKE, BETWEEN, IN, and EXISTS. These operators help you to form flexible conditions in the WHERE clause.

To negate the result of any Boolean expression, you use the NOT operator. The following illustrates how to use the NOT operator:

NOT [Boolean_expression]Code language: SQL (Structured Query Language) (sql)

The following table shows the result of the NOT operator.

SQL NOT operator examples

We’ll use the employees table to help better you understand the NOT operator.

The following statement retrieves all employees who work in the department id 5.

SELECT
	employee_id,
	first_name,
	last_name,
	salary
FROM
	employees
WHERE
	department_id = 5
ORDER BY
	salary;
Code language: SQL (Structured Query Language) (sql)

Try It

To get the employees who work in the department id 5 and with a salary not greater than 5000.

SELECT
	employee_id,
	first_name,
	last_name,
	salary
FROM
	employees
WHERE
	department_id = 5
AND NOT salary > 5000
ORDER BY
	salary;Code language: SQL (Structured Query Language) (sql)

Try It

SQL NOT with IN operator example

To negate the IN operator, you use the NOT operator. For example, the following statement gets all the employees who are not working in the departments 1, 2, or 3.

SELECT
	employee_id,
	first_name,
	last_name,
	department_id
FROM
	employees
WHERE
	department_id NOT IN (1, 2, 3)
ORDER BY
	first_name;Code language: SQL (Structured Query Language) (sql)

Try It

SQL NOT LIKE operator example

You can negate the LIKE operator by using the NOT LIKE. For example, the following statement retrieves all the employees whose first names do not start with the letter D.

SELECT
	first_name,
	last_name
FROM
	employees
WHERE
	first_name NOT LIKE 'D%'
ORDER BY
	first_name;Code language: SQL (Structured Query Language) (sql)

Try It

SQL NOT BETWEEN example

The following example shows you how to use the NOT to negate the BETWEEN operator to get employees whose salaries are not between 5,000 and 1,000.

SELECT
	employee_id,
	first_name,
	last_name,
	salary
FROM
	employees
WHERE
	salary NOT BETWEEN 3000
AND 5000
ORDER BY
	salary;Code language: SQL (Structured Query Language) (sql)

Try It

SQL NOT EXISTS example

See the following employees and dependents tables:

The following query uses the NOT EXISTS operator to get the employees who do not have any dependents.

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees e
WHERE
	NOT EXISTS (
		SELECT
			employee_id
		FROM
			dependents d
		WHERE
			d.employee_id = e.employee_id
	);Code language: SQL (Structured Query Language) (sql)

Try It

Now you should know how to use the NOT operator to negate a Boolean expression.