patternsqlModerate
AND conditions within unbracketed ORs work but why?
Viewed 0 times
conditionswhybutwithinorsworkunbracketedand
Problem
I have very similar code to the first block below. It is in production in a critical process, and I am not confident that it will always work. I would like to know why it does. It uses
Here is the simple table I am using for testing:
Id Description
1 One
2 Two
3 Three
4 Four
5 Five
6 Six
7 Seven
8 Eight
9 Nine
10 Ten
11 Eleven
This code works, but I don't know why:
It returns:
Id Description
1 One
3 Three
7 Seven
Here is the traditional code that uses parentheses (that I would expect to work):
Returns the same result.
or (1=1) to group sets of and conditions. I am concerned that the returned results will change if the SQL version is changed.Here is the simple table I am using for testing:
Id Description
1 One
2 Two
3 Three
4 Four
5 Five
6 Six
7 Seven
8 Eight
9 Nine
10 Ten
11 Eleven
This code works, but I don't know why:
SELECT [Id] ,[Description]
FROM [Seminars].[dbo].[CodeTable]
where (1=1)
and Id = 1
and Description = 'One'
or (1=1)
and Id = 7
and Description = 'Seven'
or (1=1)
and Id = 3
and Description = 'Three'It returns:
Id Description
1 One
3 Three
7 Seven
Here is the traditional code that uses parentheses (that I would expect to work):
SELECT TOP 1000 [Id], [Description]
FROM [Seminars].[dbo].[CodeTable]
where
(Id = 1
and Description = 'One')
or
(Id = 7
and Description = 'Seven')
or
(Id = 3
and Description = 'Three')Returns the same result.
Solution
This is explained by the rules for operator precedence:
Operator Precedence (Transact-SQL)
As noted there,
The
This produces the same result, for the same reason (precedence):
Demo on Stack Exchange Data Explorer
I would strongly suggest using the version with parentheses for clarity.
Operator Precedence (Transact-SQL)
As noted there,
AND has a higher evaluation precedence than OR.The
1 = 1 parts of your query are redundant (and removed by the optimizer). You could equally well have written:DECLARE @Test AS table
(
Id integer PRIMARY KEY,
[Description] varchar(10) UNIQUE
);
INSERT @Test
(Id, [Description])
VALUES
(1, 'One'),
(2, 'Two'),
(3, 'Three'),
(4, 'Four'),
(5, 'Five'),
(6, 'Six'),
(7, 'Seven'),
(8, 'Eight'),
(9, 'Nine'),
(10, 'Ten'),
(11, 'Eleven');
SELECT
T.Id,
T.[Description]
FROM @Test AS T
WHERE
T.Id = 1 AND T.[Description] = 'One'
OR Id = 7 AND T.[Description] = 'Seven'
OR Id = 3 AND T.[Description] = 'Three';This produces the same result, for the same reason (precedence):
Demo on Stack Exchange Data Explorer
I would strongly suggest using the version with parentheses for clarity.
Code Snippets
DECLARE @Test AS table
(
Id integer PRIMARY KEY,
[Description] varchar(10) UNIQUE
);
INSERT @Test
(Id, [Description])
VALUES
(1, 'One'),
(2, 'Two'),
(3, 'Three'),
(4, 'Four'),
(5, 'Five'),
(6, 'Six'),
(7, 'Seven'),
(8, 'Eight'),
(9, 'Nine'),
(10, 'Ten'),
(11, 'Eleven');
SELECT
T.Id,
T.[Description]
FROM @Test AS T
WHERE
T.Id = 1 AND T.[Description] = 'One'
OR Id = 7 AND T.[Description] = 'Seven'
OR Id = 3 AND T.[Description] = 'Three';Context
StackExchange Database Administrators Q#135379, answer score: 16
Revisions (0)
No revisions yet.