• Dung Dinh (3/7/2013)


    GilaMonster (3/7/2013)


    Dung Dinh (3/7/2013)


    2 - If both are big, you should use second solution and index the columns on ON clause

    Not true.

    I mean that the second is preferred in this case.

    The first is the preferred option in all cases, it's less work and if all you're doing is checking for existence of a row in another table then IN/EXISTS is the logical operation to use as that's exactly what it does.

    Inner join checks, joins and fetches both rows, it'll cause duplicate rows if there's more than one matching row (in won't) and it's more work since it's a full join instead of a semi-join

    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