代码之家 › 专栏 › 技术社区 › user2834566

为什么这个简单的左连接从不匹配的行返回数据?

left-join sql mysql

user2834566 · 技术社区 · 7 年前

请看简单的 http://sqlfiddle.com/#!9/e853f/1 针对运行中的此问题。

我参考MySQL版本5.6.12-log

据我所知,对于最右侧数据集中的列,如果左侧数据集中的键在右侧数据集中不存在,则左连接将返回NULL。

然而,即使右侧不存在左手键,我也会从右侧返回数据。

有人能解释一下这是怎么回事吗?

SQLFIDLE创建:

具有6行的表,每行仅包含一个整数ID
第二个表有3行,其中包含一些整数ID加上两个更多整型字段
基于第二个表的视图,该表返回3行,其中包含整数ID和文本字段,该字段派生自其他两个INT字段

(显然,视图中的3个ID对应于6行表中的一些ID。)

SQL 选择*FROM LEFT JOIN ON table\u ID=view\u ID; 按预期返回6行,但 全部的 其中,文本字段中有数据,而不是3个不匹配的数据为空

但是

如果视图中用于派生文本列的方法略有更改,则左连接SQL会给出正确的结果。 (您可以通过在sql FIDLE中有选择地注释掉两个方法中的一个或另一个来显示这一点)

但是,乐观主义者肯定不会首先评估视图,因此,数据是如何创建的并不重要,而是它包含什么?

(这是我先前一个问题的简化版,我承认这个问题太复杂了,不可能给出非法的明智答案)

有人建议(Jeroen Mostert)我展示数据和预期结果。这是:

表人员

personID
--------
   1
   2
   3
   4
   5
   6

查看payment\u状态

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'

3 回复 | 直到 7 年前

The Impaler 7 年前

我不同意其他答案。 这是一个MySQL缺陷。 实际上是bug #83707 在MySQL 5.6中。看起来它在MySQL 5.7中已修复

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>

而且,效果很好!

Pelin 7 年前

视图的处理算法导致此结果。默认情况下,MySQL通常选择MERGE,因为它更高效。如果使用“诱人”算法创建视图,则对于不匹配的行,可以看到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);

Robert Synoradzki 7 年前

这是正常的方式 LEFT JOIN 作品它会将新列附加到结果中,然后用以下内容填充这些列:

从表中提取的值 JOIN 如果 加入 成功,
NULL s如果 加入 不匹配(包括您加入的字段 ON )哦!

通常,两者之间没有区别 空值 从实际表格中提取(其中 加入 匹配)和 空值 因为 加入 不匹配。这个 CASE + IFNULL 只需寻找 空值 并将其交换到 0 s(无论其来源)。这就是为什么你在状态列,即使在不匹配的行中也是如此。

事实上,如果你想知道 空值 您正在查看的是不匹配的结果 加入 ,您需要显式检查此项-如果所有关键字段 加入 在are上编辑 空值 s、当 空值 此列中是填写的结果。如果该行中存在来自键的字段,但仍存在 空值 在另一列中,则是因为它是从您 加入 ed。