MS SQL Server LEFT JOIN没有产生预期的结果

Question

我正试图在两个表上做一个LEFT JOIN，但我不确定为什么LEFT JOIN不像预期的那样工作。

select * from table1

<表类>dealer_idorg_idproduct_idzipcode数量tbody><<tr>200111100122001121004520011210018200111100982002131001820021110099

看起来你的SQL没有正确形成…

DECLARE @table1 TABLE
(
dealer_id VARCHAR(10)
, org_id INT 
, product_id INT
, zipcode VARCHAR(10)
, qty INT
)
INSERT INTO @table1 VALUES(2001,1,1,1001,2)
INSERT INTO @table1 VALUES(2001,1,2,1004,5)
INSERT INTO @table1 VALUES(2001,1,2,1001,8)
INSERT INTO @table1 VALUES(2001,1,1,1009,8)
INSERT INTO @table1 VALUES(2002,1,3,1001,8)
INSERT INTO @table1 VALUES(2002,1,1,1009,9)
DECLARE @table2 TABLE
(
org_id INT 
, product_id INT
, zipcode VARCHAR(10)
, customer_id INT
)
INSERT INTO @table2 VALUES(1,1,1001,23)
INSERT INTO @table2 VALUES(1,2,1004,34)
INSERT INTO @table2 VALUES(1,2,1001,22)
INSERT INTO @table2 VALUES(2,1,9001,23)
INSERT INTO @table2 VALUES(1,3,1001,45)
SELECT a.dealer_id, 
a.org_id, 
a.product_id, 
b.customer_id,
a.zipcode, 
b.zipcode AS mapped_code, 
a.qty
FROM @table1 a
LEFT JOIN @table2 b ON a.org_id = b.org_id 
AND a.product_id = b.product_id 
AND a.zipcode = b.zipcode

如果使用LEFT JOIN，将返回左表的所有行。但是，在Right表中，返回匹配的记录。你的结果是正确的