다음을 통해 공유

NULL 의미 체계

  • 아티클

적용 대상:예로 표시된 확인 Databricks SQL 예로 표시된 확인 Databricks Runtime

table은 set개의 행으로 구성되며, 각 행에는 columns개의 set가 포함됩니다. column 데이터 형식과 연결되며 엔터티의 특정 특성을 나타냅니다(예: ageperson라는 엔터티의 column). 어떤 경우에는 행이 존재할 때 그 행에 특정한 column 값을 알 수 없는 경우가 있습니다. SQL에서 이러한 values 는 NULL로 표시됩니다. 이 섹션에서는 다양한 연산자, 식 및 기타 SQL 구문에서 NULLvalues 처리의 의미 체계를 자세히 설명합니다.

다음은 person이라고 명명된 table의 schema 레이아웃 및 데이터를 보여 줍니다. 데이터에는 NULLvalues이 agecolumn에 포함되어 있으며, 이 table는 아래 섹션의 다양한 예제에서 사용됩니다.

 Id  Name   Age
 --- -------- ----
 100 Joe      30
 200 Marry    NULL
 300 Mike     18
 400 Fred     50
 500 Albert   NULL
 600 Michelle 30
 700 Dan      50

비교 연산자

Azure Databricks는 >, >=, =, < 및 <=와 같은 표준 비교 연산자를 지원합니다. 피연산자 중 하나 또는 피연산자를 모두 알 수 없거나 NULL인 경우 이러한 연산자의 결과를 알 수 없거나 NULL입니다. 동등함을 비교하기 위해 NULLvalues 간의, Azure Databricks는 널 안전 동등 연산자(<=>)를 제공합니다. 이는 피연산자 중 하나가 NULL일 때 False를 반환하고, 두 피연산자가 모두 NULL일 때 True를 반환합니다. 다음의 table은 하나 또는 양쪽 피연산자가 NULL일 때 비교 연산자의 동작을 보여 줍니다.

왼쪽 피연산자 오른쪽 피연산자 > >= = < <= <=>
NULL 모든 값 NULL NULL NULL NULL NULL False
모든 값 NULL NULL NULL NULL NULL NULL False
NULL NULL NULL NULL NULL NULL NULL True

예제

-- Normal comparison operators return `NULL` when one of the operand is `NULL`.
> SELECT 5 > null AS expression_output;
 expression_output
 -----------------
              null

-- Normal comparison operators return `NULL` when both the operands are `NULL`.
> SELECT null = null AS expression_output;
 expression_output
 -----------------
              null

-- Null-safe equal operator return `False` when one of the operand is `NULL`
> SELECT 5 <=> null AS expression_output;
 expression_output
 -----------------
             false

-- Null-safe equal operator return `True` when one of the operand is `NULL`
> SELECT NULL <=> NULL;
 expression_output
 -----------------
              true
 -----------------

논리 연산자

Azure Databricks는 AND, OR 및 NOT과 같은 표준 논리 연산자를 지원합니다. 이러한 연산자는 Boolean 식을 인수로 사용하고 Boolean 값을 반환합니다.

다음 tables은 하나의 피연산자가 NULL인 경우와 두 피연산자가 모두 NULL인 경우에 논리 연산자의 동작을 보여 줍니다.

왼쪽 피연산자 오른쪽 피연산자 또는 AND
True NULL True NULL
False NULL NULL False
NULL True True NULL
NULL False NULL False
NULL NULL NULL NULL
피연산자 NOT
NULL NULL

예제

-- Normal comparison operators return `NULL` when one of the operands is `NULL`.
> SELECT (true OR null) AS expression_output;
 expression_output
 -----------------
              true

-- Normal comparison operators return `NULL` when both the operands are `NULL`.
> SELECT (null OR false) AS expression_output
 expression_output
 -----------------
              null

-- Null-safe equal operator returns `False` when one of the operands is `NULL`
> SELECT NOT(null) AS expression_output;
 expression_output
 -----------------
              null

식

비교 연산자 및 논리 연산자는 Azure Databricks에서 식으로 처리됩니다. Azure Databricks는 다음과 같이 광범위하게 분류할 수 있는 다른 형식의 식도 지원합니다.

  • Null 불내성 식
  • 값 피연산자를 처리 NULL 할 수 있는 식
    • 이러한 식의 결과는 식 자체에 따라 다릅니다.

Null 불내성 식

Null 불내성 하나 이상의 식 인수가 NULL이고 대부분의 식이 이 범우에 속하는 경우 NULL를 반환합니다.

예제

> SELECT concat('John', null) AS expression_output;
 expression_output
 -----------------
              null

> SELECT positive(null) AS expression_output;
 expression_output
 -----------------
              null

> SELECT to_date(null) AS expression_output;
 expression_output
 -----------------
              null

null 값 피연산자를 처리할 수 있는 식

이 식 클래스는 NULLvalues처리하도록 설계되었습니다. 식의 결과는 식 자체에 따라 달라집니다. 예를 들어 함수 식 isnull는 null 입력에 대해서는 true을 반환하고, null이 아닌 입력 where에 대해서는 false를 반환합니다. 이는 함수 coalesce가 피연산자 list 내에서 첫 번째 비-NULL 값을 반환하는 것과 동일합니다. 그러나 coalesce는 모든 피연산자가 NULL인 경우 NULL를 반환합니다. 다음은 해당 범주의 표현 목록 중에 불완전한 list입니다.

  • COALESCE
  • NULLIF
  • IFNULL
  • NVL
  • NVL2
  • ISNAN
  • NANVL
  • ISNULL
  • ISNOTNULL
  • ATLEASTNNONNULLS
  • IN

예제

> SELECT isnull(null) AS expression_output;
 expression_output
 -----------------
              true

-- Returns the first occurrence of non `NULL` value.
> SELECT coalesce(null, null, 3, null) AS expression_output;
 expression_output
 -----------------
                 3

-- Returns `NULL` as all its operands are `NULL`.
> SELECT coalesce(null, null, null, null) AS expression_output;
 expression_output
 -----------------
              null

> SELECT isnan(null) AS expression_output;
 expression_output
 -----------------
             false

기본 제공 집계 식

집계 함수는 입력 행의 set 처리하여 단일 결과를 계산합니다. 다음은 집계 함수에서 NULLvalues 처리하는 방법에 대한 규칙입니다.

  • NULL values 모든 집계 함수에서 처리에서 무시됩니다.
    • 이 규칙의 유일한 예외는 COUNT(*) 함수입니다.
  • 일부 집계 함수는 모든 입력 valuesNULL 또는 입력 데이터 set 비어 있는 경우 NULL 반환합니다. 이러한 함수의 list 다음과 같습니다.
    • MAX
    • MIN
    • SUM
    • AVG
    • EVERY
    • ANY
    • SOME

예제

-- `count(*)` does not skip `NULL` values.
> SELECT count(*) FROM person;
 count(1)
 --------
        7

-- `NULL` values in column `age` are skipped from processing.
> SELECT count(age) FROM person;
 count(age)
 ----------
          5

-- `count(*)` on an empty input set returns 0. This is unlike the other
-- aggregate functions, such as `max`, which return `NULL`.
> SELECT count(*) FROM person where 1 = 0;
 count(1)
 --------
        0

-- `NULL` values are excluded from computation of maximum value.
> SELECT max(age) FROM person;
 max(age)
 --------
       50

-- `max` returns `NULL` on an empty input set.
> SELECT max(age) FROM person where 1 = 0;
 max(age)
 --------
     null

WHERE, HAVING 및 JOIN 절의 조건식

WHERE, HAVING 연산자는 사용자가 지정한 조건에 따라 행을 필터링합니다. JOIN 연산자는 join 조건에 따라 두 tables 행을 결합하는 데 사용됩니다. 세 연산자 모두에 대해 조건 식은 부울 식이며 True, False 또는 Unknown (NULL)을 반환할 수 있습니다. 조건의 결과가 True이면 "만족"입니다.

예제

-- Persons whose age is unknown (`NULL`) are filtered out from the result set.
> SELECT * FROM person WHERE age > 0;
     name age
 -------- ---
 Michelle  30
     Fred  50
     Mike  18
      Dan  50
      Joe  30

-- `IS NULL` expression is used in disjunction to select the persons
-- with unknown (`NULL`) records.
> SELECT * FROM person WHERE age > 0 OR age IS NULL;
     name  age
 -------- ----
   Albert null
 Michelle   30
     Fred   50
     Mike   18
      Dan   50
    Marry null
      Joe   30

-- Person with unknown(`NULL`) ages are skipped from processing.
> SELECT * FROM person GROUP BY age HAVING max(age) > 18;
 age count(1)
 --- --------
  50        2
  30        2

-- A self join case with a join condition `p1.age = p2.age AND p1.name = p2.name`.
-- The persons with unknown age (`NULL`) are filtered out by the join operator.
> SELECT * FROM person p1, person p2
    WHERE p1.age = p2.age
    AND p1.name = p2.name;
     name age     name age
 -------- --- -------- ---
 Michelle  30 Michelle  30
     Fred  50     Fred  50
     Mike  18     Mike  18
      Dan  50      Dan  50
      Joe  30      Joe  30

-- The age column from both legs of join are compared using null-safe equal which
-- is why the persons with unknown age (`NULL`) are qualified by the join.
> SELECT * FROM person p1, person p2
    WHERE p1.age <=> p2.age
    AND p1.name = p2.name;
     name  age     name  age
 -------- ---- -------- ----
   Albert null   Albert null
 Michelle   30 Michelle   30
     Fred   50     Fred   50
     Mike   18     Mike   18
      Dan   50      Dan   50
    Marry null    Marry null
      Joe   30      Joe   30

집계 연산자(GROUP BY, DISTINCT)

비교 연산자설명한 것처럼 두 NULLvalues 같지 않습니다. 그러나 그룹화 및 고유한 처리를 위해 NULL data이 포함된 두 개 이상의 values는 같은 버킷에 그룹화됩니다. 이 동작은 SQL 표준 및 기타 엔터프라이즈 데이터베이스 관리 시스템을 따릅니다.

예제

-- `NULL` values are put in one bucket in `GROUP BY` processing.
> SELECT age, count(*) FROM person GROUP BY age;
  age count(1)
 ---- --------
 null        2
   50        2
   30        2
   18        1

-- All `NULL` ages are considered one distinct value in `DISTINCT` processing.
> SELECT DISTINCT age FROM person;
  age
 ----
 null
   50
   30
   18

정렬 연산자(ORDER BY 절)

Azure Databricks는 ORDER BY 절에서 null 순서 지정 사양을 지원합니다. Azure Databricks는 null 순서 지정 사양에 따라 모든 NULLvalues를 처음이나 마지막에 배치하여 ORDER BY 절을 처리합니다. 기본적으로 모든 NULLvalues 처음에 배치됩니다.

예제

-- `NULL` values are shown at first and other values
-- are sorted in ascending way.
> SELECT age, name FROM person ORDER BY age;
  age     name
 ---- --------
 null    Marry
 null   Albert
   18     Mike
   30 Michelle
   30      Joe
   50     Fred
   50      Dan

-- Column values other than `NULL` are sorted in ascending
-- way and `NULL` values are shown at the last.
> SELECT age, name FROM person ORDER BY age NULLS LAST;
  age     name
 ---- --------
   18     Mike
   30 Michelle
   30      Joe
   50      Dan
   50     Fred
 null    Marry
 null   Albert

-- Columns other than `NULL` values are sorted in descending
-- and `NULL` values are shown at the last.
> SELECT age, name FROM person ORDER BY age DESC NULLS LAST;
  age     name
 ---- --------
   50     Fred
   50      Dan
   30 Michelle
   30      Joe
   18     Mike
 null    Marry
 null   Albert

Set 연산자 (UNION, INTERSECT, EXCEPT)

NULL values는 set 작업용 문맥에서 동등성을 위해 null 안전 방식으로 비교됩니다. 즉, 행을 비교할 때 두 NULLvalues은 일반 EqualTo(=) 연산자와 달리 동등한 것으로 간주됩니다.

예제

> CREATE VIEW unknown_age AS SELECT * FROM person WHERE age IS NULL;

-- Only common rows between two legs of `INTERSECT` are in the
-- result set. The comparison between columns of the row are done
-- in a null-safe manner.
> SELECT name, age FROM person
    INTERSECT
    SELECT name, age from unknown_age;
   name  age
 ------ ----
 Albert null
  Marry null

-- `NULL` values from two legs of the `EXCEPT` are not in output.
-- This basically shows that the comparison happens in a null-safe manner.
> SELECT age, name FROM person
    EXCEPT
    SELECT age FROM unknown_age;
 age     name
 --- --------
  30      Joe
  50     Fred
  30 Michelle
  18     Mike
  50      Dan

-- Performs `UNION` operation between two sets of data.
-- The comparison between columns of the row ae done in
-- null-safe manner.
> SELECT name, age FROM person
    UNION
    SELECT name, age FROM unknown_age;
     name  age
 -------- ----
   Albert null
      Joe   30
 Michelle   30
    Marry null
     Fred   50
     Mike   18
      Dan   50

EXISTS 및 NOT EXISTS 하위 쿼리

Azure Databricks에서는 EXISTS 절 내에서 NOT EXISTS 및 WHERE 식이 허용됩니다. 이는 TRUE 또는 FALSE를 반환하는 부울 식입니다. 즉, EXISTS는 멤버 자격 조건이며 참조하는 하위 쿼리가 하나 이상의 행을 반환할 때 TRUE를 반환합니다. 마찬가지로 NOT EXISTS는 비 멤버 자격 조건이며 하위 쿼리에서 행 또는 0개의 행이 반환되지 않을 때 TRUE를 반환합니다.

이러한 두 식은 하위 쿼리의 결과에 NULL이 있어도 영향을 받지 않습니다. null 인식에 대한 특별한 프로비저닝 없이 semijoins 및 anti-semijoins로 변환할 수 있기 때문에 일반적으로 더 빠릅니다.

예제

-- Even if subquery produces rows with `NULL` values, the `EXISTS` expression
-- evaluates to `TRUE` as the subquery produces 1 row.
> SELECT * FROM person WHERE EXISTS (SELECT null);
     name  age
 -------- ----
   Albert null
 Michelle   30
     Fred   50
     Mike   18
      Dan   50
    Marry null
      Joe   30

-- `NOT EXISTS` expression returns `FALSE`. It returns `TRUE` only when
-- subquery produces no rows. In this case, it returns 1 row.
> SELECT * FROM person WHERE NOT EXISTS (SELECT null);
 name age
 ---- ---

-- `NOT EXISTS` expression returns `TRUE`.
> SELECT * FROM person WHERE NOT EXISTS (SELECT 1 WHERE 1 = 0);
     name  age
 -------- ----
   Albert null
 Michelle   30
     Fred   50
     Mike   18
      Dan   50
    Marry null
      Joe   30

IN 및 NOT IN 하위 쿼리

Azure Databricks에서는 쿼리의 IN 절 내에서 NOT IN 및 WHERE 식이 허용됩니다. EXISTS 식과 달리 IN 식은 TRUE, FALSE 또는 UNKNOWN (NULL) 값을 반환할 수 있습니다. 개념적으로 IN 표현은OR로 구분된 동등 조건의 set과 의미상 동일합니다. 예를 들어 c1 IN(1, 2, 3)은 의미상 (C1 = 1 OR c1 = 2 OR c1 = 3)과 동일합니다.

NULL values 처리와 관련하여 의미 체계는 비교 연산자(=) 및 논리 연산자(OR)의 NULL 값 처리에서 추론할 수 있습니다. 요약하자면 다음은 IN 식의 결과를 계산하는 규칙입니다.

  • 해당 NULL이 아닌 값이 list에서 발견되면 TRUE이 반환됩니다.
  • NULL이 아닌 값을 list에서 찾을 수 없고 list에 NULL values이 포함되지 않으면 FALSE이 반환됩니다.
  • 값이 NULL일 때 UNKNOWN가 반환되거나 list에서 NULL이 아닌 값을 찾을 수 없거나 list이 적어도 하나 이상의 NULL 값을 포함하고 있을 경우.

NOT IN은 입력 값에 상관없이 list에 NULL가 포함되면 항상 UNKNOWN을 반환합니다. IN은 값이 NULL를 포함한 list에 없을 경우 UNKNOWN을 반환하고, NOT UNKNOWN가 다시 UNKNOWN이기 때문입니다.

예제

-- The subquery has only `NULL` value in its result set. Therefore,
-- the result of `IN` predicate is UNKNOWN.
> SELECT * FROM person WHERE age IN (SELECT null);
 name age
 ---- ---

-- The subquery has `NULL` value in the result set as well as a valid
-- value `50`. Rows with age = 50 are returned.
> SELECT * FROM person
    WHERE age IN (SELECT age FROM VALUES (50), (null) sub(age));
 name age
 ---- ---
 Fred  50
  Dan  50

-- Since subquery has `NULL` value in the result set, the `NOT IN`
-- predicate would return UNKNOWN. Hence, no rows are
-- qualified for this query.
> SELECT * FROM person
    WHERE age NOT IN (SELECT age FROM VALUES (50), (null) sub(age));
 name age
 ---- ---