MySQL NOT operator

Last update on May 25 2024 07:53:45 (UTC/GMT +8 hours)

NOT operator

MySQL NOT operator reverses or negates the input.

This function is useful in -

If you want to retrieve all records except those that meet a specific condition, NOT is invaluable.
It allows you to negate the result of a condition.
NOT makes queries more explicit. It clearly states that you want the opposite of a certain condition, which can help prevent misunderstandings or misinterpretations of your query.
When you have complex conditions involving multiple logical operators like AND and OR, NOT can be crucial for achieving the desired outcome.
NOT can be used to invert the logical meaning of a condition.
In some scenarios, NOT can be used to conditionally insert or update records based on the absence of a specific condition.
By using NOT judiciously, you can optimize your queries to efficiently retrieve the desired data.

Syntax:

NOT, !

The operator returns 1 if the operand is 0 and returns 0 if the operand is nonzero. It returns NULL if the operand is NOT NULL.

MySQL Version: 8.0

Example: MySQL NOT operator

In the following MySQL statement, NOT operator negates the input 10, it returns 0.

Code:


-- This SQL statement performs a logical NOT operation on the number 10
-- Explanation: The query evaluates a logical NOT operation on the number 10, returning the result of the operation.
SELECT !10;  -- The SELECT statement is used to evaluate the expression '!10' and return the result

Explanation:

The SELECT statement is used to evaluate expressions and return the result.

The ! operator is a logical NOT operator in MySQL.

In the context of the expression !10:

Any non-zero number is considered as TRUE.

The logical NOT operation returns TRUE if the operand is FALSE, and FALSE if the operand is TRUE.

Since 10 is a non-zero number, it is considered TRUE.

!TRUE evaluates to FALSE, which is represented as 0 in MySQL.

Therefore, the result of SELECT !10 is 0.

Output:

MySQL> SELECT ! 0;
+------+
| ! 10 |
+------+
|    0 | 
+------+
1 row in set (0.00 sec)

Example of MySQL NOT operator with zero input

In the following MySQL statement, NOT operator negates the input 0, it returns 1.

Code:


-- This SQL statement performs a logical NOT operation on the number 0
-- Explanation: The query evaluates a logical NOT operation on the number 0, returning the result of the operation.
SELECT ! 0;  -- The SELECT statement is used to evaluate the expression '!0' and return the result

Explanation:

The SELECT statement is used to evaluate expressions and return the result.

The ! operator is a logical NOT operator in MySQL.

In the context of the expression !0:

Zero (0) is considered as FALSE.

The logical NOT operation returns TRUE if the operand is FALSE, and FALSE if the operand is TRUE.

Since 0 is FALSE, !FALSE evaluates to TRUE, which is represented as 1 in MySQL.

Therefore, the result of SELECT !0 is 1.

Example of MySQL NOT operator with zero input

In the following MySQL statement, NOT operator negates the input 0, it returns 1.

Output:

MySQL> SELECT ! 0;
+-----+
| ! 0 |
+-----+
|   1 | 
+-----+
1 row in set (0.00 sec)

Example of MySQL NOT operator with NULL

In the following MySQL statement, NOT operator negates the input NULL, it returns NULL.

Code:


-- This SQL statement performs a logical NOT operation on the value NULL
-- Explanation: The query evaluates a logical NOT operation on the value NULL, returning the result of the operation.
SELECT !NULL;  -- The SELECT statement is used to evaluate the expression '!NULL' and return the result

Explanation:

The SELECT statement is used to evaluate expressions and return the result.

The ! operator is a logical NOT operator in MySQL.

In the context of the expression !NULL:

NULL represents an unknown or undefined value in SQL.

The logical NOT operation returns TRUE if the operand is FALSE, and FALSE if the operand is TRUE.

However, if the operand is NULL, the result of the logical NOT operation is also NULL because the truth value is unknown.

Therefore, the result of SELECT !NULL is NULL.

Output:

 MySQL> SELECT ! NULL;
+--------+
| ! NULL |
+--------+
|   NULL | 
+--------+
1 row in set (0.00 sec)

Example of MySQL NOT operator with non-zero input

In the following MySQL statement, NOT operator negates the input (10+10), it returns 0.

Code:


-- This SQL statement performs a logical NOT operation on the result of the expression (10 + 10)
-- Explanation: The query first evaluates the arithmetic expression (10 + 10), then performs a logical NOT operation on the result, returning the final result of the operation.
SELECT ! (10 + 10);  -- The SELECT statement is used to evaluate the expression '!(10 + 10)' and return the result

Explanation:

The SELECT statement is used to evaluate expressions and return the result.

The expression (10 + 10) is evaluated first.

The arithmetic operation 10 + 10 results in 20.

The ! operator is a logical NOT operator in MySQL.

The result of (10 + 10) is 20.

Any non-zero number is considered as TRUE.

The logical NOT operation returns TRUE if the operand is FALSE, and FALSE if the operand is TRUE.

Since 20 is a non-zero number, it is considered TRUE.

!TRUE evaluates to FALSE, which is represented as 0 in MySQL.

Therefore, the result of SELECT ! (10 + 10) is 0.

Output:

 MySQL> SELECT ! (10+10);
+-----------+
| ! (10+10) |
+-----------+
|         0 | 
+-----------+
1 row in set (0.00 sec)

Previous: AND operator
Next: OR operator