如何使用NULL值连接多个表 [英] How to JOIN multiple tables with NULL values

问题描述

我正在尝试连接4个表，其中一个表没有所有匹配的ID但是我仍然需要显示连接的结果，甚至是没有相应ID的行。



以下是我所说的一个例子：



示例表：

 DECLARE @ Table1 TABLE（id INT PRIMARY KEY CLUSTERED，ts DateTime，tbl2_id INT，price DECIMAL（4,2），tbl3_id int，tbl4_id int）
 
 INSERT INTO @ Table1 VALUES（1，'2013-07-25 09:30:00'，10,10.25,1）; 
 INSERT INTO @ Table1 VALUES（2，'2013-07-25 10:25:00'，20,25.25,1）; 
 INSERT INTO @ Table1 VALUES（3，'2013-07-25 11:45:00'，30,30.15,2）; 
 INSERT INTO @ Table1 VALUES（4，'2013-07-25 13:31:00'，40,80.40,2）; 
 
 DECLARE @ Table2 TABLE（id INT PRIMARY KEY CLUSTERED，symbol VARCHAR（25），tbl1_id int）
 
 INSERT INTO @ Table2 VALUES（10，'XYZ'，1） ; 
 INSERT INTO @ Table2 VALUES（20，'ABC'，2）; 
 INSERT INTO @ Table2 VALUES（30，'RST'，3）; 
 INSERT INTO @ Table2 VALUES（40，'EFG'，4）; 
 
 DECLARE @ Table3 TABLE（id INT PRIMARY KEY CLUSTERED，exch VARCHAR（25））
 
 INSERT INTO @ Table3 VALUES（1，'A'）; 
 INSERT INTO @ Table3 VALUES（2，'B'）; 
 INSERT INTO @ Table3 VALUES（3，'C'）; 
 INSERT INTO @ Table3 VALUES（4，'D'）; 
 
 DECLARE @ Table4 TABLE（id INT PRIMARY KEY CLUSTERED，int tbl1_id，cnt INT）
 
 INSERT INTO @ Table4 VALUES（1,2,19）; 
 INSERT INTO @ Table4 VALUES（2，4,2013）; 
 

示例查询：

 SELECT tbl1.id，tbl1 .ts，tbl2.symbol，IFNULL（tbl3.cnt，0）AS cnt 
 
 FROM TABLE1 tbl1 
 
 JOIN TABLE2 tbl2 
 ON tbl1.tbl2_id = tbl2。 id 
 
 JOIN TABLE3 tbl3 
 ON tbl3.id = tbl1.tbl3_id 
 
 LEFT OUTER JOIN TABLE4 tbl4 
 ON tbl1.tbl4_id = tbl4.id 
 
 WHERE tbl1.ts BETWEEN'2013-07-25 09:30:00'和'2013-07-25 16:00:00'
 AND tbl1.price> = 15.00 
 LIMIT 1000; 
 

所以基本上我要做的是如果tbl4没有tbl1_id我仍然想要从table1看到结果但是显示Cnt的0值...当我运行这个查询时，我得到一堆重复的条目，数据看起来不对。

解决方案

检查，像这样，这里我检查ifnull是否为空值的id。

  SELECT  tbl1.id，tbl1.ts，tbl2.symbol，IFNULL（tbl3.cnt， 0 ） AS  cnt 
 
  FROM  TABLE1 tbl1 
 
  JOIN  TABLE2 tbl2 
  ON  tbl1.tbl2_id = tbl2.id 
 
  JOIN  TABLE3 tbl3 
  ON  tbl3.id = tbl1.tbl3_id 
 
  LEFT   OUTER   JOIN  TABLE4 tbl4 
  ON  IFNULL（tbl1.tbl4_id， 0 ）= IFNULL（tbl4.id， 0 ）
 
  WHERE  tbl1.ts  BETWEEN  '  2013-07-25 09:30:00'  AND  '  2013-07-25 16:00:00' 
  AND  tbl1.price> =  15 。 00  
 LIMIT  1000 ; 
 
 
< pre lang =   SQL> 

I'm trying to join 4 tables and one of the tables doesn't have all the matching ID's BUT I still need to show the results of the join and even for the rows that didn't have a corresponding ID.

Here's an example of what I'm talking about:

Example tables:

DECLARE @Table1 TABLE (id INT PRIMARY KEY CLUSTERED, ts DateTime, tbl2_id INT, price DECIMAL(4,2), tbl3_id int, tbl4_id int)

   INSERT INTO @Table1 VALUES(1, '2013-07-25 09:30:00', 10, 10.25, 1);
   INSERT INTO @Table1 VALUES(2, '2013-07-25 10:25:00', 20, 25.25, 1);
   INSERT INTO @Table1 VALUES(3, '2013-07-25 11:45:00', 30, 30.15, 2);
   INSERT INTO @Table1 VALUES(4, '2013-07-25 13:31:00', 40, 80.40, 2);

   DECLARE @Table2 TABLE (id INT PRIMARY KEY CLUSTERED, symbol VARCHAR(25), tbl1_id int)

   INSERT INTO @Table2 VALUES(10, 'XYZ', 1);
   INSERT INTO @Table2 VALUES(20, 'ABC', 2);
   INSERT INTO @Table2 VALUES(30, 'RST', 3);
   INSERT INTO @Table2 VALUES(40, 'EFG', 4);

   DECLARE @Table3 TABLE (id INT PRIMARY KEY CLUSTERED, exch VARCHAR(25))

   INSERT INTO @Table3 VALUES(1, 'A');
   INSERT INTO @Table3 VALUES(2, 'B');
   INSERT INTO @Table3 VALUES(3, 'C');
   INSERT INTO @Table3 VALUES(4, 'D');

   DECLARE @Table4 TABLE (id INT PRIMARY KEY CLUSTERED, int tbl1_id, cnt INT)

   INSERT INTO @Table4 VALUES(1, 2, 19);
   INSERT INTO @Table4 VALUES(2, 4, 2013);

Example query:

SELECT tbl1.id, tbl1.ts, tbl2.symbol, IFNULL(tbl3.cnt,0) AS cnt

   FROM TABLE1 tbl1

   JOIN TABLE2 tbl2
   ON tbl1.tbl2_id = tbl2.id

   JOIN TABLE3 tbl3
   ON tbl3.id = tbl1.tbl3_id

   LEFT OUTER JOIN TABLE4 tbl4
   ON tbl1.tbl4_id = tbl4.id

   WHERE tbl1.ts BETWEEN '2013-07-25 09:30:00' AND '2013-07-25 16:00:00'
   AND tbl1.price >= 15.00
   LIMIT 1000;

So basically what I'm trying to do is if tbl4 doesn't have a tbl1_id I'd still want to see the result from table1 BUT show a 0 value for Cnt...when i run this query I'm getting a bunch of duplicate entries and the data isn't looking right.

解决方案

Hi,

Check ,like this ,Here i have check ifnull for the id which has null value.

SELECT tbl1.id, tbl1.ts, tbl2.symbol, IFNULL(tbl3.cnt,0) AS cnt
 
    FROM TABLE1 tbl1
 
    JOIN TABLE2 tbl2
    ON tbl1.tbl2_id = tbl2.id
 
    JOIN TABLE3 tbl3
    ON tbl3.id = tbl1.tbl3_id
 
    LEFT OUTER JOIN TABLE4 tbl4
    ON IFNULL(tbl1.tbl4_id,0) = IFNULL(tbl4.id,0)
 
    WHERE tbl1.ts BETWEEN '2013-07-25 09:30:00' AND '2013-07-25 16:00:00'
    AND tbl1.price >= 15.00
    LIMIT 1000;


<pre lang="SQL">

这篇关于如何使用NULL值连接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！