mysql - 当右表中没有对应行时，左连接返回所有 NULL 值

Question

我有以下查询，并且子查询在左连接时产生奇怪的结果。这是发生的情况(StoreID 1 的行在连接表中返回所有 null)以及我真正想要的:

TABLE A                     
StoreID unitsales1  unitsales2  unitsales3          
1       NULL        NULL        6           
2       12          12          12          
3       12          NULL        12          

TABLE B                     
StoreID prevunitsales1  prevunitsales2  prevunitsales3          
2       NULL            NULL            6           
3       12              12              12          

我得到了什么

LEFT JOIN ON A.StoreId = B.StoreId                      
StoreID unitsales1  unitsales2  unitsales3  prevunitsales1  prevunitsales2  prevunitsales3
1       NULL        NULL        NULL        NULL        NULL        NULL
2       12          12          12          NULL        NULL        6
3       12          NULL        12          12          12          12

我想要什么

LEFT JOIN ON A.StoreId = B.StoreId                      
StoreID unitsales1  unitsales2  unitsales3  prevunitsales1  prevunitsales2  prevunitsales3
1       NULL        NULL        6           NULL        NULL        NULL
2       12          12          12          NULL        NULL        6
3       12          NULL        12          12          12          12

这是查询:

 SELECT SQL_CALC_FOUND_ROWS @storeid:=z.id,z.biz_name, z.wf_store_name, z.e_address,  z.e_city, z.e_state, z.e_postal, IFNULL(total_sales - prev_total_sales,'CV') as diff_total_sales, IFNULL(d_source,'N/A') as d_source,  IFNULL(unit_sales1 - prev_unit_sales1,(SELECT IFNULL(max(datetimesql),'NS') FROM storeCheckRecords WHERE store_id=@storeid AND upc=855555000019)) as diff_unit_sales1, IFNULL(unit_sales2 - prev_unit_sales2,(SELECT IFNULL(max(datetimesql),'NS') FROM storeCheckRecords WHERE store_id=@storeid AND upc=855555000022)) as diff_unit_sales2, IFNULL(unit_sales3 - prev_unit_sales3,(SELECT IFNULL(max(datetimesql),'NS') FROM storeCheckRecords WHERE store_id=@storeid AND upc=855555000025)) as diff_unit_sales3, IFNULL(unit_sales4 - prev_unit_sales4,(SELECT IFNULL(max(datetimesql),'NS') FROM storeCheckRecords WHERE store_id=@storeid AND upc=855555000028)) as diff_unit_sales4 FROM

            (SELECT s1.id,s1.biz_name as biz_name, s1.wf_store_name as wf_store_name, s1.e_address as e_address,s1.e_city as e_city,s1.e_state as e_state,s1.e_postal as e_postal,sum(s2.unit_sales) as total_sales, sum(s2.unit_sales/4.28571428571) as week_avg,group_concat(DISTINCT s2.d_source separator ',') as d_source,
             SUM(CASE u.id WHEN 1 THEN s2.unit_sales ELSE NULL END) AS unit_sales1,SUM(CASE u.id WHEN 2 THEN s2.unit_sales ELSE NULL END) AS unit_sales2,SUM(CASE u.id WHEN 3 THEN s2.unit_sales ELSE NULL END) AS unit_sales3,SUM(CASE u.id WHEN 4 THEN s2.unit_sales ELSE NULL END) AS unit_sales4
             FROM allStores as s1
             INNER JOIN storeCheckRecords AS s2
             ON s1.id = s2.store_id
             AND s2.datetimesql BETWEEN '2015-07-01' AND '2015-07-31'
              AND s1.key_retailer LIKE 'FRESH THYME' 


             INNER JOIN ( SELECT 1 AS id, '855555000019' AS upc UNION SELECT 2, '855555000022' UNION SELECT 3, '855555000025' UNION SELECT 4, '855555000028'  ) u ON u.upc = s2.upc  
             GROUP BY 
                s1.id) x

            LEFT OUTER JOIN

            (SELECT s1.id,s1.biz_name as prev_biz_name, s1.wf_store_name as prev_wf_store_name, s1.e_address as prev_e_address,s1.e_city as prev_e_city,s1.e_state as prev_e_state,s1.e_postal as prev_e_postal,sum(s2.unit_sales) as prev_total_sales, sum(s2.unit_sales/4.28571428571) as prev_week_avg,group_concat(DISTINCT s2.d_source separator ',') as prev_d_source,
             SUM(CASE u.id WHEN 1 THEN s2.unit_sales ELSE 0 END) AS prev_unit_sales1,SUM(CASE u.id WHEN 2 THEN s2.unit_sales ELSE 0 END) AS prev_unit_sales2,SUM(CASE u.id WHEN 3 THEN s2.unit_sales ELSE 0 END) AS prev_unit_sales3,SUM(CASE u.id WHEN 4 THEN s2.unit_sales ELSE 0 END) AS prev_unit_sales4
             FROM allStores as s1
             INNER JOIN storeCheckRecords AS s2
             ON s1.id = s2.store_id
             AND s2.datetimesql BETWEEN '2015-06-01' AND '2015-06-30'
              AND s1.key_retailer LIKE 'FRESH THYME' 


             INNER JOIN ( SELECT 1 AS id, '855555000019' AS upc UNION SELECT 2, '855555000022' UNION SELECT 3, '855555000025' UNION SELECT 4, '855555000028'  ) u ON u.upc = s2.upc  
             GROUP BY 
                s1.id) y

            ON x.id = y.id

            RIGHT JOIN

            (SELECT s1.id,s1.biz_name,s1.wf_store_name,s1.e_address,s1.e_city,s1.e_state,s1.e_postal
            FROM allStores as s1
            WHERE 1
             AND s1.key_retailer LIKE 'FRESH THYME') z

            ON y.id = z.id

             ORDER BY biz_name ASC
             LIMIT 0, 1000

我错过了什么？

Answer 1

使用 == 比较 NULL 与 NULL 永远不会匹配，这就是为什么 table2 中没有行与 table1 的第一行相匹配。

您可以使用<==>相反，如果你真的想这样做。

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL