为什么这个简单的左连接返回来自不匹配行的数据?
请参阅简单的http://sqlfiddle.com/#!9/e853f/1,以了解运行中的这个问题。
我指的是MySQL版本5.6.12-log
据我了解,左连接返回NULL为最右边的数据集中的列,左数据集中的键不存在于右数据集中。
但是,即使左侧键不存在于右侧,我也会从右侧返回数据。
任何人都可以解释这里发生了什么?
SQLfiddle创建:
(显然,视图中的3个ID对应于6行表中的一些ID。)
SQL SELECT * FROM LEFT JOIN ON table_ID = view_ID; 返回6行如预期,但个个都在文本字段,而不是3周无与伦比的莫过于NULL数据
但
如果视图中用于派生文本列的方法稍有改动,则左连接SQL将提供正确的结果。 (你可以通过选择性地注释掉sql提琴中的两个方法中的一个或另一个来显示)
但是,优化器肯定不会首先评估视图,因此数据的创建方式应该不重要,只是它包含的是什么?
(这是我的一个早期问题的简化版本,我承认它太复杂而不合理的明智答案)
有人(Jeroen Mostert)建议我显示数据和预期结果。 这里是:
表人
personID
--------
1
2
3
4
5
6
查看payment_state
payment_personID | state
----------------------------
1 | 'equal'
2 | 'under'
3 | 'over'
询问
SELECT * FROM person
LEFT JOIN payment_state
ON personID = payment_personID;
预期结果
personID | payment_personID |state
-------------------------------------
1 | 1 | 'equal'
2 | 2 | 'under'
3 | 3 | 'over'
4 | NULL | NULL
5 | NULL | NULL
6 | NULL | NULL
实际结果
personID | payment_personID |state
-------------------------------------
1 | 1 | 'equal'
2 | 2 | 'under'
3 | 3 | 'over'
4 | NULL | 'equal'
5 | NULL | 'equal'
6 | NULL | 'equal'
我恳求不同意其他答案。 这是一个MySQL缺陷。 实际上它是MySQL 5.6中的bug#83707。 它看起来在MySQL 5.7中是固定的
这个bug已经在MariaDB 5.5中修复了。
内部连接策略(如嵌套循环连接,合并连接或散列连接)无关紧要。 无论如何结果都应该是正确的。
我在PostgreSQL和Oracle中尝试了相同的查询,并且它按预期工作,在最后三行返回空值。
Oracle示例
CREATE TABLE person (personID INT);
INSERT INTO person (personID) VALUES (1);
INSERT INTO person (personID) VALUES(2);
INSERT INTO person (personID) VALUES(3);
INSERT INTO person (personID) VALUES(4);
INSERT INTO person (personID) VALUES(5);
INSERT INTO person (personID) VALUES(6);
CREATE TABLE payments (
payment_personID INT,
Due INT,
Paid INT) ;
INSERT INTO payments (payment_personID, due, paid) VALUES (1, 5, 5);
INSERT INTO payments (payment_personID, due, paid) VALUES (2, 5, 3);
INSERT INTO payments (payment_personID, due, paid) VALUES (3, 5, 8);
CREATE VIEW payment_state AS (
SELECT
payment_personID,
CASE
WHEN COALESCE(paid,0) < COALESCE(due,0) AND due <> 0 THEN 'under'
WHEN COALESCE(paid,0) > COALESCE(due,0) THEN 'over'
WHEN COALESCE(paid,0) = COALESCE(due,0) THEN 'equal'
END AS state
FROM payments);
SELECT *
FROM
person
LEFT JOIN
payment_state
ON personID = payment_personID;
结果:
PERSONID PAYMENT_PERSONID STATE
======== ================ =====
1 1 equal
2 2 under
3 3 over
6 <null> <null>
5 <null> <null>
4 <null> <null>
完美的作品!
PostgreSQL示例
CREATE TABLE person (personID INT);
INSERT INTO person (personID) VALUES
(1),(2),(3),(4),(5),(6);
CREATE TABLE payments (
payment_personID INT,
Due INT,
Paid INT) ;
INSERT INTO payments (payment_personID, due, paid) VALUES
(1, 5, 5), (2, 5, 3), (3, 5, 8);
CREATE VIEW payment_state AS (
SELECT
payment_personID,
CASE
WHEN COALESCE(paid,0) < COALESCE(due,0) AND due <> 0 THEN 'under'
WHEN COALESCE(paid,0) > COALESCE(due,0) THEN 'over'
WHEN COALESCE(paid,0) = COALESCE(due,0) THEN 'equal'
END AS state
FROM payments);
SELECT *
FROM
person
LEFT JOIN
payment_state
ON personID = payment_personID;
结果:
personid payment_personid state
======== ================ =====
1 1 equal
2 2 under
3 3 over
4 <null> <null>
5 <null> <null>
6 <null> <null>
此外,完美的作品!
你的视图的处理算法会导致这个结果。 默认情况下,MySQL通常会选择MERGE,因为它更高效。 如果使用“TEMPTABLE”算法创建视图,您将能够看到不匹配行的NULL。
http://www.mysqltutorial.org/create-sql-views-mysql.aspx
CREATE ALGORITHM = TEMPTABLE VIEW payment_state AS (
SELECT
payment_personID,
CASE
WHEN IFNULL(paid,0) < IFNULL(due,0) AND due <> 0 THEN 'under'
WHEN IFNULL(paid,0) > IFNULL(due,0) THEN 'over'
WHEN IFNULL(paid,0) = IFNULL(due,0) THEN 'equal'
END AS state
FROM payments);
这是LEFT JOIN
正常工作的方式。 它将新列添加到结果中,然后填充它们:
JOIN
ED如果JOIN
成功, NULL
■如果JOIN
不匹配(包括你加入域ON
)! 通常情况下,从真正的表中取出的NULL
( JOIN
匹配)和NULL
填充因为JOIN
不匹配而没有区别。 CASE
+ IFNULL
只是查找NULL
并将它们交换为0
秒(不管它们的来源)。 这就是为什么即使在不匹配的行中你也会在状态栏中得到结果。
事实上,如果你想知道你正在查看的给定的NULL
是否是不匹配JOIN
的结果,你需要明确地检查这个 - 如果你JOIN
所有关键字段都是NULL
,当NULL
在这一列是填充的结果。 如果来自键的字段存在于此行中,但其他列中仍然存在NULL
,那么它就在那里,因为它是从您JOIN
编辑的表中提取的。
上一篇: Why does this simple Left Join return data from unmatched rows?