I have no id

  • robolance

    SSC-Addicted

    Points: 403

    Comments posted to this topic are about the item I have no id

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Got this one right. It's an old trick question I've come across a few times 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Heals

    SSCrazy

    Points: 2234

    Good question - got it wrong, but learnt something so all to the good!

    Never come across it before as I always use aliases, but good to know when I have to work on code that isn't mine 🙂

  • This was removed by the editor as SPAM

  • Carlo Romagnano

    SSC-Insane

    Points: 22011

    It might be better to use JOINS, but that is a different question.

    I disagree! JOINS are different than IN or EXISTS in the WHERE clause.

    JOINS multiply the number of rows of the joined tables.

    At last, if you write wrong the query also JOIN fails.

    SELECT

    *

    FROM

    dbo.Stocks

    JOIN

    OuterVal

    ON id = id -- here is the same error + all columns of OuterVal are in the output list

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    I got an error. My default schema is not dbo. The tables get created in the default schema and then the first select is explicitly from dbo. Sometimes tough to tell what is being tested.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Tricky. Thank goodness I've seen it before.

  • domenico.delbrocco

    Mr or Mrs. 500

    Points: 587

    Good question. Thanks

  • Hany Helmy

    SSChampion

    Points: 13488

    Nice tricky question, needs to be read slowly to get it right 🙂

  • Brian.Klinect

    Mr or Mrs. 500

    Points: 592

    Didn't expect to see that at all! 😀

    So in this particular query, since we are checking if the id from Stocks is in the same set of id from Stocks, the WHERE clause can be left out?

  • Sean Lange

    SSC Guru

    Points: 286536

    For those interested in reading about INNER JOIN versus IN...http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Dana Medley

    SSCertifiable

    Points: 6764

    Iwas Bornready (12/16/2014)


    Tricky. Thank goodness I've seen it before.

    +1 Thanks for the question.



    Everything is awesome!

  • robolance

    SSC-Addicted

    Points: 403

    Short answer, yes, the WHERE clause could be left out. Thanks for pointing that out.

    Long answer: It's usually a bug. The idea is that you are trying to link two tables together, where the 2nd table has a subset of and the where clause is attempting to return only the values that match. But, as written, the question never implied the 3 rows was an issue. Good point.

    I found this bug in a query we had been running for years. It is part of our master planning process and was throwing our production numbers off.

    The moral of the story is use aliases.

  • robolance

    SSC-Addicted

    Points: 403

    Oops. Sorry about leaving the dbo in there. That could be the topic of a good QotD. That can be a nasty, hard to find issue.

  • david.gugg

    SSCertifiable

    Points: 5696

    So as it is currently written, is the query actually doing this?:

    SELECT s.*

    FROM dbo.Stocks s

    WHERE s.id IN ( SELECT s.id

    FROM OuterVal )


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

Viewing 15 posts - 1 through 15 (of 30 total)

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