Post reply

Best SQL query

  • September 26, 2010 at 11:30 pm

    #387865

    Let say I have two tables,

    TableA,

    Col1(P)

    Col2

    TableB,

    Col3(P)

    Col1(F)

    Col4

    Which one of the following query you think is best,

    SELECT Col2,Col4 FROM TableA A,TableB B WHERE A.Col1=B.Col1

    SELECT Col2,Col4 FROM TableA A INNER JOIN TableB B ON A.Col1=B.Col1

    SELECT Col2,(SELECT Col4 FROM TableB B WHERE A.Col1=B.Col1) AS Col4 FROM TableA A

    I think you understand what I mean, Cross Join, INNER JOIN or SubQuery?

  • September 27, 2010 at 12:03 am

    #1227521

    Best for what?

    Performance? The first two will be the same, dunno about the third, should be very close.

    Readability? I'd say the second.

    Validity? The third will fail if there's more than one row in B for each row in A.

    You don't have a cross join in the first one, you have an old-style join where the join predicate is in the where clause. Do note that it is no longer possible to do outer joins like that due to the removal of *= as a valid operator.

    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

  • September 27, 2010 at 12:10 am

    #1227522

    I think that is the same.

    the better From the format:

    SELECT Col2,Col4 FROM TableA A INNER JOIN TableB B ON A.Col1=B.Col1

  • September 27, 2010 at 12:10 am

    #1227523

    Thank you very much Gail.

    So you are saying I should go with second(INNER JOIN),

    (Note: The third one will use Top 1 so that it will not fail(only fails if no rows are present))

    Thanks again for your tip.

  • September 27, 2010 at 12:13 am

    #1227525

    abcim (9/27/2010)

    (Note: The third one will use Top 1 so that it will not fail(only fails if no rows are present))

    In which case it will perform absolutely terribly as you'll be forcing execution of the subquery once for each row of the outer query (because of the TOP).

    It won't fail if no rows are present.

    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

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

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