In SQL, the NOT operator negates a Boolean input (it reverses the value of any Boolean expression). Therefore it returns TRUE when the expression is FALSE.
Source Table
The following table is used for the examples on this page.
SELECT * FROM Pets;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+ (8 rows affected)
Example 1
Here’s a simple example to demonstrate the NOT operator.
SELECT * FROM Pets
WHERE PetName NOT LIKE 'F%';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
In this case, we used the NOT operator to negate the outcome of the LIKE operator. Our query returns all pets that don’t start with the letter F.
Example 2 – Using NOT with Comparison Operators
If you’re using the NOT operator to negate a comparison operator, you’ll need to modify your syntax slightly from the one used in the previous example.
For example, if you want to use it to negate the equals operator (=), use the following syntax:
SELECT * FROM Pets
WHERE NOT PetName = 'Fluffy';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
This returns all pets whose name is not Fluffy.
In this example, we put the NOT operator in front of the column name. If we hadn’t done that, we would have received an error that goes something like this:
SELECT * FROM Pets
WHERE PetName NOT = 'Fluffy';Result:
Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '='.
We could also have achieved the same outcome by using the not equal to operator (either <> or != depending on your DBMS).
For example, this:
SELECT * FROM Pets
WHERE PetName <> 'Fluffy';Or this:
SELECT * FROM Pets
WHERE PetName != 'Fluffy';Example 3 – Same Syntax with Logical Operators
As it turns out, we can also use that syntax when using logical operators, such as the LIKE operator that we used in the first example.
Therefore, we could rewrite the first example to this:
SELECT * FROM Pets
WHERE NOT PetName LIKE 'F%';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
In case you’re not sure what the difference is, we moved the NOT operator from after the PetName column, to before it.
Here are the two statements together:
SELECT * FROM Pets
WHERE PetName NOT LIKE 'F%';
SELECT * FROM Pets
WHERE NOT PetName LIKE 'F%';Example 4 – Negating Multiple Conditions
The NOT operator only negates a single condition. If you have multiple conditions that you need to negate, use a separate NOT operator for each condition,
SELECT * FROM Pets
WHERE NOT PetName = 'Fluffy'
AND NOT PetName = 'Wag';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
Example 5 – Precedence
When using compound conditions, where you have multiple operators, the NOT operator is evaluated prior to any logical operators, but after any. comparison operators,
When two operators in an expression have the same precedence level, they’re evaluated left to right based on their position in the expression. However, you can use parentheses to specify the order in which you want each condition to be evaluated.
Here’s an example.
SELECT * FROM Pets
WHERE
NOT PetName = 'Fluffy'
OR NOT PetName = 'Wag'
AND NOT DOB > '2020-01-01';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
We didn’t use any parentheses here, and all conditions were negated with the NOT operator, and so the AND operator took precedence over the OR operator.
However, we can use parentheses to specify that the OR condition should be evaluated before the AND condition.
SELECT * FROM Pets
WHERE
(NOT PetName = 'Fluffy'
OR NOT PetName = 'Wag')
AND NOT DOB > '2020-01-01';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | +---------+-------------+-----------+-----------+------------+
In this case we get a different result.
Example 6 – Negate the BETWEEN Operator
Here’s another example, this time using the NOT operator to negate the result of the BETWEEN operator.
SELECT * FROM Pets
WHERE DOB NOT BETWEEN '2018-10-01' AND '2020-09-17';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 5 | 1 | 1 | Tweet | 2020-11-28 | +---------+-------------+-----------+-----------+------------+
In this example, we returned all rows where the DOB column is not between 2018-10-01 and 2020-09-17.
In this case, two rows matched that criteria, and therefore, two rows were returned.
This is the opposite result of the following query:
SELECT * FROM Pets
WHERE DOB BETWEEN '2018-10-01' AND '2020-09-17';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | +---------+-------------+-----------+-----------+------------+
In this example, we returned all rows where the DOB column is between 2018-10-01 and 2020-09-17.
In this case, four rows matched that criteria, and therefore, four rows were returned.