SQL Server 2008 R2 – Understanding AND Conditions Within ORs

sql serversql-server-2008-r2

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 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.

Best Answer

This is explained by the rules for operator precedence:

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):

Result

Demo on Stack Exchange Data Explorer

I would strongly suggest using the version with parentheses for clarity.

Related Question