1、使用left join時從表的過濾
WITH a AS(
SELECT 'A' aid FROM dual
UNION ALL
SELECT 'B' FROM dual
UNION ALL
SELECT 'C' FROM dual
UNION ALL
SELECT 'D' FROM dual
UNION ALL
SELECT 'E' FROM dual
),
b AS(
SELECT 'A' aid,10 num,1 type FROM dual
UNION ALL
SELECT 'B',20,2 FROM dual
UNION ALL
SELECT 'D',30,1 FROM dual
UNION ALL
SELECT 'E',10 ,1 FROM dual
)
SELECT t1.aid,NVL(t2.num,0) num,'0通過WHERE過濾' 備注
FROM a t1
LEFT JOIN b t2 ON t2.aid = t1.aid
WHERE t2.type = 1
UNION ALL
SELECT t1.aid,NVL(t2.num,0),'1通過JOIN條件過濾'
FROM a t1
LEFT JOIN b t2 ON t2.aid = t1.aid AND t2.type = 1
ORDER BY 3,1,2
結果如下:
2、使用(+)外連接時過濾從表
WITH a AS(
SELECT 'A' aid FROM dual
UNION ALL
SELECT 'B' FROM dual
UNION ALL
SELECT 'C' FROM dual
UNION ALL
SELECT 'D' FROM dual
UNION ALL
SELECT 'E' FROM dual
),
b AS(
SELECT 'A' aid,10 num,1 type FROM dual
UNION ALL
SELECT 'B',20,2 FROM dual
UNION ALL
SELECT 'D',30,1 FROM dual
UNION ALL
SELECT 'E',10 ,1 FROM dual
)
SELECT t1.aid,NVL(t2.num,0) num,'0不用外連接標識過濾'備注
FROM a t1,b t2
WHERE t2.aid(+) = t1.aid
AND t2.type = 1
UNION ALL
SELECT t1.aid,NVL(t2.num,0),'1使用外連接標識過濾'
FROM a t1,b t2
WHERE t2.aid(+) = t1.aid
AND t2.type(+) = 1
ORDER BY 3,1,2
結果如下