2

I have query which look like this

SELECT *
FROM my_table
WHERE ... AND id IN (SELECT t2.id
                     FROM my_table t2
                     WHERE id = t2.id AND ... );

I know in this example I could combine the two WHERE clauses and don't use a sub-query but this is not the point.

This query works perfectly on the DBMS I'm using (SQL Anywhere 16) but I'm wondering if the fact that id in the sub-query refers to my_table.id from the main query is a standard behavior or if I'm just lucky.

Link or reference to an RFC or any official document appreciated :)

  • 2
    Hmm. I would have expected id to be the same as t2.id - the "nearest" table reference is used – a_horse_with_no_name Jan 27 '17 at 12:10
  • 1
    @a_horse_with_no_name you are right. It is. The id is equivalent to t2.id there. – ypercubeᵀᴹ Jan 27 '17 at 12:32
4

No, this is wrong. The standard behaviour is that in WHERE id = ... in the subquery the id refers to the closest table in scope. And that is t2. It would refer to my_table in the external query only if t2 had no id column.

You are lucky though because the WHERE id = t2.id - which is translated as WHERE t2.id = t2.id is not actually needed because you used WHERE id IN (subquery). If you had an EXISTS version, it wouldn't work as you expected.

So, the proper ways to write the query would be either prefix all column references:

SELECT t1.*
FROM my_table AS t1
WHERE ... AND t1.id IN 
              (SELECT t2.id
               FROM my_table t2
               WHERE --- t1.id = t2.id AND  -- remove, we don't need this 
               ... );

Or be very careful and prefix only when needed:

SELECT *
FROM my_table AS t1
WHERE ... AND id IN 
              (SELECT id
               FROM my_table t2
               WHERE --- id = t2.id AND  -- remove, we still don't need this 
                 ... );

I would still prefer the EXISTS version for such queries:

SELECT t1.*
FROM my_table AS t1
WHERE ... AND EXISTS
              (SELECT *
               FROM my_table t2
               WHERE t1.id = t2.id AND  -- we do need "t1". here
               ... );

or (if you are always very careful):

SELECT *
FROM my_table AS t1
WHERE ... AND EXISTS
              (SELECT *
               FROM my_table t2
               WHERE t1.id = id AND    -- we need only the "t1". here
                                       -- the second ref, = id is translated
                                       -- to:   = t2.id
               ... );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.