NULL 의미 체계

적용 대상: Databricks SQL Databricks 런타임

테이블은 행 집합으로 구성되며 각 행에는 열 집합이 포함됩니다. 열은 데이터 형식과 연결되며 엔터티의 특정 특성을 나타냅니다(예 age는 person라는 엔터티의 열임). 행이 생성될 때 행에 특정한 열의 값을 알 수 없는 경우가 있습니다. SQL에서 이러한 값은 NULL로 표시됩니다. 이 섹션에서는 다양한 연산자, 식 및 기타 SQL 구문에서 처리되는 NULL 값의 의미에 대해 자세히 설명합니다.

다음은 person라는 테이블의 스키마 레이아웃과 데이터를 보여 줍니다. 데이터에는 age 열의 NULL 값이 포함되며 이 테이블은 아래 섹션의 다양한 예제에서 사용됩니다.

 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입니다. NULL 값이 같은지 비교하기 위해 Azure Databricks는 피연산자 중 하나가 NULL일 때 False를 반환하고 두 피연산자가 모두 NULL이면 True를 반환하는 null-safe 등호 연산자(<=>)를 제공합니다. 다음 표에서는 하나 또는 두 피연산자가 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 값을 반환합니다.

다음 표는 하나 또는 두 개의 피연산자가 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 값 피연산자를 처리할 수 있는 식

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

• 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

기본 제공 집계 식

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

• NULL 값은 모든 집계 함수의 처리에서 무시됩니다.
  • 이 규칙의 유일한 예외는 COUNT(*) 함수입니다.
• 일부 집계 함수는 모든 입력 값이 NULL이거나 입력 데이터 세트가 비어 있는 경우 NULL을 반환합니다. 이러한 함수 목록은 다음과 같습니다.
  • 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 연산자는 조인 조건에 따라 두 테이블의 행을 결합하는 데 사용됩니다. 세 연산자 모두에 대해 조건 식은 부울 식이며 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)

비교 연산자에서 설명한 것처럼 두 개의 NULL 값은 같지 않습니다. 그러나 그룹화 및 고유 처리를 위해 NULL data가 있는 둘 이상의 값이 동일한 버킷으로 그룹화됩니다. 이 동작은 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 순서 지정 사양에 따라 처음 또는 마지막에 모든 NULL 값을 배치하여 ORDER BY 절을 처리합니다. 기본적으로 모든 NULL 값은 처음에 배치됩니다.

예제

-- `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

집합 연산자(UNION, INTERSECT, EXCEPT)

NULL 값은 집합 작업 컨텍스트에서 같음을 위해 null 안전 방식으로 비교됩니다. 즉, 행을 비교할 때 일반 EqualTo(=) 연산자와 달리 두 개의 NULL 값이 동일한 것으로 간주됩니다.

예제

> 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에서는 WHERE 절 내에서 EXISTS 및 NOT EXISTS 식이 허용됩니다. 이는 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에서는 쿼리의 WHERE 절 내에서 IN 및 NOT IN 식이 허용됩니다. EXISTS 식과 달리 IN 식은 TRUE, FALSE 또는 UNKNOWN (NULL) 값을 반환할 수 있습니다. 개념적으로 IN 식은 분리 연산자(OR)로 구분된 같음 조건 집합과 의미상 동일합니다. 예를 들어 c1 IN(1, 2, 3)은 의미상 (C1 = 1 OR c1 = 2 OR c1 = 3)과 동일합니다.

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

• 목록에서 문제의 NULL이 아닌 값이 발견되면 TRUE가 반환됩니다.
• 목록에 NULL이 아닌 값이 없고 목록에 NULL 값이 없으면 FALSE가 반환됩니다.
• 값이 NULL이거나 목록에 NULL이 아닌 값이 없고 목록에 하나 이상의 NULL 값이 포함된 경우 UNKNOWN이 반환됩니다.

NOT IN은 입력 값에 관계없이 목록에 NULL이 포함된 경우 항상 UNKNOWN을 반환합니다. 이는 값이 NULL을 포함하는 목록에 없는 경우 IN이 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
 ---- ---