MySQL OR operator

If you want to combine one or more expressions in the WHERE clause, you can use the logical operator AND, OR or NOT. In this article, we will learn the OR operator.

OR operator syntax

The OR operator is a binary logical operator, and it requires two operands. As long as one of the two operands is TRUE, it returns TRUE, otherwise it returns FALSE or NULL.

OR Operator syntax:

a OR b

Here:

• a and b are the two operands of OR. They can be expressions or values.
• If one of a and b is 1 (TRUE), OR returns 1 (TRUE), or else returns 0 (FALSE) or NULL.

Unlike some programming languages, there is no Boolean type in MySQL. The result of OR is 1, 0 or NULL..

The operating rules of the OR operator are as follows:

• If two operands at least one operand 1 (TURE), then OR returns 1.

  SELECT 1 OR 1, 1 OR 0, 1 OR NULL, 2 OR 0;
  

  +--------+--------+-----------+--------+
  | 1 OR 1 | 1 OR 0 | 1 OR NULL | 2 OR 0 |
  +--------+--------+-----------+--------+
  |      1 |      1 |         1 |      1 |
  +--------+--------+-----------+--------+

• If both of two operands are 0 (FALSE), then OR returns 0.

  SELECT 0 OR 0;
  

  +--------+
  | 0 OR 0 |
  +--------+
  |      0 |
  +--------+

• If one operand is NULL and the other is 0 (FALSE) or NULL , then OR returns NULL.

  SELECT NULL OR 0, NULL or NULL;
  

  +-----------+--------------+
  | NULL OR 0 | NULL or NULL |
  +-----------+--------------+
  |      NULL |         NULL |
  +-----------+--------------+
  

• The order of the operands does not affect the result of the OR operator.

  SELECT 1 OR 0, 0 OR 1, 1 OR NULL, NULL OR 1, 0 OR NULL, NULL OR 0;
  

  +--------+--------+-----------+-----------+-----------+-----------+
  | 1 OR 0 | 0 OR 1 | 1 OR NULL | NULL OR 1 | 0 OR NULL | NULL OR 0 |
  +--------+--------+-----------+-----------+-----------+-----------+
  |      1 |      1 |         1 |         1 |      NULL |      NULL |
  +--------+--------+-----------+-----------+-----------+-----------+

The following table lists the results of the OR operator with different operands:

OR operator example

In the following example, we will use the actor table from the Sakila sample database for the demonstration.

The following query uses the WHERE clause to find the last name ALLEN or DAVIS of all the actors:

The following SQL statement returns all actors whose last names are DAVIS or SUSAN:

SELECT *
FROM actor
WHERE last_name = 'ALLEN'
  OR last_name = 'DAVIS';

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      118 | CUBA       | ALLEN     | 2006-02-15 04:34:33 |
|      145 | KIM        | ALLEN     | 2006-02-15 04:34:33 |
|      194 | MERYL      | ALLEN     | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS     | 2006-02-15 04:34:33 |
|      101 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
|      110 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

Conclusion

In this article, we learned the syntax and operation rules of the OR operator. The main points of the OR operator are as follows:

• The OR operator is a binary operator and requires two Boolean operands.
• If one of two operands is 1 (TURE), then OR returns 1.
• If both of two operands are 0 (FALSE), then OR returns 0.
• If one operand is NULL and the other is 0 (FALSE) or NULL , then OR returns NULL.
• The order of the operands does not affect the result of the OR operator.
• The AND operator has higher precedence than the OR operator.