I have no id

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

  • 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

  • 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

  • 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

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

  • Tricky. Thank goodness I've seen it before.

  • Good question. Thanks

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

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

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

  • Iwas Bornready (12/16/2014)


    Tricky. Thank goodness I've seen it before.

    +1 Thanks for the question.



    Everything is awesome!

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

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

  • 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