SQL OR Operator

Summary: in this tutorial, you will learn how to use the SQL OR operator to combine two Boolean expressions.

Introduction to SQL OR operator #

The OR is a logical operator that combines two Boolean expressions. The OR operator returns false if and only if both expressions are false.

The OR operator is typically used in the WHERE clause of the SELECT, UPDATE, or DELETE statement to form a flexible condition.

Here’s the syntax of the OR operator:

expression1 OR expression2

The following table shows the results of the OR operator when comparing the true, false, and NULL values:

expression1	expression2	expression1 OR expression2
true	true	true
true	false	true
true	NULL	true
false	false	false
false	NULL	NULL
NULL	NULL	NULL

If the database system supports the short-circuit feature, the OR operator stops evaluating the second expression as soon as the first expression is true because the the OR operator always returns true if one of the two expressions is true.

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.

When using the OR operator with the AND operator, the database system evaluates the OR operator after the AND operator. This is known as the rule of precedence. However, you can use parentheses to change the order of evaluation.

SQL OR operator examples #

We’ll use the employees table from the sample database for the demonstration of the OR operator.

The following statement finds all employees who joined the company in 1997 or 1998.

SELECT
  first_name,
  last_name,
  hire_date
FROM
  employees
WHERE
  EXTRACT(year from hire_date) = 1999
  OR EXTRACT(year from hire_date) = 2000
ORDER BY
  hire_date;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name  | hire_date
------------+------------+------------
 Diana      | Lorentz    | 1999-02-07
 Kimberely  | Grant      | 1999-05-24
 Karen      | Colmenares | 1999-08-10
 Luis       | Popp       | 1999-12-07
 Charles    | Johnson    | 2000-01-04

To find all employees who joined the company in 1999 or 2000 and work in the department id 3, you use both AND and OR operators as follows:

SELECT
  first_name,
  last_name,
  hire_date,
  department_id
FROM
  employees
WHERE
  department_id = 3
  AND (
    EXTRACT(year from hire_date) = 1999 OR 
    EXTRACT(year from hire_date) = 2000
  )
ORDER BY
  hire_date;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name  | hire_date  | department_id
------------+------------+------------+---------------
 Karen      | Colmenares | 1999-08-10 |             3Code language: plaintext (plaintext)

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

This is because the database system evaluates the OR operator after the AND operator.

SELECT
  first_name,
  last_name,
  hire_date,
  department_id
FROM
  employees
WHERE
  department_id = 3
  AND 
    EXTRACT(year from hire_date) = 1999 OR 
    EXTRACT(year from hire_date) = 2000
  
ORDER BY
  hire_date;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name  | hire_date  | department_id
------------+------------+------------+---------------
 Karen      | Colmenares | 1999-08-10 |             3
 Charles    | Johnson    | 2000-01-04 |             8Code language: plaintext (plaintext)

If a query uses many OR operators, it will become difficult to read. To make the query more readable, you can use the IN operator instead.

For example, the following query finds all employees who joined the company in 1990 or 1999 or 2000.

SELECT
  first_name,
  last_name,
  hire_date,
  department_id
FROM
  employees
WHERE
    EXTRACT(year from hire_date) = 1990 OR 
    EXTRACT(year from hire_date) = 1999 OR 
    EXTRACT(year from hire_date) = 2000
ORDER BY
  hire_date;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name  | hire_date  | department_id
------------+------------+------------+---------------
 Alexander  | Hunold     | 1990-01-03 |             6
 Diana      | Lorentz    | 1999-02-07 |             6
 Kimberely  | Grant      | 1999-05-24 |             8
 Karen      | Colmenares | 1999-08-10 |             3
 Luis       | Popp       | 1999-12-07 |            10
 Charles    | Johnson    | 2000-01-04 |             8Code language: plaintext (plaintext)

You can replace the OR operators by the IN operator as follows:

SELECT
  first_name,
  last_name,
  hire_date,
  department_id
FROM
  employees
WHERE
    EXTRACT(year from hire_date) IN (1990, 1999, 2000)
ORDER BY
  hire_date;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name  | hire_date  | department_id
------------+------------+------------+---------------
 Alexander  | Hunold     | 1990-01-03 |             6
 Diana      | Lorentz    | 1999-02-07 |             6
 Kimberely  | Grant      | 1999-05-24 |             8
 Karen      | Colmenares | 1999-08-10 |             3
 Luis       | Popp       | 1999-12-07 |            10
 Charles    | Johnson    | 2000-01-04 |             8Code language: plaintext (plaintext)

SQL OR Operator

Introduction to SQL OR operator #

SQL OR operator examples #

Summary #

Databases #

Quiz #