I’ve been seeing a lot people get confused with their where clauses lately. Someone wanted to return where (X = A) and (Y = B or Z = C), but what they get is a totally different result. Sure it’s physically written and viewed as (X = A and Y = B or Z = C), but logically it holds a different meaning.
This demo will help illustrate this for you.
Let’s create our table variable, put a little data inside it, and see what it looks like afterwards.
DECLARE @TestTable TABLE
(
id int primary key
,color varchar(10)
,number int
,animal varchar(25)
);
INSERT INTO @TestTable
(id,color, number, animal)
VALUES
(1,'blue', 1, 'monkey'),
(2,'red', 1, 'monkey'),
(3,'pink',1,'rhino'),
(4,'purple', 2, 'monkey'),
(5,'orange', 2, 'chicken'),
(6,'black',2,'cat');
select * from @testtable;
Neat! We got data! Well, let’s try the above scenario.
This was my example earlier: (X = A and Y = B or Z = C)
Translated to where clause language: (number = 1 and color = ‘blue’ or animal = ‘monkey’)
This will translate completely to:
SELECT * FROM @testtable WHERE number = 1 AND color = 'blue' OR animal = 'monkey'
Now here’s the result set once we run the query:
But hold up! We are getting three results back, we only expected to get back records 1 and 2. How did record 4 get in there? The answer is order of precedence. To make it perform logically how you wrote it out physically, you have two options.
The way many people would recommend to help protect against user error or accidents is to use parenthesis. These make it declaritive to at least show your logical intent behind the code.
SELECT *
FROM @testtable
WHERE (number = 1)
AND (
color = 'blue' OR animal = 'monkey')
You can re-write the query correctly though to forgo the need of parenthesis.
SELECT * FROM @testtable WHERE color = 'blue' OR animal = 'monkey' AND number=1
If you run the query with the parenthesis fix or the logical fix, your results will be as seen below:
Regardless of your style or method, your team should have a standard for how they want the SQL styled and formatted. When your code becomes convoluted with unnecessary additions, it may be time to take a step back and evaluate how the code is written and how it can be tuned. You want it to be maintainable, short, and following best practices / ansi standards. The code should be documented, whether implied or explicitly, but of course preferrably both.
I do tend to personally use parenthesis even unnecessarily (like the example above) because I want that code to be implicitly documented. I want the next developer that sees this to know that this is the way I expect the logic to be followed. It can also lend itself to having less likely of a chance of personal error when writing the code.
So when in doubt, check your order of precedence but use your parenthesis with proper discretion and vigor!
Here is the handy reference for the order of precedence in T-SQL.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql