不会啊,有空值的行照样输出的。 with a(id,c1,c2) AS ( SELECT 1,1,2 UNION ALL SELECT 2,2,NULL UNION ALL SELECT 3,NULL,2 UNION ALL SELECT 4,2,3 ) ,b (id,name) AS ( SELECT 1,'A' UNION ALL SELECT 2,'B' ) SELECT a.id, a.c1, (SELECT b.name FROM b WHERE b.id = a.c1) name1, a.c2, (SELECT b.name FROM b WHERE b.id = a.c2) name2 FROM a id c1 name1 c2 name2 ----------- ----------- ----- ----------- ----- 1 1 A 2 B 2 2 B NULL NULL 3 NULL NULL 2 B 4 2 B 3 NULL
with a(id,c1,c2) AS (
SELECT 1,1,2 UNION ALL
SELECT 2,2,NULL UNION ALL
SELECT 3,NULL,2 UNION ALL
SELECT 4,2,3
)
,b (id,name) AS (
SELECT 1,'A' UNION ALL
SELECT 2,'B'
)
SELECT a.id,
a.c1,
(SELECT b.name FROM b WHERE b.id = a.c1) name1,
a.c2,
(SELECT b.name FROM b WHERE b.id = a.c2) name2
FROM a
id c1 name1 c2 name2
----------- ----------- ----- ----------- -----
1 1 A 2 B
2 2 B NULL NULL
3 NULL NULL 2 B
4 2 B 3 NULL