首页 文章

MySQL：使用具有外键到公共基表的两个表表达完全连接

提问于
浏览 次
0

假设我们需要在table1-3上执行两个 FULL JOIN （在PostgreSQL术语中），其中table2和table3都有指向table1的外键 . 这应该在MySQL中工作，其中 FULL JOIN 不可用 . 因此必须找到解决方法 .

从 LEFT JOIN 开始，以下代码的工作方式如(1)所述：

SELECT table1.ID, table1.x, table2.y, table3.z
   FROM (table1
      LEFT JOIN table2
         ON table1.ID = table2.FOREIGN_ID)
      LEFT JOIN table3
         ON table1.ID = table3.FOREIGN_ID

在(2)之后，我能够使用 UNION 语句模拟一个完整的外连接，用于单个连接：

SELECT table1.ID, table1.x, table2.y
   FROM table1
      LEFT JOIN table2
         ON table1.ID = table2.FOREIGN_ID
UNION
SELECT table1.ID, table1.x, table2.y
   FROM table1
      RIGHT JOIN table2
         ON table1.ID = table2.FOREIGN_ID

生产环境

enter image description here

和

SELECT table1.ID, table1.x, table3.z
   FROM table1
       LEFT JOIN table3
         ON table1.ID = table3.FOREIGN_ID
UNION
SELECT table1.ID, table1.x, table3.z
   FROM table1
       RIGHT JOIN table3
         ON table1.ID = table3.FOREIGN_ID
ORDER BY ID

生产环境

enter image description here

我需要有关如何从此处获取此输出的建议：

resulting table

也就是说，通过外键引用table1中的条目的结果行应该合理地连接，而table2和table3（具有外键NULL的那些）的杂散条目应该列在额外的行中（示例中为y2和z4） .

mysql sql join outer-join full-outer-join

1 回答

  • 0

    原来在PostgreSQL中这将是一个微不足道的两步 FULL JOIN ：

    SELECT table1.id, table1.x, table2.y, table3.z
  FROM table1
  FULL JOIN table2
    ON table2.Foreign_Id = table1.id
  FULL JOIN table3
    ON table3.Foreign_Id = table1.id
 ORDER BY table1.id, table2.id, table3.id;

    来自freenode #sql的人们指出这个等效的MySQL查询，避免使用 FULL JOIN ：

    SELECT id1, x, y, z FROM (
SELECT table1.id as id1, table1.x, table2.id as id2, table2.y, table3.id as id3, table3.z
  FROM table1
  LEFT JOIN table2
    ON table2.Foreign_Id = table1.id
  LEFT JOIN table3
    ON table3.Foreign_Id = table1.id
 UNION ALL
SELECT NULL, NULL, table2.id, table2.y, NULL, NULL
  FROM table2 WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = table2.Foreign_Id)
 UNION ALL
SELECT NULL, NULL, NULL, NULL, table3.id, table3.z
  FROM table3 WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = table3.Foreign_Id)
 ORDER BY id1, id2, id3 ) AS T
    回复于

相关问题