postgresql 多连接语法

Question

SELECT sessions_compare.*
    FROM archive_sessions as f_session, sessions_compare
    LEFT JOIN archive_sessions as s_session ON (s_session.id = sessions_compare.second_session_id)
    LEFT JOIN consols as f_consol ON (f_session.console_id = f_consol.id)

    where sessions_compare.first_session_id = f_session.id

在执行类似get错误之后

ERROR: invalid reference to FROM-clause entry for table "f_session" LINE 13:
LEFT JOIN consols as f_consol ON (f_session.console_id =...

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

当我切换位置并拥有 from 时，例如 sessions_compare，archive_sessions 作为 f_session我得到这样的错误

ERROR: invalid reference to FROM-clause entry for table "sessions_compare" LINE 4: ... archive_sessions as s_session ON (s_session.id = sessions_c...

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

唯一有用的是

SELECT sessions_compare.*
    FROM sessions_compare
    LEFT JOIN archive_sessions as s_session ON (s_session.id = sessions_compare.second_session_id)

    ,archive_sessions as f_session

    LEFT JOIN consols as f_consol ON (f_session.console_id = f_consol.id)
    where sessions_compare.first_session_id = f_session.id

我的问题是这正常吗？？当从需要在 ()

中的表中使用多个连接时，我在 mysql 中的 Postgresql 中很年轻

Answer 1

是的，这是 PostgreSQL 中记录的行为。

In any case JOIN binds more tightly than the commas separating FROM items.

这意味着 PostgreSQL 的行为就像它通过在评估 FROM 子句中的逗号列表之前评估 JOIN 子句来构建其工作表。

最佳实践通常被认为是始终使用 JOIN 子句；永远不要在 FROM 子句中放置多个表名。