IN 조건의 이상한점? 0 2 1,319

by DISTINCT [SQL Query] [2017.04.04 11:03:25]


                WITH T AS(
                        SELECT NULL AS DT  FROM DUAL UNION ALL
                        SELECT NULL AS DT  FROM DUAL UNION ALL
                        SELECT ''   AS DT  FROM DUAL UNION ALL
                        SELECT ''   AS DT  FROM DUAL UNION ALL
                        SELECT '-'  AS DT  FROM DUAL UNION ALL
                        SELECT '-'  AS DT  FROM DUAL UNION ALL
                        SELECT 'A'  AS DT  FROM DUAL UNION ALL
                        SELECT 'B'  AS DT  FROM DUAL UNION ALL
                        SELECT 'C'  AS DT  FROM DUAL UNION ALL
                        SELECT 'D'  AS DT  FROM DUAL UNION ALL
                        SELECT 'E'  AS DT  FROM DUAL )
                SELECT DT FROM T
                WHERE NVL(DT,'-') = '-';

 

                WITH T AS(
                        SELECT NULL AS DT  FROM DUAL UNION ALL
                        SELECT NULL AS DT  FROM DUAL UNION ALL
                        SELECT ''   AS DT  FROM DUAL UNION ALL
                        SELECT ''   AS DT  FROM DUAL UNION ALL
                        SELECT '-'  AS DT  FROM DUAL UNION ALL
                        SELECT '-'  AS DT  FROM DUAL UNION ALL
                        SELECT 'A'  AS DT  FROM DUAL UNION ALL
                        SELECT 'B'  AS DT  FROM DUAL UNION ALL
                        SELECT 'C'  AS DT  FROM DUAL UNION ALL
                        SELECT 'D'  AS DT  FROM DUAL UNION ALL
                        SELECT 'E'  AS DT  FROM DUAL )
                SELECT DT FROM T
                WHERE DT IN ( DT ,'')

 

위 두 쿼리의 결과과 다른데....왜 그런건지 이유좀 알수 있을까요?;;; ㅠㅠ

               


 

by 랑에1 [2017.04.04 11:21:13]

오라클에서 null은 equal(=) 로 비교할 수 없어서 질문자님이 원하시는 결과가 안나온거예요.


by 마농 [2017.04.04 11:33:22]

대부분의 컴퓨터 프로그래밍에서 NULL 과 '' 을 다르게 인식하는데.
오라클에서는 특이하게도 '' 을 NULL 로 인식합니다.
NULL 은 값이 아니므로 비교식에서 사용하면 결과는 항상 거짓(False)입니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입