DECODE在WHERE语句中的妙用
假设我们有两个表,需要用ROW_ID连接,找出值相同或者不相同的数据
WITH TEMP1 AS(
SELECT 1 AS ROW_ID,1 AS COLA FROM DUAL
UNION ALL SELECT 2 ,1 FROM DUAL
UNION ALL SELECT 3 ,NULL FROM DUAL
UNION ALL SELECT 4 ,NULL FROM DUAL
)
SELECT * FROM TEMP1;
WITH TEMP2 AS(
SELECT 1 AS ROW_ID,1 AS COLB FROM DUAL
UNION ALL SELECT 2,NULL FROM DUAL
UNION ALL SELECT 3,1 FROM DUAL
UNION ALL SELECT 4,NULL FROM DUAL
)
SELECT * FROM TEMP2;
两个表的结果集如图:
我们对比较结果的期望如列5,如果:
- 两个比较字段都是NULL,认为是相等
- 两个比较字段其中一个为NULL,另外一个为有效值,认为不等
NULL值的存在有点绕人,
NULL=NULL 结果为NULL
1=NULL结果为NULL
所以必须对当字段可为NULL的时候,比较需要对NULL进行单独的处理
找出COLA和COLB相等的记录
不使用DECODE,必须对NULL进行单独的处理
WITH TEMP1 AS(
SELECT 1 AS ROW_ID,1 AS COLA FROM DUAL
UNION ALL SELECT 2 ,1 FROM DUAL
UNION ALL SELECT 3 ,NULL FROM DUAL
UNION ALL SELECT 4 ,NULL FROM DUAL
)
,TEMP2 AS(
SELECT 1 AS ROW_ID,1 AS COLB FROM DUAL
UNION ALL SELECT 2,NULL FROM DUAL
UNION ALL SELECT 3,1 FROM DUAL
UNION ALL SELECT 4,NULL FROM DUAL
)
SELECT * FROM TEMP1 A
INNER JOIN TEMP2 B
ON A.ROW_ID=B.ROW_ID
WHERE A.COLA=COLB
OR (A.COLA IS NULL AND B.COLB IS NULL) /*必须对NULL进行单独处理*/
使用DECODE替换:
WITH TEMP1 AS(
SELECT 1 AS ROW_ID,1 AS COLA FROM DUAL
UNION ALL SELECT 2 ,1 FROM DUAL
UNION ALL SELECT 3 ,NULL FROM DUAL
UNION ALL SELECT 4 ,NULL FROM DUAL
)
,TEMP2 AS(
SELECT 1 AS ROW_ID,1 AS COLB FROM DUAL
UNION ALL SELECT 2,NULL FROM DUAL
UNION ALL SELECT 3,1 FROM DUAL
UNION ALL SELECT 4,NULL FROM DUAL
)
SELECT * FROM TEMP1 A
INNER JOIN TEMP2 B
ON A.ROW_ID=B.ROW_ID
WHERE DECODE(A.COLA,B.COLB,1,0)=1
结果集如图:
找出COLA和COLB不相等的记录
不使用DECODE,需要对NULL进行一大段处理
WITH TEMP1 AS(
SELECT 1 AS ROW_ID,1 AS COLA FROM DUAL
UNION ALL SELECT 2 ,1 FROM DUAL
UNION ALL SELECT 3 ,NULL FROM DUAL
UNION ALL SELECT 4 ,NULL FROM DUAL
)
,TEMP2 AS(
SELECT 1 AS ROW_ID,1 AS COLB FROM DUAL
UNION ALL SELECT 2,NULL FROM DUAL
UNION ALL SELECT 3,1 FROM DUAL
UNION ALL SELECT 4,NULL FROM DUAL
)
SELECT * FROM TEMP1 A
INNER JOIN TEMP2 B
ON A.ROW_ID=B.ROW_ID
WHERE A.COLA!=COLB
OR (A.COLA IS NULL AND B.COLB IS NOT NULL)
OR (A.COLA IS NOT NULL AND B.COLB IS NULL) /*必须对NULL进行单独处理*/
使用DECODE
WITH TEMP1 AS(
SELECT 1 AS ROW_ID,1 AS COLA FROM DUAL
UNION ALL SELECT 2 ,1 FROM DUAL
UNION ALL SELECT 3 ,NULL FROM DUAL
UNION ALL SELECT 4 ,NULL FROM DUAL
)
,TEMP2 AS(
SELECT 1 AS ROW_ID,1 AS COLB FROM DUAL
UNION ALL SELECT 2,NULL FROM DUAL
UNION ALL SELECT 3,1 FROM DUAL
UNION ALL SELECT 4,NULL FROM DUAL
)
SELECT * FROM TEMP1 A
INNER JOIN TEMP2 B
ON A.ROW_ID=B.ROW_ID
WHERE DECODE(A.COLA,B.COLB,1,0)=0
结果集如图:
请关注个人小站:http://sqlhis.com/