我想在MySQL中进行完全的外部连接。这可能吗?MySQL是否支持完全外部联接?
当前回答
您可以只转换一个完整的外部联接,例如。
SELECT fields
FROM firsttable
FULL OUTER JOIN secondtable ON joincondition
进入:
SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields (replacing any fields from firsttable with NULL)
FROM secondtable
WHERE NOT EXISTS (SELECT 1 FROM firsttable WHERE joincondition)
或者,如果firsttable中至少有一列(比如foo)不为NULL,则可以执行以下操作:
SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields
FROM firsttable
RIGHT JOIN secondtable ON joincondition
WHERE firsttable.foo IS NULL
其他回答
在SQLite中,您应该执行以下操作:
SELECT *
FROM leftTable lt
LEFT JOIN rightTable rt ON lt.id = rt.lrid
UNION
SELECT lt.*, rl.* -- To match column set
FROM rightTable rt
LEFT JOIN leftTable lt ON lt.id = rt.lrid
前面的答案实际上都不正确,因为当存在重复值时,它们不遵循语义。
对于查询,例如(来自此副本):
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;
正确的等效值为:
SELECT t1.*, t2.*
FROM (SELECT name FROM t1 UNION -- This is intentionally UNION to remove duplicates
SELECT name FROM t2
) n LEFT JOIN
t1
ON t1.name = n.name LEFT JOIN
t2
ON t2.name = n.name;
如果需要使用NULL值(这可能也是必要的),则使用NULL安全比较运算符<=>而不是=。
使用交叉联接解决方案:
SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2
ON 1=1;
Use:
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;
可以按如下方式重新创建:
SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
LEFT JOIN t1 ON t1.id = tmp.id
LEFT JOIN t2 ON t2.id = tmp.id;
使用UNION或UNION ALL答案不能涵盖基表具有重复条目的边缘情况。
说明:
UNION或UNION ALL无法覆盖边缘情况。我们无法在MySQL上测试这一点,因为它不支持完整的外部连接,但我们可以在一个支持它的数据库上说明这一点:
WITH cte_t1 AS
(
SELECT 1 AS id1
UNION ALL SELECT 2
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
),
cte_t2 AS
(
SELECT 3 AS id2
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
)
SELECT * FROM cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;
这给了我们这样的答案:
id1 id2
1 NULL
2 NULL
NULL 3
NULL 4
5 5
6 6
6 6
6 6
6 6
UNION解决方案:
SELECT * FROM cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
UNION
SELECT * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
给出错误答案:
id1 id2
NULL 3
NULL 4
1 NULL
2 NULL
5 5
6 6
UNION ALL解决方案:
SELECT * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2
UNION ALL
SELECT * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
也不正确。
id1 id2
1 NULL
2 NULL
5 5
6 6
6 6
6 6
6 6
NULL 3
NULL 4
5 5
6 6
6 6
6 6
6 6
鉴于此查询:
SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
LEFT JOIN t1 ON t1.id = tmp.id
LEFT JOIN t2 ON t2.id = tmp.id;
给出以下内容:
id1 id2
1 NULL
2 NULL
NULL 3
NULL 4
5 5
6 6
6 6
6 6
6 6
顺序不同,但在其他方面与正确答案匹配。
巴勃罗·圣克鲁斯给出的答案是正确的;然而,如果有人无意中看到了这一页,并希望得到更多的澄清,这里有一个详细的分类。
示例表
假设我们有以下表格:
-- t1
id name
1 Tim
2 Marta
-- t2
id name
1 Tim
3 Katarina
内连接
内部连接,如下所示:
SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
将只获取两个表中出现的记录,如下所示:
1 Tim 1 Tim
内部连接没有方向(如左或右),因为它们是显式双向的——我们需要两边都匹配。
外连接
另一方面,外部联接用于查找其他表中可能不匹配的记录。因此,您必须指定允许连接的哪一侧缺少记录。
LEFT JOIN和RIGHT JOIN是LEFT OUTER JOIN和RIGHT OUTER JON的简写;我将在下面使用它们的全名来强化外部连接与内部连接的概念。
左侧外部连接
左外连接,如下所示:
SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
…将从左表中获取所有记录,无论它们在右表中是否匹配,如下所示:
1 Tim 1 Tim
2 Marta NULL NULL
右侧外部连接
右外部连接,如下所示:
SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
…将从右表中获取所有记录,无论它们在左表中是否匹配,如下所示:
1 Tim 1 Tim
NULL NULL 3 Katarina
完全外部联接
一个完整的外部联接将为我们提供两个表中的所有记录,无论它们在另一个表中是否匹配,在没有匹配的两侧都有NULL。结果如下:
1 Tim 1 Tim
2 Marta NULL NULL
NULL NULL 3 Katarina
然而,正如Pablo Santa Cruz所指出的,MySQL不支持这一点。我们可以通过执行左联接和右联接的UNION来模拟它,如下所示:
SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
UNION
SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
您可以将UNION看作是“运行这两个查询,然后将结果叠加在一起”;其中一些行来自第一个查询,一些来自第二个查询。
需要注意的是,MySQL中的UNION将消除完全的重复:Tim将出现在这里的两个查询中,但UNION的结果只列出了他一次。我的数据库专家同事认为不应该依赖这种行为。因此,为了更加明确,我们可以在第二个查询中添加WHERE子句:
SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
UNION
SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;
另一方面,如果出于某种原因希望看到重复项,可以使用UNION ALL。
推荐文章
- 将值从同一表中的一列复制到另一列
- GROUP BY with MAX(DATE)
- 删除id与其他表不匹配的sql行
- 等价的限制和偏移SQL Server?
- MySQL CPU使用率高
- 是什么导致这个ActiveRecord::ReadOnlyRecord错误?
- INT和VARCHAR主键之间有真正的性能差异吗?
- 拒绝访问;您需要(至少一个)SUPER特权来执行此操作
- 为什么我不能在DELETE语句中使用别名?
- 在SQL Server Management Studio中保存带有标题的结果
- 从存储引擎得到错误28
- "where 1=1"语句
- 如何选择一个记录和更新它,与一个单一的查询集在Django?
- 多语句表值函数vs内联表值函数
- 如何从Oracle的表中获取列名?