I have no id

Question

I have no id

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

david.gugg (12/16/2014)
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 )

Yes indeed. 😀

_______________________________________________________________

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/

robolance SSC-Addicted Points: 403 More actions · Answer 2

david.gugg (12/16/2014)
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 )

Yep. Assuming you are trying to subset the result set, that's the bug. The corrected query would be:

SELECT s.*

FROM dbo.Stocks s

WHERE s.id IN ( SELECT o.id

FROM OuterVal o )

Assuming what you want is all records in Stocks where the id has a matching record in the OuterVal table.

gayaneavanian SSC Veteran Points: 249 More actions · Answer 3

Small correction - column name in OuterVal is valueid, not id

SELECT s.*

FROM dbo.Stocks s

WHERE s.id IN ( SELECT o.valueid

FROM OuterVal o )

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Carlo Romagnano (12/16/2014)
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

While that is a valid point (paritcularly with poorly written queries), the use of IN or EXISTS can cause significant performance issues. I just replaced a query that used EXISTS for a client where the query was performing 585,000+ reads each time it was run. Using a JOIN we saw the reads drop (amongst other things) to 330 reads each execution. That is significant by itself, but when paired with 1000s of executions an hour, that performance gain becomes huge! In addition, a well written query that uses JOINs, can also return the single record as is done with the EXISTS.

As for the use of IN, I would caution strongly against it. Many apps out there love to use the IN clause instead of a JOIN. Most of them do it wrongly. One such app is a well known big-brother type of app. It loves to throw 35,000 or more values into the IN clause. This, if it ever runs, will cause performance issues. Many times, it will just throw an annoying and nasty error.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 5

Thanks for the excellent question, Lance. I have to admit, it tripped me up. I can see where that would be a problem in queries. I'm fairly strict about using aliases in my queries, but it could be a problem in code that I inherit from someone else.

As an experiment, I tried this code on Sybase ASE, to see if it worked the same way. After fixing a few minor syntax differences, Sybase gives me "ERROR: Invalid column name 'id'." That's probably how SQL Server should work in this case, IMHO. It would be interesting to see if someone else who has access to an Oracle database could try this code there.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Lance,

You continue to mention that this is a bug while it's actually a feature that allows us to write correlated subqueries. It's a feature that can cause bugs, but a feature no matter what. That's a good reason to always qualify the columns used.

And if you continue to post questions, don't use integers for dates as it might be considered a good practice by newbies and it's not. There might be reasons to use them, but never as a default choice.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

robolance SSC-Addicted Points: 403 More actions · Answer 7

Thanks. You are right about using integers for dates. Just got lazy.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

Heals (12/16/2014)
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 🙂

Missed it too. Thanks for pointing this out. I usually use aliases as well, and primarily have to debug my own code - one man shop.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Koen Verbeeck (12/16/2014)
Got this one right. It's an old trick question I've come across a few times 😀

Yes, I think it's turned up as QotD in various disguises three or four times this year.

Tom

webrunner SSC-Dedicated Points: 31865 More actions · Answer 10

Thanks for the question. I fell for the trick and got it wrong, but now I know why.

Thanks again!

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Miles Neale SSChampion Points: 13147 More actions · Answer 11

TomThomson (12/16/2014)
Koen Verbeeck (12/16/2014)
Got this one right. It's an old trick question I've come across a few times 😀
Yes, I think it's turned up as QotD in various disguises three or four times this year.

And the first time I got it wrong, This time I got it.

Thanks!

Not all gray hairs are Dinosaurs!

saravanan.a 88885 Ten Centuries Points: 1003 More actions · Answer 12

saravanan.a 88885

Ten Centuries

Points: 1003

December 18, 2014 at 10:02 pm

#1765609

Two weeks before come across these, Good one...:-)

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 13

Hany Helmy (12/16/2014)
Nice tricky question, needs to be read slowly to get it right 🙂

+1

Igor Micev,My blog: www.igormicev.com

zaff_mind Default port Points: 1480 More actions · Answer 14

you do not have ID column in the Outval Table. Then in this case subquery itself will fail. how can you check in a where clause IN(select id from outval ) table, when Id column itself is not available in the outval table.

Please explain

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 15

zaff_mind (12/20/2014)
Please explain

Here is a good link: http://www.sqlservercentral.com/questions/T-SQL/119847/

Scroll to where you see a blue box and read the text inside of it. 🙂

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/