Parentheses with OR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parentheses with OR

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-05-06 : 15:19:44

Not sure why these two queries should return different counts?

SELECT COUNT(DISTINCT(T.ID))
FROM Table1 AS T
WHERE (
T.Code IN ('1','2')
OR T.Code IN ('3')
)

SELECT COUNT(DISTINCT(T.ID))
FROM Table1 AS T
WHERE T.Code IN ('1','2')
OR T.Code IN ('3')

--the first returns less than the second. Do parentheses act as a kind of aggregator?

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-06 : 15:46:50

I..don't think so


CREATE TABLE #Table1(ID int , code char(1))
GO

INSERT INTO #Table1(ID, Code)
SELECT 1, '1' UNION ALL
SELECT 1, '2' UNION ALL
SELECT 2, '3' UNION ALL
SELECT 3, '4'
GO


SELECT COUNT(DISTINCT(T.ID))
FROM #Table1 AS T
WHERE (
T.Code IN ('1','2')
OR T.Code IN ('3')
)

SELECT COUNT(DISTINCT(T.ID))
FROM #Table1 AS T
WHERE T.Code IN ('1','2')
OR T.Code IN ('3')
GO

DROP TABLE #Table1
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dmilam
Posting Yak Master

185 Posts

Posted - 2010-05-06 : 16:25:00

Thanks; could it be something wrong with the table I'm using, Table1? Or with the fact that, apart from the too-simplified version I've given, I'm joining Table1 to other tables and adding a date calculation? (I thought not at first, hence the simplification, but maybe so)


SELECT COUNT(DISTINCT(T1.ID))
FROM Table1 AS T1
INNER JOIN Table2 AT T2 ON T2.ID = T1.ID
INNER JOIN Table3 AT T3 ON T3.ID = T2.ID
WHERE (
LEFT(T3.Code,2) IN ('AB','BC')
OR LEFT(T3.Code,3) IN ('CDE')
)
AND DATEPART(Year,T3.StartDate) >= DATEPART(Year,Current_TimeStamp)-1

SELECT COUNT(DISTINCT(T1.ID))
FROM Table1 AS T1
INNER JOIN Table2 AT T2 ON T2.ID = T1.ID
INNER JOIN Table3 AT T3 ON T3.ID = T2.ID
WHERE 
LEFT(T3.Code,2) IN ('AB','BC')
OR LEFT(T3.Code,3) IN ('CDE')
AND DATEPART(Year,T3.StartDate) >= DATEPART(Year,Current_TimeStamp)-1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-06 : 16:30:08

Yes those are different WHERE clauses. Look up Operator Precedence [Transact-SQL] in books online.

The AND operator has higher precedence than OR.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-06 : 16:35:55

Run this. Modified the sample data. You should see the difference.

CREATE TABLE #Table1(ID int , code char(1),date datetime)
GO

INSERT INTO #Table1(ID, code,date)
SELECT 1, '1', '20090101' UNION ALL
SELECT 1, '3', '20100101' UNION ALL
SELECT 2, '2', '20080601' UNION ALL
SELECT 3, '4', '20100101'
GO


SELECT COUNT(DISTINCT(T.ID))
FROM #Table1 AS T
WHERE (
T.Code IN ('1','2')
OR T.Code IN ('3')
)
AND date >= '20090101'

SELECT COUNT(DISTINCT(T.ID))
FROM #Table1 AS T
WHERE T.Code IN ('1','2')
OR T.Code IN ('3')
AND date >= '20090101'
GO

DROP TABLE #Table1
GO

dmilam
Posting Yak Master

185 Posts

Posted - 2010-05-06 : 17:24:42

Very interesting, thanks.

It seems that parentheses here "exclude" operations in the WHERE clause, so AND comes first, unless

WHERE T.Code IN ('1','2')

which comes first as written, again because

T.Code IN ('1','2')

is not included in parentheses with other items.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources