tableA:
FA FB
1 AA
2 BB
3 CCtableB:
FA FB
1 3
2 4select a.FA, a.FB, b.FB from tableA as a, tableB as b where a.FA = b.FB
结果
FA FB FB
1 AA 3
2 BB 4想知道如何能使结果为
FA FB FB
1 AA 3
2 BB 4
3 CC 0
FA FB
1 AA
2 BB
3 CCtableB:
FA FB
1 3
2 4select a.FA, a.FB, b.FB from tableA as a, tableB as b where a.FA = b.FB
结果
FA FB FB
1 AA 3
2 BB 4想知道如何能使结果为
FA FB FB
1 AA 3
2 BB 4
3 CC 0
from tableA as a left jion tableB as b
on a.FA = b.FB
IF OBJECT_ID('[tableA]') IS NOT NULL
DROP TABLE [tableA]
GO
CREATE TABLE [tableA] ([FA] [int],[FB] [nvarchar](10))
INSERT INTO [tableA]
SELECT '1','AA' UNION ALL
SELECT '2','BB' UNION ALL
SELECT '3','CC'--> 生成测试数据表: [tableB]
IF OBJECT_ID('[tableB]') IS NOT NULL
DROP TABLE [tableB]
GO
CREATE TABLE [tableB] ([FA] [int],[FB] [int])
INSERT INTO [tableB]
SELECT '1','3' UNION ALL
SELECT '2','4'--SELECT * FROM [tableA]
--SELECT * FROM [tableB]-->SQL查询如下:
SELECT a.*,ISNULL(b.FB,0) FB
FROM tableA a
LEFT JOIN tableB b
ON a.FA = b.FA
/*
FA FB FB
----------- ---------- -----------
1 AA 3
2 BB 4
3 CC 0(3 行受影响)*/