I have no id

Question

I have no id

robolance

SSC-Addicted

Points: 403
More actions
December 15, 2014 at 9:20 pm

#313876

Comments posted to this topic are about the item I have no id
December 16, 2014 at 12:56 am

This was removed by the editor as SPAM

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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 More actions · Answer 2

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 🙂

Carlo Romagnano SSC-Insane Points: 22670 More actions · Answer 3

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: 3976 More actions · Answer 4

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 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

December 16, 2014 at 6:47 am

#1764915

Tricky. Thank goodness I've seen it before.

domenico.delbrocco SSChasing Mays Points: 617 More actions · Answer 6

domenico.delbrocco

SSChasing Mays

Points: 617

December 16, 2014 at 6:55 am

#1764919

Good question. Thanks

Hany Helmy SSChampion Points: 13921 More actions · Answer 7

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

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Brian.Klinect Mr or Mrs. 500 Points: 592 More actions · Answer 8

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: 286590 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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: 6096 More actions · Answer 13

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 )

Be still, and know that I am God - Psalm 46:10