Post reply

Dumb? SQL Question

  • October 15, 2012 at 5:30 am

    #269512

    I'm working on a dynamic SQL builder and came across the following by accident.

    -- Some background

    -- To query from a list:

    select a, b, c from myTable where a IN (1, 2, 3)

    -- To query from a correlated sub query

    select a, b, c from myTable where a IN (select A.a from myOtherTable A)

    -- An example of why correlated sub queries are bad if you don't use aliases

    -- In the sub query, the DB doesn't know where to get the value of [a]

    -- The name exists in both places

    select a, b, c from myTable where a IN (select a from myOtherTable)

    -- OK, Here's the question. By accident my code created the following

    select a, b, c from myTable where a IN (a)

    -- I realized it returns all records because it compares the value of [a] in each row to itself.

    This actually works well for what I am doing. There wouldn't be any overhead since the DB isn't querying anything to get the value of [a]. My question:

    Is it valid syntax or a fluke that will bomb in another release?

    Is there any reason not to use this approach?

    Thanks as always

    ST

  • October 15, 2012 at 6:37 am

    #1549098

    ... There wouldn't be any overhead since the DB isn't querying anything to get the value of [a].

    ...

    Except the fact that this will lead to full table (or, if you have an index on this column, then Index) scan...

    The syntax itself is valid as it can be executed just fine.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • October 15, 2012 at 6:40 am

    #1549100

    Eugene Elutin (10/15/2012)

    ... There wouldn't be any overhead since the DB isn't querying anything to get the value of [a].

    ...

    Except the fact that this will lead to full table (or, if you have an index on this column, then Index) scan...

    Since there's no other where clause, it has to scan anyway. If there was a sargable predicate, SQL would seek for that.

    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

  • October 15, 2012 at 6:41 am

    #1549103

    It's completely valid. It's like saying WHERE 1=1. At worst it'll confuse people reading the code who wonder why it's there.

    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

  • October 15, 2012 at 6:48 am

    #1549106

    Thanks!

    ST

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

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