In SQL, the AND operator allows you to check for multiple conditions when filtering your queries.
The AND operator combines two Boolean expressions and returns TRUE when both expressions are 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 AND operator.
SELECT * FROM Pets
WHERE PetName = 'Fluffy' AND DOB > '2020-11-01';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | +---------+-------------+-----------+-----------+------------+
As expected, it returned only those rows where both the PetName column was Fluffy and the DOB column was greater than 2020-11-01.
In this case, only one row matched that criteria, and therefore, only one row was returned.
Combined with Other Operators
Your filtering criteria can combine expressions that use other operators in addition to the AND operator.
Here’s an example that includes the OR 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. I did that in order to specify the order in which each expression should be evaluated.,
By default, AND operators are evaluated before OR 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.
To demonstrate this 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 | +---------+-------------+-----------+-----------+------------+
Now we 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.
Therefore, it’s highly recommended to use parentheses when using multiple operators. Doing this ensures that the query is evaluated in exactly the way you intended it to be.