IS [ NOT ] DISTINCT FROM
IS [ NOT ] DISTINCT FROM
IS [ NOT ] DISTINCT FROM 구문은 두 표현식이 같은지(또는 다른지) 비교해요. 이 함수는 NULL 안전(NULL-safe)해서 NULL을 알려진 값으로 취급해 동등성을 비교해요. NULL을 알려진 값으로 취급하지 않는 EQUAL 비교 연산자(=)와 다르다는 점에 유의하세요.
본문
문법
<expr1> IS [ NOT ] DISTINCT FROM <expr2>
사용 메모
반환 값은 입력 중 NULL 값이 있는지에 따라 달라져요.
TRUE를 반환하는 경우:
<null> IS NOT DISTINCT FROM <null><null> IS DISTINCT FROM <not_null><not_null> IS DISTINCT FROM <null>
FALSE를 반환하는 경우:
<null> IS DISTINCT FROM <null><null> IS NOT DISTINCT FROM <not_null><not_null> IS NOT DISTINCT FROM <null>
그 외의 경우:
<expr1> IS DISTINCT FROM <expr2>는<expr1> != <expr2>와 같아요.<expr1> IS NOT DISTINCT FROM <expr2>는<expr1> = <expr2>와 같아요.
자세한 내용은 아래 예제를 참고하세요.
예제
간단한 데이터로 테이블을 만들어요.
CREATE OR REPLACE TABLE x (i number);
INSERT INTO x values (1), (2), (null);
필터 없이 테이블을 자기 자신과 조인해 생성한 카테시안 곱을 표시해요.
SELECT x1.i x1_i, x2.i x2_i FROM x x1, x x2 ORDER BY x1.i, x2.i;
+------+------+
| X1_I | X2_I |
|------+------|
| 1 | 1 |
| 1 | 2 |
| 1 | NULL |
| 2 | 1 |
| 2 | 2 |
| 2 | NULL |
| NULL | 1 |
| NULL | 2 |
| NULL | NULL |
+------+------+
다음을 포함하는 행을 반환해요.
SELECT x1.i x1_i, x2.i x2_i FROM x x1, x x2 WHERE x1.i=x2.i;
+------+------+
| X1_I | X2_I |
|------+------|
| 1 | 1 |
| 2 | 2 |
+------+------+
SELECT x1.i x1_i, x2.i x2_i FROM x x1, x x2 WHERE x1.i IS NOT DISTINCT FROM x2.i ORDER BY x1.i;
+------+------+
| X1_I | X2_I |
|------+------|
| 1 | 1 |
| 2 | 2 |
| NULL | NULL |
+------+------+
모든 가능한 결과를 설명해요.
SELECT x1.i x1_i, x2.i x2_i, x1.i=x2.i, iff(x1.i=x2.i, 'Selected', 'Not') "SELECT IF X1.I=X2.I", x1.i<>x2.i, iff(not(x1.i=x2.i), 'Selected', 'Not') "SELECT IF X1.I<>X2.I" FROM x x1, x x2;
+------+------+-----------+---------------------+------------+----------------------+
| X1_I | X2_I | X1.I=X2.I | SELECT IF X1.I=X2.I | X1.I<>X2.I | SELECT IF X1.I<>X2.I |
|------+------+-----------+---------------------+------------+----------------------|
| 1 | 1 | True | Selected | False | Not |
| 1 | 2 | False | Not | True | Selected |
| 1 | NULL | NULL | Not | NULL | Not |
| 2 | 1 | False | Not | True | Selected |
| 2 | 2 | True | Selected | False | Not |
| 2 | NULL | NULL | Not | NULL | Not |
| NULL | 1 | NULL | Not | NULL | Not |
| NULL | 2 | NULL | Not | NULL | Not |
| NULL | NULL | NULL | Not | NULL | Not |
+------+------+-----------+---------------------+------------+----------------------+
IS DISTINCT FROM의 모든 가능한 결과를 설명해요.
SELECT x1.i x1_i, x2.i x2_i, x1.i IS NOT DISTINCT FROM x2.i, iff(x1.i IS NOT DISTINCT FROM x2.i, 'Selected', 'Not') "SELECT IF X1.I IS NOT DISTINCT FROM X2.I", x1.i IS DISTINCT FROM x2.i, iff(x1.i IS DISTINCT FROM x2.i, 'Selected', 'Not') "SELECT IF X1.I IS DISTINCT FROM X2.I" FROM x x1, x x2 ORDER BY x1.i, x2.i;
+------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------+
| X1_I | X2_I | X1.I IS NOT DISTINCT FROM X2.I | SELECT IF X1.I IS NOT DISTINCT FROM X2.I | X1.I IS DISTINCT FROM X2.I | SELECT IF X1.I IS DISTINCT FROM X2.I |
|------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------|
| 1 | 1 | True | Selected | False | Not |
| 1 | 2 | False | Not | True | Selected |
| 1 | NULL | False | Not | True | Selected |
| 2 | 1 | False | Not | True | Selected |
| 2 | 2 | True | Selected | False | Not |
| 2 | NULL | False | Not | True | Selected |
| NULL | 1 | False | Not | True | Selected |
| NULL | 2 | False | Not | True | Selected |
| NULL | NULL | True | Selected | False | Not |
+------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------+