MySQL OR operator

Last update on May 25 2024 09:34:57 (UTC/GMT +8 hours)

OR operator

MySQL OR operator compares two expressions and returns TRUE if either of the expressions is TRUE.

This function is useful in -

It allows you to combine multiple conditions in a query, specifying that at least one of the conditions must be true for a record to be included in the result set.
OR makes queries more explicit. It clearly states that you want records that meet one condition or another, which can help prevent misunderstandings or misinterpretations of your query.
When you want to filter records based on multiple criteria, OR is crucial.
By using OR judiciously, you can optimize your queries to efficiently retrieve the desired data.
OR provides a way to logically combine conditions, allowing you to express complex logic in your queries.
In some scenarios, OR can be used to conditionally insert or update records based on the presence of specific conditions.
When you have complex conditions involving multiple logical operators like AND and OR, OR can be crucial for achieving the desired outcome.

Syntax:

OR, ||

When more than one logical operator is used in a statement, OR operators perform after AND operator. The order of evaluation can be changed by using parentheses.

The operator returns 1 when both operands are a non-NULL and one of them is nonzero and returns 0 when both operands are non-NULL and one of them is zero and returns NULL when one operand is NULL and other is zero and return 1 also when one is NULL and another operand is nonzero and NULL also when both operands and NULL.

MySQL Version: 8.0

Example: MySQL OR operator

The following MySQL statement satisfies the condition - "both operands are a non-NULL and one of them is nonzero", so it returns 1.

Code:


-- This SQL statement performs a logical OR operation on the number 5
-- Explanation: The query evaluates a logical OR operation between the number 5 and itself, returning the result of the operation.
SELECT 5 || 5;  -- The SELECT statement is used to evaluate the expression '5 || 5' and return the result

Explanation:

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

The || operator is a logical OR operator in MySQL.

In the context of the expression 5 || 5:

Any non-zero number is considered as TRUE.

The logical OR operation returns TRUE if at least one of the operands is TRUE.

Since both operands (5 and 5) are non-zero numbers, they are both considered TRUE.

TRUE || TRUE evaluates to TRUE, which is represented as 1 in MySQL.

Therefore, the result of SELECT 5 || 5 is 1.

Output:

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

Example of MySQL OR operator with at least one (zero) 0

The following MySQL statement satisfies the condition - "both operands are non-NULL and one of them is zero", so it returns 1.

Code:


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

Explanation:

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

The || operator is a logical OR operator in MySQL.

In the context of the expression 5 || 0:

Any non-zero number is considered as TRUE.

Zero (0) is considered as FALSE.

The logical OR operation returns TRUE if at least one of the operands is TRUE.

In this case, 5 is TRUE (non-zero), and 0 is FALSE.

TRUE || FALSE evaluates to TRUE, which is represented as 1 in MySQL.

Therefore, the result of SELECT 5 || 0 is 1.

Output:

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

Example of MySQL OR operator when both operands are zero(0)

The following MySQL statement both of the operands are 0, so it returns 0.

Code:


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

Explanation:

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

The || operator is a logical OR operator in MySQL.

In the context of the expression 0 || 0:

Zero (0) is considered as FALSE.

The logical OR operation returns TRUE if at least one of the operands is TRUE.

Since both operands (0 and 0) are FALSE, the result is FALSE.

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

Therefore, the result of SELECT 0 || 0 is 0.

Output:

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

Example of MySQL OR operator with at least one NULL operand

The following MySQL statement satisfies the condition - "one operand is NULL and other is zero", so it returns NULL.


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

Explanation:

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

The || operator is a logical OR operator in MySQL.

In the context of the expression 0 || NULL:

Zero (0) is considered as FALSE.

NULL represents an unknown or undefined value in SQL.

The logical OR operation returns TRUE if at least one of the operands is TRUE.

If any operand is TRUE, the result of the logical operation is TRUE.

If both operands are FALSE, the result is FALSE.

Since 0 is FALSE, and NULL is an undefined value, the result of the operation is NULL because the truth value is unknown.

Therefore, the result of SELECT 0 || NULL is NULL.

Output:

MySQL> SELECT 0 || NULL;
+-----------+
| 0 || NULL |
+-----------+
|      NULL | 
+-----------+
1 row in set (0.01 sec)

Example of MySQL OR operator with NULL and non-zero operand

The following MySQL statement satisfies the condition - "one operand is NULL and other is non-zero", so it returns 1.

Code:


-- This SQL statement performs a logical OR operation between the number 5 and NULL
-- Explanation: The query evaluates a logical OR operation between the number 5 and NULL, returning the result of the operation.
SELECT 5 || NULL;  -- The SELECT statement is used to evaluate the expression '5 || NULL' and return the result

Explanation:

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

The || operator is a logical OR operator in MySQL.

In the context of the expression 5 || NULL:

Any non-zero number is considered as TRUE.

NULL represents an unknown or undefined value in SQL.

The logical OR operation returns TRUE if at least one of the operands is TRUE.

If any operand is TRUE, the result of the logical operation is TRUE.

If both operands are FALSE, the result is FALSE.

Since 5 is TRUE (non-zero), and NULL is an undefined value, the result of the operation is TRUE because one operand is TRUE.

Therefore, the result of SELECT 5 || NULL is 1.

Output:

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

Example of MySQL OR operator with both NULL operands

The following MySQL statement both of the operands are NULL, so it returns NULL.

Code:


-- This SQL statement performs a logical OR operation between two NULL values
-- Explanation: The query evaluates a logical OR operation between NULL and NULL, returning the result of the operation.
SELECT NULL || NULL;  -- The SELECT statement is used to evaluate the expression 'NULL || NULL' and return the result

Explanation:

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

The || operator is a logical OR operator in MySQL.

In the context of the expression NULL || NULL:

NULL represents an unknown or undefined value in SQL.

The logical OR operation returns TRUE if at least one of the operands is TRUE.

If any operand is TRUE, the result of the logical operation is TRUE.

If both operands are FALSE, the result is FALSE.

Since both operands (NULL and NULL) are undefined values, the result of the operation is NULL because the truth value is unknown.

Therefore, the result of SELECT NULL || NULL is NULL.

Output:

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

Previous: NOT operator
Next: XOR operator