Example HTML page

MySQL OR operator

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

The OR operator combines two Boolean expressions and returns to TRUE if any one of the two statements evaluates to TRUE. It is represented with “| |” symbol.

But what if there is more than one logical operator (i.e., combination of AND and OR) exists in a statement…At that time OR operator perform after the evaluation of AND operator. We use parentheses to change the evaluation order.

Let us discuss about OR operator on simple statements.

a. OR operator returns 1 when one operand is non-NULL and the other operand is Non zero.

Ex:

mysql> select 3 || 5;
+--------+
| 3 || 5 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

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

b. OR operator returns 1 when an operand is Non-NULL and the other operand is Zero.

Ex.

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

c. OR operator returns zero when both the operands are zero.

Ex:

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

d.OR operator returns “NULL” when one operand NULL and the other operand is zero.

Ex

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

e. OR operator returns “1” when one operand is NULL and the other operand is non-zero.

Ex

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

f. OR operator returns “NULL” when both the operands are “NULL”.

Ex:

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

In MySQL, Notice that 0 is FALSE and other than 0 are TRUE.

Till now we have seen how OR operator in MySQL works on simple statements. Let us discuss how OR operator in MySQL works with databases…

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

The syntax of OR operator is as follows:

SELECT column1, column2, … FROM table_name WHERE condition1 OR condition2 OR condition3 …;

I am taking my sample database for the demonstration on OR operator.

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

mysql> use sports;
Database changed
mysql> show tables;
+------------------+
| Tables_in_sports |
+------------------+
| players          |
| rankings         |
| rankings_1       |
+------------------+
3 rows in set (0.05 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.03 sec)

Now,

mysql> select * from rankings_1 where rank=1 OR Country="Sweden";
+---------+------+---------+
| name    | rank | country |
+---------+------+---------+
| Dhoni   |    1 | India   |
| T Woods |   13 | Sweden  |
+---------+------+---------+
2 rows in set (0.19 sec)
mysql> select * from rankings_1 where rank=8 OR Country="NULL";
+-------+------+---------+
| name  | rank | country |
+-------+------+---------+
| Nadal |    8 | Spain   |
+-------+------+---------+
1 row in set (0.06 sec)

In the above example we are combining only two expressions. But we can also combine more than two expressions. For example,

mysql> select * from rankings_1 where rank=8 OR Country="NULL" AND name="T Woods
";
+-------+------+---------+
| name  | rank | country |
+-------+------+---------+
| Nadal |    8 | Spain   |
+-------+------+---------+
1 row in set (0.00 sec)

mysql> select * from rankings_1 where rank=8 AND Country="NULL" OR name="T Woods
";
+---------+------+---------+
| name    | rank | country |
+---------+------+---------+
| T Woods |   13 | Sweden  |
+---------+------+---------+
1 row in set (0.00 sec)

mysql> select * from rankings_1 where rank=8 OR Country="NULL" OR name="T Woods"
;
+---------+------+---------+
| name    | rank | country |
+---------+------+---------+
| Nadal   |    8 | Spain   |
| T Woods |   13 | Sweden  |
+---------+------+---------+
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