Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dumb? SQL Question


Dumb? SQL Question

Author
Message
souLTower
souLTower
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 559
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
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478

... 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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47137 Visits: 44346
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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47137 Visits: 44346
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


souLTower
souLTower
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 559
Thanks!
ST
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search