SQL NOT

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

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 reverse the result of any Boolean expression, you use the NOT operator. The following illustrates how to use the NOT operator.

NOT [Boolean_expression]

The following table shows the result of the NOT operator.

	NOT
TRUE	FALSE
FALSE	TRUE
NULL	NULL

SQL NOT operator examples

We are going to use the employees table for the demonstration of 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;

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;

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

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;

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;

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

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