{"id":5297,"date":"2014-05-23T20:32:25","date_gmt":"2014-05-24T03:32:25","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5297"},"modified":"2023-10-08T03:19:28","modified_gmt":"2023-10-08T10:19:28","slug":"mysql-or","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-or\/","title":{"rendered":"MySQL OR Operator"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL OR<\/code> operator to combine Boolean expressions.<\/p>\n\n\n\n

Introduction to the MySQL OR operator<\/h2>\n\n\n\n

The MySQL OR<\/code> operator is a logical operator that combines two Boolean<\/a> expressions.<\/p>\n\n\n

A OR B<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If both A and B are not NULL, the OR operator returns 1 (true) if either A or B is non-zero. For example:<\/p>\n\n\n
SELECT<\/span> 1<\/span> OR<\/span> 1<\/span>, 1<\/span> OR<\/span> 0<\/span>, 0<\/span> OR<\/span> 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+--------+--------+--------+\n| 1 OR 1 | 1 OR 0 | 0 OR 1 |\n+--------+--------+--------+\n|      1 |      1 |      1 |\n+--------+--------+--------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
If both A and B are zero (false), the OR operator returns zero. For example:<\/p>\n\n\n
SELECT<\/span> 0<\/span> OR<\/span> 0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+--------+\n| 0 OR 0 |\n+--------+\n|      0 |\n+--------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
When A and \/ or B is NULL, the OR operator returns 1 (true) if either A or B is non-zero. Otherwise, it returns NULL. For example:<\/p>\n\n\n
SELECT<\/span> 1<\/span> OR<\/span> NULL<\/span>, 0<\/span> OR<\/span> NULL<\/span>, NULL<\/span> or<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+-----------+-----------+--------------+\n| 1 OR NULL | 0 OR NULL | NULL or NULL |\n+-----------+-----------+--------------+\n|         1 |      NULL |         NULL |\n+-----------+-----------+--------------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The following table shows the result of the OR<\/code> operator when combining true (non-zero), false (zero), and NULL:<\/p>\n\n\n\n
<\/td>TRUE<\/strong><\/td>FALSE<\/strong><\/td>NULL<\/strong><\/td><\/tr>
TRUE<\/strong><\/td>TRUE<\/td>TRUE<\/td>TRUE<\/td><\/tr>
FALSE<\/strong><\/td>TRUE<\/td>FALSE<\/td>NULL<\/td><\/tr>
NULL<\/strong><\/td>TRUE<\/td>NULL<\/td>NULL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
MySQL OR operator and short-circuit evaluation<\/h2>\n\n\n\n
Like the AND<\/a><\/code> operator, the OR<\/code> operator is also short-circuited. In other words, MySQL stops evaluating the remaining parts of the expression as soon as it can determine the result. For example:<\/p>\n\n\n
SELECT<\/span> 1<\/span> = 1<\/span> OR<\/span> 1<\/span> \/ 0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+----------------+\n| 1 = 1 OR 1 \/ 0 |\n+----------------+\n|              1 |\n+----------------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Because the expression 1 = 1<\/code> always returns 1, MySQL won’t evaluate the 1 \/ 0 expression. And MySQL would issue an error if it did.<\/p>\n\n\n\n
Operator precedence<\/h2>\n\n\n\n
When an expression contains both AND<\/code> and OR<\/code> operators, MySQL uses the operator precedence to determine the order of evaluation of the operators. MySQL evaluates the operator with higher precedence first.<\/p>\n\n\n\n
Since the AND<\/code> operator has higher precedence than the OR<\/code> operator, MySQL evaluates the AND<\/code> operator before the OR<\/code> operator. For example:<\/p>\n\n\n
SELECT<\/span> 1<\/span> OR<\/span> 0<\/span> AND<\/span> 0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+--------------+\n| 1 OR 0 AND 0 |\n+--------------+\n|            1 |\n+--------------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n
1 OR 0 AND 0 = 1 OR 0 = 1<\/code><\/span><\/pre>\n\n\n
To change the order of evaluation, you use the parentheses. For example:<\/p>\n\n\n
SELECT<\/span> (1<\/span> OR<\/span> 0<\/span>) AND<\/span> 0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+----------------+\n| (1 OR 0) AND 0 |\n+----------------+\n|              0 |\n+----------------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n
(1 OR 0) AND 0 = 1 AND 0 = 0<\/code><\/span><\/pre>\n\n\n
MySQL OR operator examples<\/h2>\n\n\n\n
We’ll use the\u00a0customers<\/code> table from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following query uses the OR<\/code> operator in the WHERE<\/a><\/code> clause to select all the customers located in the USA or France:<\/p>\n\n\n
SELECT<\/span>    \n\tcustomername, \n\tcountry\nFROM<\/span>    \n\tcustomers\nWHERE<\/span> country = 'USA'<\/span> OR<\/span> \n      country = 'France'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+------------------------------+---------+\n| customername                 | country |\n+------------------------------+---------+\n| Atelier graphique            | France  |\n| Signal Gift Stores           | USA     |\n| La Rochelle Gifts            | France  |\n| Mini Gifts Distributors Ltd. | USA     |\n| Mini Wheels Co.              | USA     |\n| Land of Toys Inc.            | USA     |\n| Saveley & Henriot, Co.       | France  |\n| Muscle Machine Inc           | USA     |\n| Diecast Classics Inc.        | USA     |\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example uses the OR<\/code> operator to select the customers who locate in the USA or France and have a credit limit greater than 100,000.<\/p>\n\n\n
SELECT<\/span>   \n\tcustomername, \n\tcountry, \n\tcreditLimit\nFROM<\/span>   \n\tcustomers\nWHERE<\/span>(country = 'USA'<\/span>\n\t\tOR<\/span> country = 'France'<\/span>)\n\t  AND<\/span> creditlimit > 100000<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+------------------------------+---------+-------------+\n| customername                 | country | creditLimit |\n+------------------------------+---------+-------------+\n| La Rochelle Gifts            | France  |   118200.00 |\n| Mini Gifts Distributors Ltd. | USA     |   210500.00 |\n| Land of Toys Inc.            | USA     |   114900.00 |\n| Saveley & Henriot, Co.       | France  |   123900.00 |\n| Muscle Machine Inc           | USA     |   138500.00 |\n| Diecast Classics Inc.        | USA     |   100600.00 |\n| Collectable Mini Designs Co. | USA     |   105000.00 |\n| Marta's Replicas Co.         | USA     |   123700.00 |\n| Mini Classics                | USA     |   102700.00 |\n| Corporate Gift Ideas Co.     | USA     |   105000.00 |\n| Online Diecast Creations Co. | USA     |   114200.00 |\n+------------------------------+---------+-------------+\n11 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that if you do not use the parentheses, the query will return the customers who locate in the USA or the customers located in France with a credit limit greater than 100,000.<\/p>\n\n\n
SELECT<\/span>    \n    customername, \n    country, \n    creditLimit\nFROM<\/span>    \n    customers\nWHERE<\/span> \n    country = 'USA'<\/span>\n    OR<\/span> country = 'France'<\/span>\n    AND<\/span> creditlimit > 100000<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+------------------------------+---------+-------------+\n| customername                 | country | creditLimit |\n+------------------------------+---------+-------------+\n| Signal Gift Stores           | USA     |    71800.00 |\n| La Rochelle Gifts            | France  |   118200.00 |\n| Mini Gifts Distributors Ltd. | USA     |   210500.00 |\n| Mini Wheels Co.              | USA     |    64600.00 |\n| Land of Toys Inc.            | USA     |   114900.00 |\n| Saveley & Henriot, Co.       | France  |   123900.00 |\n| Muscle Machine Inc           | USA     |   138500.00 |\n| Diecast Classics Inc.        | USA     |   100600.00 |\n| Technics Stores Inc.         | USA     |    84600.00 |\n| American Souvenirs Inc       | USA     |        0.00 |\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • The OR<\/code> operator combines two Boolean expressions and returns true when either expression is true. Otherwise, it returns false.<\/li>\n\n\n\n
  • MySQL evaluates the OR<\/code> operator after the AND<\/code> operator if an expression contains both AND<\/code> and OR<\/code> operators. <\/li>\n\n\n\n
  • Use parentheses to change the order of evaluation.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n