SQL OR

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 SQL OR is a logical operator that combines two boolean expressions. The SQL OR operator returns either true or false depending on the results of expressions.

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

The following illustrates the syntax of the SQL OR operator:

SELECT
	column1,
	column2,
        ...
FROM
	table1
WHERE
	expression1
OR expression2;

The following table shows the result of the OR operator when we compare the true, false, and NULL values.

	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

Notice that the OR operator always returns true if either expression is true.

If the database system supports the short-circuit feature, the OR the operator stops evaluating the remaining parts of the condition as soon as one expression 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 it out the SQL AND operator tutorial.

When you use 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 the evaluation.

SQL OR operator examples

We will 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
	YEAR (hire_date) = 1997 OR YEAR (hire_date) = 1998
ORDER BY
	first_name,
	last_name;

To find all employees who joined the company  in 1997 or 1997 and worked in 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 (
	YEAR (hire_date) = 1997
	OR YEAR (hire_date) = 1998
)
ORDER BY
	first_name,
	last_name;

If you don’t use the parentheses, the query will retrieve employees who joined the company in 1997 and worked in department id 3 or employees who joined the company in 1998 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 YEAR (hire_date) = 1997
OR YEAR (hire_date) = 1998
ORDER BY
	first_name,
	last_name;

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
FROM
	employees
WHERE
	YEAR (hire_date) = 2000
OR YEAR (hire_date) = 1999
OR YEAR (hire_date) = 1990;

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

SELECT
	first_name,
	last_name,
	hire_date
FROM
	employees
WHERE
	YEAR (hire_date) IN (1990, 1999, 2000)
ORDER BY
	hire_date;

In this tutorial, you have learned how to use the SQL OR operator combines two Boolean expressions to form a flexible condition.