In this tutorial, you will learn how to use the SQL {NOT operator to negate Boolean expressions in the WHERE clause of a SELECT statement.
You have learned how to use various logical operators, such as: AND，OR，LIKE，BETWEEN，IN and EXISTS . These operators can help you in WHERE clause Form flexible conditions in.
To reverse the result of any Boolean expression, use the NOT operator. The following shows how to use the NOT operator. For more tutorials, visit http://www.manongzj.com
NOT [Boolean_expression]
The following table shows the results of the NOT operator.
| Original value | After applying the Not operator |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| NULL | NULL |
2. SQL NOT operator example
We will use the employees table to demonstrate the NOT operator. The structure of the employees table is as follows-
mysql> DESC employees; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | employee_id | int(11) | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(25) | NO | | NULL | | | email | varchar(100) | NO | | NULL | | | phone_number | varchar(20) | YES | | NULL | | | hire_date | date | NO | | NULL | | | job_id | int(11) | NO | MUL | NULL | | | salary | decimal(8,2) | NO | | NULL | | | manager_id | int(11) | YES | MUL | NULL | | | department_id | int(11) | YES | MUL | NULL | | +---------------+--------------+------+-----+---------+----------------+ 10 rows in set
The following statement retrieves all employees working in department ID 5. The following query statement-
SELECT
employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
department_id = 5
ORDER BY
salary;
Execute the above example code and get the following results-
Query employees with department ID 5 and salary no more than 5000.
SELECT
employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
department_id = 5
AND NOT salary > 5000
ORDER BY
salary;
Execute the above example code and get the following results-
+-------------+------------+-----------+--------+ | employee_id | first_name | last_name | salary | +-------------+------------+-----------+--------+ | 126 | Irene | Liu | 2700 | | 193 | Britney | Zhao | 3900 | | 192 | Sarah | Yang | 4000 | +-------------+------------+-----------+--------+ 3 rows in set
SQL NOT IN operator example
To negate the IN operator, use the NOT operator. For example, the following statement will get all employees who do NOT work IN department ID 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;
Execute the above query statement and get the following results-
+-------------+------------+-----------+---------------+ | employee_id | first_name | last_name | department_id | +-------------+------------+-----------+---------------+ | 103 | Alexander | Lee | 6 | | 193 | Britney | Zhao | 5 | | 104 | Bruce | Wong | 6 | | 179 | Charles | Yang | 8 | | 109 | Daniel | Chen | 10 | | 105 | David | Liang | 6 | | 107 | Diana | Chen | 6 | | 204 | Hermann | Wu | 7 | | 126 | Irene | Liu | 5 | ...... | 100 | Steven | Lee | 9 | | 203 | Susan | Zhou | 4 | | 106 | Valli | Chen | 6 | | 206 | William | Wu | 11 | +-------------+------------+-----------+---------------+ 31 rows in set
SQL NOT LIKE operator example
You can use NOT LIKE to negate LIKE Operator. For example, the following statement retrieves all employees whose names do not begin with the letter M.
SELECT
first_name,
last_name
FROM
employees
WHERE
first_name NOT LIKE 'M%'
ORDER BY
first_name;
Execute the above query statement and get the following results-
+------------+-----------+ | first_name | last_name | +------------+-----------+ | Alexander | Lee | | Alexander | Su | | Avg | Su | | Britney | Zhao | | Bruce | Wong | | Charles | Yang | ... ... | Shelley | Wu | | Shelli | Zhang | | Sigal | Zhang | | Steven | Lee | | Susan | Zhou | | Valli | Chen | | William | Wu | +------------+-----------+ 34 rows in set
SQL NOT BETWEEN example
The following example shows how to use NOT to negate BETWEEN Operator so that the employee's salary is not between 1000 and 1000.
SELECT
employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
salary NOT BETWEEN 3000
AND 5000
ORDER BY
salary;
Execute the above query statement and get the following results-
+-------------+------------+-----------+--------+ | employee_id | first_name | last_name | salary | +-------------+------------+-----------+--------+ | 119 | Karen | Zhang | 2500 | | 118 | Guy | Zhang | 2600 | | 126 | Irene | Liu | 2700 | | 117 | Sigal | Zhang | 2800 | | 116 | Shelli | Zhang | 2900 | | 104 | Bruce | Wong | 6000 | | 202 | Pat | Zhou | 6000 | | 179 | Charles | Yang | 6200 | ... ... | 205 | Shelley | Wu | 12000 | | 201 | Michael | Zhou | 13000 | | 146 | Karen | Liu | 13500 | | 145 | John | Liu | 14000 | | 102 | Lex | Liang | 17000 | | 101 | Neena | Wong | 17000 | | 100 | Steven | Lee | 24000 | +-------------+------------+-----------+--------+ 33 rows in set
SQL NOT EXISTS example
Please refer to the following employee and family table:
The following query uses the NOT EXISTS operator to get employees without any family members.
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
);
Execute the above query statement and get the following results-
From what you have learned above, you should now know how to negate Boolean expressions using the NOT operator.