同一張表上只有一個別名的相關子查詢

我有這樣的查詢

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

我知道在這個例子中我可以結合這兩個WHERE子句並且不使用子查詢，但這不是重點。

該查詢在我正在使用的 DBMS（SQL Anywhere 16）上完美執行，但我想知道id子查詢中my_table.id從主查詢引用的事實是否是標準行為，或者我只是幸運。

連結或參考 RFC 或任何官方文件表示讚賞:)

不，這是錯誤的。標準行為是在WHERE id = ...子查詢中id引用範圍內最近的表。那就是t2。my_table只有t2在沒有id列的情況下才會在外部查詢中引用。

不過你很幸運，因為WHERE id = t2.id- 被翻譯為WHERE t2.id = t2.id實際上並不需要，因為你使用了WHERE id IN (subquery). 如果你有一個EXISTS 版本，它不會像你預期的那樣工作。

因此，編寫查詢的正確方法是為所有列引用添加前綴：

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 
              ... );

或者非常小心，僅在需要時添加前綴：

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 
                ... );

我仍然更喜歡EXISTS此類查詢的版本：

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
              ... );

或（如果您總是非常小心）：

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
              ... );

引用自：https://dba.stackexchange.com/questions/162414