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

请参阅简单的http://sqlfiddle.com/#!9/e853f/1,以了解运行中的这个问题。

我指的是MySQL版本5.6.12-log

据我了解,左连接返回NULL为最右边的数据集中的列,左数据集中的键不存在于右数据集中。

但是,即使左侧键不存在于右侧,我也会从右侧返回数据。

任何人都可以解释这里发生了什么?

SQLfiddle创建:

  • 一个包含6行的表,每个表只包含一个整数ID
  • 第三个表格包含3行,其中包含一些整数ID和两个更多的INT字段
  • 基于第二个表的视图,该表返回包含整数ID的3行和从其他两个INT字段派生的文本字段
  • (显然,视图中的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 )!
  • 通常情况下,从真正的表中取出的NULLJOIN匹配)和NULL填充因为JOIN不匹配而没有区别。 CASE + IFNULL只是查找NULL并将它们交换为0秒(不管它们的来源)。 这就是为什么即使在不匹配的行中你也会在状态栏中得到结果。

    事实上,如果你想知道你正在查看的给定的NULL是否是不匹配JOIN的结果,你需要明确地检查这个 - 如果你JOIN所有关键字段都是NULL ,当NULL在这一列是填充的结果。 如果来自键的字段存在于此行中,但其他列中仍然存在NULL ,那么它就在那里,因为它是从您JOIN编辑的表中提取的。

    链接地址: http://www.djcxy.com/p/41433.html

    上一篇: Why does this simple Left Join return data from unmatched rows?

    下一篇: Rolling pivot table in python