MySQL NOT Condition

MySQL NOT condition is a logical operator that reverses the boolean result of a condition.

It can be used to exclude rows from a query result that meet a certain condition.  

Generic select result:

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

Usage of NOT condition: 

mysql> SELECT * FROM friends WHERE NOT Telephone=111333;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
6 rows in set (0.00 sec)

mysql>

The NOT condition is used to exclude the rows where the Telephone value is equal to 111333.

The query returns all rows where the Telephone value is not equal to 111333.

mysql> SELECT * FROM friends WHERE NOT Name='Anastasia' AND NOT Lastname='Gates';
+----+----------+----------+-----------+
| id | Name     | Lastname | Telephone |
+----+----------+----------+-----------+
|  1 | John     | Smith    |    111333 |
|  3 | Samantha | Fox      |    456456 |
| 10 | Larry    | TheFirst |    555888 |
| 13 | John     | Smith    |    111333 |
+----+----------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

This is a MySQL query that selects all rows from the "friends" table where the "Name" column is not equal to 'Anastasia' AND the "Lastname" column is not equal to 'Gates'.

The query uses the NOT operator to negate the condition of the WHERE clause. The first condition is "NOT Name='Anastasia'", which will select all rows where the value of the "Name" column is not equal to 'Anastasia'. The second condition is "NOT Lastname='Gates'", which will select all rows where the value of the "Lastname" column is not equal to 'Gates'.

The AND operator is used to combine these two conditions, so only rows that meet both conditions will be selected. T

Therefore, this query will select all rows from the "friends" table where the "Name" is not 'Anastasia' AND the "Lastname" is not 'Gates'. 

mysql> SELECT id, Name, Telephone FROM friends WHERE NOT Lastname='Fox';
+----+-----------+-----------+
| id | Name      | Telephone |
+----+-----------+-----------+
|  1 | John      |    111333 |
|  2 | Anastasia |    555777 |
| 10 | Larry     |    555888 |
| 11 | Bill      |    444888 |
| 12 | Bill      |    444888 |
| 13 | John      |    111333 |
| 14 | Anastasia |    555444 |
+----+-----------+-----------+
7 rows in set (0.00 sec)

mysql>

This SELECT statement will retrieve data from the "friends" table that matches the following conditions:

• The "Lastname" column does not contain the value "Fox".
• For the resulting rows, only the "id", "Name", and "Telephone" columns will be displayed.

The "NOT" keyword is used to negate the condition. So, this statement will return all rows where the "Lastname" is not equal to "Fox".

The result will include the id, name, and telephone number of all the friends whose last name is not "Fox". 

mysql> SELECT * FROM friends WHERE NOT Name='Anastasia' OR NOT Lastname='Gates';
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111333 |
|  2 | Anastasia | TheGreat |    555777 |
|  3 | Samantha  | Fox      |    456456 |
| 10 | Larry     | TheFirst |    555888 |
| 11 | Bill      | Gates    |    444888 |
| 12 | Bill      | Gates    |    444888 |
| 13 | John      | Smith    |    111333 |
| 14 | Anastasia | Some     |    555444 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

The query "SELECT * FROM friends WHERE NOT Name='Anastasia' OR NOT Lastname='Gates'" will retrieve all rows from the "friends" table where either the "Name" is not "Anastasia" OR the "Lastname" is not "Gates".

This means that the query will return all rows where either the "Name" is not "Anastasia" (even if the "Lastname" is "Gates") OR the "Lastname" is not "Gates" (even if the "Name" is "Anastasia").