{"id":318,"date":"2016-03-29T07:14:23","date_gmt":"2016-03-29T07:14:23","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=318"},"modified":"2025-01-23T08:04:57","modified_gmt":"2025-01-23T15:04:57","slug":"sql-or","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-or\/","title":{"rendered":"SQL OR Operator"},"content":{"rendered":"\n

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

Introduction to SQL OR operator #<\/a><\/h2>\n\n\n\n

The OR<\/code> is a logical operator<\/a> that combines two Boolean expressions. The OR<\/code> operator returns false<\/code> if and only if both expressions are false<\/code>.<\/p>\n\n\n\n

The OR<\/code> operator is typically used in the WHERE<\/a><\/code> clause of the SELECT<\/a><\/code>, UPDATE<\/a><\/code>, or DELETE<\/a><\/code> statement to form a flexible condition.<\/p>\n\n\n\n

Here’s the syntax of the OR<\/code> operator:<\/p>\n\n\n

expression1 OR expression2<\/code><\/span><\/pre>\n\n\n
The following table shows the results of the OR<\/code> operator when comparing the true<\/code>, false<\/code>, and NULL<\/code> values:<\/p>\n\n\n\n
expression1<\/th>expression2<\/th>expression1 OR expression2<\/th><\/tr><\/thead>
true<\/td>true<\/td>true<\/td><\/tr>
true<\/td>false<\/td>true<\/td><\/tr>
true<\/td>NULL<\/td>true<\/td><\/tr>
false<\/td>false<\/td>false<\/td><\/tr>
false<\/td>NULL<\/td>NULL<\/td><\/tr>
NULL<\/td>NULL<\/td>NULL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
If the database system supports the short-circuit feature, the OR<\/code> operator stops evaluating the second expression as soon as the first expression is true because the the OR<\/code> operator always returns true if one of the two expressions is true.<\/p>\n\n\n\n
Note that the short-circuit feature helps the database system save CPU computation by aborting processing the remaining part of a logical expression as soon as it can determine the result. For more information on the short-circuit feature, check the SQL AND operator tutorial<\/a>.<\/p>\n\n\n\n
When using the OR<\/code> operator with the AND<\/code> operator, the database system evaluates the OR<\/code> operator after the AND<\/code> operator. This is known as the rule of precedence. However, you can use parentheses to change the order of evaluation.<\/p>\n\n\n\n
SQL OR operator examples #<\/a><\/h2>\n\n\n\n
We’ll use the  employees<\/code> table from the sample database<\/a> for the demonstration of the OR<\/code> operator.<\/p>\n\n\n\n
\"employees_table\"<\/figure>\n\n\n\n
The following statement finds all employees who joined the company in 1997 or 1998.<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  hire_date\nFROM<\/span>\n  employees\nWHERE<\/span>\n  EXTRACT<\/span>(year<\/span> from<\/span> hire_date) = 1999<\/span>\n  OR<\/span> EXTRACT<\/span>(year<\/span> from<\/span> hire_date) = 2000<\/span>\nORDER<\/span> BY<\/span>\n  hire_date;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name  | hire_date\n------------+------------+------------\n Diana      | Lorentz    | 1999-02-07\n Kimberely  | Grant      | 1999-05-24\n Karen      | Colmenares | 1999-08-10\n Luis       | Popp       | 1999-12-07\n Charles    | Johnson    | 2000-01-04<\/code><\/span><\/pre>\n\n\n
To find all employees who joined the company  in 1999 or 2000 and work in the department id 3, you use both AND<\/code> and OR<\/code> operators as follows:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  hire_date,\n  department_id\nFROM<\/span>\n  employees\nWHERE<\/span>\n  department_id = 3<\/span>\n  AND<\/span> (\n    EXTRACT<\/span>(year<\/span> from<\/span> hire_date) = 1999<\/span> OR<\/span> \n    EXTRACT<\/span>(year<\/span> from<\/span> hire_date) = 2000<\/span>\n  )\nORDER<\/span> BY<\/span>\n  hire_date;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name  | hire_date  | department_id\n------------+------------+------------+---------------\n Karen      | Colmenares | 1999-08-10 |             3<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
If you don’t use the parentheses, the query will retrieve employees who joined the company in 1999 and work in department id 3 or employees who joined in 2000 regardless of departments<\/p>\n\n\n\n
This is because the database system evaluates the OR<\/code> operator after the AND<\/code> operator.<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  hire_date,\n  department_id\nFROM<\/span>\n  employees\nWHERE<\/span>\n  department_id = 3<\/span>\n  AND<\/span> \n    EXTRACT<\/span>(year<\/span> from<\/span> hire_date) = 1999<\/span> OR<\/span> \n    EXTRACT<\/span>(year<\/span> from<\/span> hire_date) = 2000<\/span>\n  \nORDER<\/span> BY<\/span>\n  hire_date;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name  | hire_date  | department_id\n------------+------------+------------+---------------\n Karen      | Colmenares | 1999-08-10 |             3\n Charles    | Johnson    | 2000-01-04 |             8<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following query finds all employees who joined the company in 1990 <\/code>or 1999<\/code> or 2000<\/code>.<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  hire_date,\n  department_id\nFROM<\/span>\n  employees\nWHERE<\/span>\n    EXTRACT<\/span>(year<\/span> from<\/span> hire_date) = 1990<\/span> OR<\/span> \n    EXTRACT<\/span>(year<\/span> from<\/span> hire_date) = 1999<\/span> OR<\/span> \n    EXTRACT<\/span>(year<\/span> from<\/span> hire_date) = 2000<\/span>\nORDER<\/span> BY<\/span>\n  hire_date;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name  | hire_date  | department_id\n------------+------------+------------+---------------\n Alexander  | Hunold     | 1990-01-03 |             6\n Diana      | Lorentz    | 1999-02-07 |             6\n Kimberely  | Grant      | 1999-05-24 |             8\n Karen      | Colmenares | 1999-08-10 |             3\n Luis       | Popp       | 1999-12-07 |            10\n Charles    | Johnson    | 2000-01-04 |             8<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  hire_date,\n  department_id\nFROM<\/span>\n  employees\nWHERE<\/span>\n    EXTRACT<\/span>(year<\/span> from<\/span> hire_date) IN<\/span> (1990<\/span>, 1999<\/span>, 2000<\/span>)\nORDER<\/span> BY<\/span>\n  hire_date;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name  | hire_date  | department_id\n------------+------------+------------+---------------\n Alexander  | Hunold     | 1990-01-03 |             6\n Diana      | Lorentz    | 1999-02-07 |             6\n Kimberely  | Grant      | 1999-05-24 |             8\n Karen      | Colmenares | 1999-08-10 |             3\n Luis       | Popp       | 1999-12-07 |            10\n Charles    | Johnson    | 2000-01-04 |             8<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
You can replace the OR<\/code> operators by the IN<\/a><\/code> operator as follows:<\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
If a query uses many OR<\/code> operators, it will become difficult to read. To make the query more readable, you can use the IN<\/a><\/code> operator instead.<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n