Example HTML page

MySQL NOT operator

MySQL NOT operator

We have already discussed that there are AND, OR and NOT are the three logical operators in MySQL. In the earlier posts we learnt how AND and OR operators. In this post we will see how the NOT operator works in MySQL.

NOT is one of the logical operators which reverses or negates the given input.

The NOT operator is denoted with “!” symbol.

Let us see the NOT operator under different cases with examples.

a.When an input operand is 0 then NOT operator negates ‘0’ and returns ‘1’.

Ex

mysql> select ! 0;
+-----+
| ! 0 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

b. When an input operand is 1(or any other value) then NOT operator negates ‘1’ and returns ‘0’.

Ex

mysql> select ! 1;
+-----+
| ! 1 |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)
mysql> select ! 26;
+------+
| ! 26 |
+------+
|    0 |
+------+
1 row in set (0.00 sec)


c.When NULL is given as an input then NOT operator negates it and returns NULL as output.

Ex:

mysql> select ! NULL;
+--------+
| ! NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

d. When the two or more operands are provided as an input then NOT operator negates those and returns 0 as output.

Ex

mysql> select ! (6+2);
+---------+
| ! (6+2) |
+---------+
|       0 |
+---------+
1 row in set (0.03 sec)

mysql> select ! (8-3);
+---------+
| ! (8-3) |
+---------+
|       0 |
+---------+
1 row in set (0.04 sec)

mysql> select ! (4*7);
+---------+
| ! (4*7) |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> select ! (1/0);
+---------+
| ! (1/0) |
+---------+
|    NULL |
+---------+
1 row in set (0.03 sec)
mysql> select ! (1+2-3);
+-----------+
| ! (1+2-3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

Note: In the above example, for the statement 1(1+2-3) it results 0 as output. Hence NOT operator negates that “0” and returns “1” as output.

Till now we discussed about NOT operator on some operands under simple statements. Now we will see how it works on databases.

The NOT operator is often used in the WHERE clause of the SELECT, UPDATE, DELETE statement to form Boolean expressions.

The syntax of NOT operator is as follows:

SELECT column1, column2, … FROM table_name WHERE NOT condition;

For demonstration I am choosing my sample database and is shown in below:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| captcha            |
| mysql              |
| performance_schema |
| sampledb           |
| sports             |
| test               |
+--------------------+
7 rows in set (0.14 sec)

mysql> use sports;
Database changed
mysql> show tables;
+------------------+
| Tables_in_sports |
+------------------+
| players          |
| rankings         |
| rankings_1       |
+------------------+
3 rows in set (0.08 sec)

mysql> select * from rankings_1;
+---------+------+-----------+
| name    | rank | country   |
+---------+------+-----------+
| Nadal   |    8 | Spain     |
| L Messi |   11 | Barcelona |
| C Marin |    3 | Spain     |
| Dhoni   |    1 | India     |
| Sachin  |    2 | India     |
| T Woods |   13 | Sweden    |
+---------+------+-----------+
6 rows in set (0.13 sec)

Now,

mysql> select * from rankings_1 where NOT country="Spain";
+---------+------+-----------+
| name    | rank | country   |
+---------+------+-----------+
| L Messi |   11 | Barcelona |
| Dhoni   |    1 | India     |
| Sachin  |    2 | India     |
| T Woods |   13 | Sweden    |
+---------+------+-----------+
4 rows in set (0.03 sec)

In the above statement, we gave the condition as Country=”Spain”. This NOT operator negates the statement and returns the output of the columns excluding the columns where the country name as Spain.

Similarly,

mysql> select * from rankings_1 where NOT country="India";
+---------+------+-----------+
| name    | rank | country   |
+---------+------+-----------+
| Nadal   |    8 | Spain     |
| L Messi |   11 | Barcelona |
| C Marin |    3 | Spain     |
| T Woods |   13 | Sweden    |
+---------+------+-----------+
4 rows in set (0.00 sec)

Combining AND,OR and NOT

We can also AND, OR and OR operators.

Ex:

mysql> select * from rankings_1 where country="India" AND (name="Sachin" OR coun
try="India");
+--------+------+---------+
| name   | rank | country |
+--------+------+---------+
| Dhoni  |    1 | India   |
| Sachin |    2 | India   |
+--------+------+---------+
2 rows in set (0.03 sec)

mysql> select * from rankings_1 where country="India" OR (name="Sachin" AND coun
try="India");
+--------+------+---------+
| name   | rank | country |
+--------+------+---------+
| Dhoni  |    1 | India   |
| Sachin |    2 | India   |
+--------+------+---------+
2 rows in set (0.00 sec)

mysql> select * from rankings_1 where country="Barcelona" OR (name="Sachin" AND
country="India");
+---------+------+-----------+
| name    | rank | country   |
+---------+------+-----------+
| L Messi |   11 | Barcelona |
| Sachin  |    2 | India     |
+---------+------+-----------+
2 rows in set (0.00 sec)

 

 

Example HTML page

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest