In SQL, the OR operator allows you to use alternative conditions when filtering your queries.
The OR operator combines two conditions, and returns TRUE when either of the conditions is TRUE.
Source Table
The following table is used for the examples on this page.
SELECT * FROM Pets;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+ (8 rows affected)
Example
Here’s a simple example to demonstrate the OR operator.
SELECT * FROM Pets
WHERE PetName = 'Fluffy' OR PetName = 'Tweet';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | +---------+-------------+-----------+-----------+------------+
We can see that it did indeed return the rows where the PetName column was either Fluffy or Tweet. No other rows were returned.
Different Columns
In the previous example, both conditions were comparing values against the same column, but this is not a requirement. The expressions on either side of the OR operator can use different columns to compare values against. The OR operator will simply return TRUE when either of those conditions is TRUE.
SELECT * FROM Pets
WHERE PetName = 'Fluffy' OR DOB < '2020-01-01';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | +---------+-------------+-----------+-----------+------------+
Combined with Other Operators
Your filtering criteria can combine expressions that use other operators in addition to the OR operator.
Here’s an example that includes the AND operator.
SELECT * FROM Pets
WHERE (PetName = 'Fluffy' OR PetName = 'Tweet')
AND DOB >= '2020-11-20';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 5 | 1 | 1 | Tweet | 2020-11-28 | +---------+-------------+-----------+-----------+------------+
Notice that I surrounded the OR condition with parentheses. The reason I did that was because, by default, OR operators are evaluated after AND operators. SQL has a defined order of precedence of the operators in an expression, and this dictates that the AND operator is evaluated before the OR operator.
However, you can use parentheses to override the defined precedence of the operators in an expression. Everything within parentheses is evaluated to return a single value. That value can be used by any operator outside those parentheses.
In other words, you can use parentheses to specify the order in which you’d like each logical operator to be evaluated in an expression.
Why is this an issue?
Take a look at the results when we remove the parentheses.
SELECT * FROM Pets
WHERE PetName = 'Fluffy' OR PetName = 'Tweet'
AND DOB >= '2020-11-20';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | +---------+-------------+-----------+-----------+------------+
We actually get a different result.
This is because, the query now checks PetName = 'Tweet' AND DOB >= '2020-11-20' first, then checks the OR expression PetName = 'Fluffy'.
It’s as if we’d put parentheses around the AND expression, like this:
SELECT * FROM Pets
WHERE PetName = 'Fluffy' OR (PetName = 'Tweet'
AND DOB >= '2020-11-20');However, to confuse things even more, we could rearrange our query so that we don’t use parentheses, and yet still get the same result as our first example with parentheses.
Like this:
SELECT * FROM Pets
WHERE DOB >= '2020-11-20'
AND PetName = 'Fluffy' OR PetName = 'Tweet';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 5 | 1 | 1 | Tweet | 2020-11-28 | +---------+-------------+-----------+-----------+------------+
In this case, the AND expression was evaluated first, as expected. The order of the expressions just happened to line up so that DOB >= '2020-11-20' AND PetName = 'Fluffy' was evaluated first, so we excluded the other pet called Fluffy due to its DOB falling outside that range. Only after that, was the OR expression evaluated as an alternative condition to our existing AND result.
The moral of this story? Use parentheses when using multiple operators. That way you can specify the exact order in which you want each expression to be evaluated.