sql - 混合显式和隐式连接失败，返回 "There is an entry for table ... but it cannot be referenced from this part of the query"

Question

SELECT
      i.*, 
      r.name AS roomname, 
      c.name AS cat, 
      p.key AS imgkey, 
      p.extension AS imgext
   FROM 
      items i, 
      rooms r, 
      categories c 
         LEFT JOIN photos p 
            ON p.referencekey = i.key 
   WHERE 
          i.room = r.key 
      AND r.key = 663308 
      AND i.sitekey = 32201 
      AND c.key = i.categorykey

上面的查询在执行时返回以下错误。

ERROR: invalid reference to FROM-clause entry for table "i"

LINE 1: ...tegory c LEFT JOIN photos p ON p.referencekey = i.key WHER...

HINT: There is an entry for table "i", but it cannot be referenced from this part of the query.

Answer 1

SQL 规范声明显式连接在隐式连接之前执行。这是一个隐式连接:

FROM table1 t1, table2 t2 WHERE t1.id=t2.t1id

这是一个显式连接:

FROM table1 t1 JOIN table2 t2 ON (t1.id=t2.t1id)

这段代码位:

categories c 
     LEFT JOIN photos p 
        ON p.referencekey = i.key 

是显式连接，首先运行。请注意，此时表别名为 i 还没有被查看，所以它还不能连接。请注意，我相信 MySQL 在 5.2 中修复了此行为，并且此查询也将不再适用。