Difference in query output

Question

Difference in query output

kapil_kk

SSC-Insane

Points: 21316
More actions
April 17, 2015 at 1:36 am

#299912

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/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

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.

Difference in query output

Cookies on SQLServerCentral