Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dumb? SQL Question Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 5:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 10:38 AM
Points: 103, Visits: 448
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







Post #1372659
Posted Monday, October 15, 2012 6:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201

... 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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1372692
Posted Monday, October 15, 2012 6:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1372694
Posted Monday, October 15, 2012 6:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1372697
Posted Monday, October 15, 2012 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 10:38 AM
Points: 103, Visits: 448
Thanks!
ST
Post #1372700
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse