Difference in query output

  • 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/

  • 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

  • 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
  • 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 4 (of 4 total)

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