mysql - 如何使用 MySQL 进行多重连接？

Question

假设我有三个处于直接 OneToMany 关系中的表。

grandparent TABLE
-----------------
id             PK AI

parent      TABLE
-----------------
id             PK AI
grandparent_id FK ManyToOne NOT NULL

child       TABLE
-----------------
id             PK AI
parent_id      FK ManyToOne NOT NULL

当我想在选择 child 的同时选择 parent 和祖 parent 时，以下两个查询哪个正确？

SELECT * FROM child AS c
    INNER JOIN (parent AS p INNER JOIN grandparent AS g ON p.grandparent_id = g.id)
    ON c.parent_id = p.id

SELECT * FROM child AS c
    INNER JOIN parent AS p ON c.parent_id = p.id
    INNER JOIN grandparent AS g ON p.grandparent_id = g.id

它们的内部和外部相同吗？

Answer 1

我会离开连接两次，从祖 parent 到 parent ，然后到 child 。其背后的动机是，并非所有祖先都有 child ，但您可能仍希望将他们包含在报告中。

SELECT
    t1.ID AS grandparent_id
    CASE WHEN t2.ID IS NOT NULL
         THEN CAST(t2.ID AS VARCHAR) ELSE 'NA' END AS parent_id,
    CASE WHEN t3.ID IS NOT NULL
         THEN CAST(t3.ID AS VARCHAR) ELSE 'NA' END AS child_id
FROM grandparent t1
LEFT JOIN parent t2
    ON t1.ID = t2.grandparent_id
LEFT JOIN child t3
    ON t2.ID = t3.parent_id
ORDER BY
    t1.ID, t2.ID, t3.ID;