April 17, 2015 at 1:36 am
Hi all,
Can you guys please tell me why I am getting different rows in output from below queries:
SELECT t1.col1
from table1 t1
WHERE t1.col1 < (SELEcT MAX(t2.col2)
FROM table2 t2
WHERE t2.col3 = t1.col4) OR t1.col1 < (SELECT MAX (t3.col2)
FROM table3 t3
WHERE t3.col2 = t1.col4);
----Returning 10 rows
SELECT t1.col1
from table1 t1
WHERE t1.col1 < (SELEcT MAX( t2.col2)
FROM table2 t2,table1 t1
WHERE t2.col3 = t1.col4) OR t1.col1 < (SELECT MAX (t3.col2)
FROM table3 t3,table1 t1
WHERE t3.col2 = t1.col4);
---- Returning >10000 rows
What the difference in these both queries?
Thanks in advance
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 17, 2015 at 2:38 am
It's impossible to tell which column belongs to which table, so there's no way we can answer your question. Add table prefix to each column.
-- Gianluca Sartori
April 17, 2015 at 2:44 am
And I'd suggest changing to use JOINs, not the old join syntax which has been out of favour for ~15 odd years or more. It looks like you have some cartesian products due to the lack of joins between the tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2015 at 5:52 am
Because they are different queries. Consider the first subquery of those queries. Suppose t2 has one row (col2=10, col3=300) and t1 rows are
(col1=5, col4=100),
(col1=5, col4=300).
In the first query first subquery for the (col1=5, col4=100) row of t1 will return max(t2.col2)==null.
In the second query first subquery for the (col1=5, col4=100) row of t1 will return max(t2.col2)== 10.
And as a result, first query will not return (col1=5,col4=100) and the second will do.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply