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