When writing SQL, using parentheses can also be a useful way to distinguish the order of operations and is very complementary to the AND and OR operators. Let’s look at an example.
We first start with a WHERE clause that has multiple conditions.
In this example, we get two results.
What if we add parentheses to the first two arguments of the WHERE clause? Would this produce the same results?
We see the same two records return – no difference. This is because the AND operator is inclusive so it doesn’t make any difference if we only use parentheses around AND statements.
But what happens when we switch those parentheses to the second two arguments with the OR operator?
Is it the same thing?
No! Only one record this time – but why? We need to break down the query in order to understand what is happening here. Just by shifting the parentheses between our arguments, we can see different results because we’re asking the database a different question.
First let’s review the WHERE clause:
Company = (‘Apple Inc.’
AND State = ‘CA’)
OR State = ‘IL’;
The first time we asked the database to return results – we asked for any customer that works for Apple, but also lives in California OR customers that live in Illinois.
Second WHERE clause:
Company = ‘Apple Inc.’
AND (State = ‘CA’
OR State = ‘IL’);
Notice the slight shift because we’ve added parentheses to the second two arguments. Now, what we’re asking for is to show all the customers that work for Apple AND either live in California OR live in Illinois. Had there been records of Apple customers that lived in Illinois, those records would have returned. Instead, we lost a record since the customer didn’t work for Apple.
Hopefully this is all starting to make sense. While it’s important to understand the differences between AND versus OR operators, it’s also important to write SQL using parentheses since the order of operations can play a large part in answering very specific database questions.