Semantik NULL

2025-03-29

Berlaku untuk: centang ditandai ya Databricks SQL Databricks Runtime

Tabel terdiri dari sekumpulan baris dan setiap baris berisi sekumpulan kolom. Kolom dikaitkan dengan jenis data dan mewakili atribut entitas tertentu (misalnya, age adalah kolom entitas yang disebut person). Terkadang, nilai kolom khusus untuk baris tidak diketahui pada saat baris muncul. Dalam SQL, nilai tersebut diwakili sebagai NULL. Bagian ini merinci semantik penanganan nilai NULL di berbagai operator, ekspresi, dan konstruksi SQL lainnya.

Berikut ini menggambarkan tata letak skema dan data tabel bernama person. Data berisi nilai NULL di kolom age dan tabel ini digunakan dalam berbagai contoh di bagian di bawah ini.

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

Operator perbandingan

Azure Databricks mendukung operator perbandingan standar seperti >, , >==, < dan <=. Hasil dari operator ini tidak diketahui atau NULL saat salah satu operand atau kedua operand tidak diketahui atau NULL. Untuk membandingkan nilai NULL untuk kesetaraan, Azure Databricks menyediakan operator "sama aman terhadap null" (<=>), yang mengembalikan False ketika salah satu operand adalah NULL dan mengembalikan True ketika kedua operand adalah NULL. Tabel berikut mengilustrasikan perilaku operator perbandingan saat satu atau kedua operan NULL:

Operand Kiri	Operand Kanan	`>`	`>=`	`=`	`<`	`<=`	`<=>`
NULL	Nilai apa pun	NULL	NULL	NULL	NULL	NULL	Salah
Nilai apa pun	NULL	NULL	NULL	NULL	NULL	NULL	Salah
NULL	NULL	NULL	NULL	NULL	NULL	NULL	Benar

Contoh

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

Operator logis

Azure Databricks mendukung operator logis standar seperti AND, OR dan NOT. Operator ini mengambil Boolean ekspresi sebagai argumen dan mengembalikan Boolean nilai.

Tabel berikut menunjukkan perilaku operator logis ketika salah satu atau kedua operan adalah NULL.

Operand di Sebelah Kiri	Operand Kanan	ATAU	DAN
Benar	NULL	Benar	NULL
Salah	NULL	NULL	Salah
NULL	Benar	Benar	NULL
NULL	Salah	NULL	Salah
NULL	NULL	NULL	NULL

operand	TIDAK
NULL	NULL

Contoh

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

Ekspresi

Operator perbandingan dan operator logis diperlakukan sebagai ekspresi di Azure Databricks. Azure Databricks juga mendukung bentuk ekspresi lain, yang dapat diklasifikasikan secara luas sebagai:

Ekspresi yang tidak mentolerir nol
Ungkapan yang dapat memproses NULL operan nilai
- Hasil dari ekspresi ini tergantung pada ekspresi itu sendiri.

Ekspresi yang tidak mentoleransi nilai null

Ekspresi yang tidak dapat menerima null akan mengembalikan NULL ketika satu atau lebih argumen ekspresi adalah NULL dan sebagian besar ekspresi termasuk dalam kategori ini.

Contoh

> 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

Ekspresi yang dapat memproses operand nilai null

Kelas ekspresi ini dirancang untuk menangani nilai NULL. Hasil dari ekspresi ini tergantung pada ekspresi itu sendiri. Sebagai contoh, ekspresi fungsi isnull mengembalikan true pada input null dan false pada input non-null di mana sebagai fungsi coalesce mengembalikan nilai non NULL pertama dalam daftar operandnya. Namun, coalesce mengembalikan NULL ketika semua operan-operannya adalah NULL. Di bawah ini adalah daftar ekspresi kategori ini yang tidak lengkap.

menggabungkan
NULLIF
IFNULL
NVL
NVL2
ISNAN
NANVL
ISNULL
ISNOTNULL
ATLEASTNNONNULLS
IN

Contoh

> 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

Ekspresi agregat bawaan

Fungsi agregat menghitung satu hasil dengan memproses sekumpulan baris input. Di bawah ini adalah aturan tentang bagaimana nilai NULL ditangani oleh fungsi agregat.

Nilai-nilai NULL diabaikan dalam pemrosesan oleh semua fungsi agregat.
- Hanya pengecualian untuk aturan ini adalah fungsi COUNT(*).
Beberapa fungsi agregat mengembalikan NULL ketika semua nilai input NULL atau himpunan data input kosong. Daftar fungsi-fungsi ini adalah:
- MAX
- MIN
- SUM
- AVG
- EVERY
- ANY
- SOME

Contoh

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

Ekspresi kondisi dalam klausul `WHERE`, `HAVING`, dan `JOIN`

WHERE, HAVING operator memfilter baris berdasarkan kondisi yang ditentukan pengguna. Operator JOIN digunakan untuk menggabungkan baris dari dua tabel berdasarkan kondisi gabungan. Untuk ketiga operator, ekspresi kondisi adalah ekspresi boolean dan dapat mengembalikan True, False, atau Unknown (NULL). Mereka "puas" jika hasil kondisinya adalah True.

Contoh

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

Operator agregat (`GROUP BY`, `DISTINCT`)

Seperti yang dibahas di operator perbandingan , dua nilai NULL tidak sama. Namun, untuk tujuan pengelompokan dan pemrosesan yang berbeda, dua nilai atau lebih dengan NULL datadikelompokkan bersama ke dalam wadah yang sama. Perilaku ini sesuai dengan standar SQL dan dengan sistem manajemen database perusahaan lainnya.

Contoh

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

Operator pengurutan (klausul `ORDER BY`)

Azure Databricks mendukung spesifikasi urutan null dalam ORDER BY klausul. Azure Databricks memproses klausa ORDER BY dengan menempatkan semua nilai NULL pada awalnya atau terakhir tergantung pada spesifikasi urutan null. Secara default, semua nilai NULL ditempatkan pada awalnya.

Contoh

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

Mengatur operator (`UNION`, `INTERSECT`, `EXCEPT`)

NULL pemeriksaan nilai dilakukan dengan cara null-safe untuk kesamaan dalam konteks operasi himpunan. Itu berarti saat membandingkan baris, dua nilai NULL dianggap sama, tidak seperti operator EqualToreguler (=).

Contoh

> 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

Subkueri `EXISTS` dan `NOT EXISTS`

Di Azure Databricks, ekspresi EXISTS dan NOT EXISTS diizinkan di dalam klausa WHERE. Ini adalah ekspresi Boolean yang mengembalikan TRUE atau FALSE. Dengan kata lain, EXISTS adalah kondisi keanggotaan dan mengembalikan TRUE ketika subkueri yang dirujuk mengembalikan satu atau beberapa baris. Demikian pula, NOT EXISTS adalah kondisi non-keanggotaan dan mengembalikan TRUE ketika tidak ada baris atau baris nol yang dikembalikan dari subkueri.

Kedua ekspresi ini tidak terpengaruh oleh kehadiran NULL dalam hasil subquery. Mereka biasanya lebih cepat karena mereka dapat dikonversi ke semijoin dan anti-semijoin tanpa ketentuan khusus untuk kesadaran nol.

Contoh

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

Subkueri `IN` dan `NOT IN`

Di Azure Databricks, IN dan NOT IN ekspresi diizinkan dalam WHERE klausa kueri. Berbeda dengan ekspresi EXISTS, ekspresi IN dapat mengembalikan nilai TRUE, FALSE, atau UNKNOWN (NULL). Secara konseptual ekspresi IN secara semantik setara dengan serangkaian kondisi kesetaraan yang dipisahkan oleh operator disjunctive (OR). Misalnya, c1 IN (1, 2, 3) secara semantik setara dengan (C1 = 1 OR c1 = 2 OR c1 = 3).

Sejauh menangani nilai NULL yang bersangkutan, semantik dapat disimpulkan dari penanganan nilai NULL dalam operator perbandingan (=) dan operator logis (OR). Untuk meringkas, di bawah ini adalah aturan untuk menghitung hasil dari ekspresi IN.

TRUE dikembalikan ketika nilai non-NULL yang dimaksud ditemukan dalam daftar
FALSE dikembalikan ketika nilai non-NULL tidak ditemukan dalam daftar dan daftar tidak berisi nilai NULL
UNKNOWN dikembalikan saat nilai NULL, atau nilai non-NULL tidak ditemukan dalam daftar dan daftar berisi setidaknya satu nilai NULL

NOT IN selalu menampilkan UNKNOWN ketika daftar berisi NULL, terlepas dari nilai input. Ini karena IN mengembalikan UNKNOWN jika nilai tidak ada dalam daftar yang berisi NULL, dan karena NOT UNKNOWN lagi UNKNOWN.

Contoh

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

Bagikan melalui

Semantik NULL

Operator perbandingan

Contoh

Operator logis

Contoh

Ekspresi

Ekspresi yang tidak mentoleransi nilai null

Contoh

Ekspresi yang dapat memproses operand nilai null

Contoh

Ekspresi agregat bawaan

Contoh

Ekspresi kondisi dalam klausul WHERE, HAVING, dan JOIN

Contoh

Operator agregat (GROUP BY, DISTINCT)

Contoh

Operator pengurutan (klausul ORDER BY)

Contoh

Mengatur operator (UNION, INTERSECT, EXCEPT)

Contoh

Subkueri EXISTS dan NOT EXISTS

Contoh

Subkueri IN dan NOT IN

Contoh

Saran dan Komentar

Sumber Daya Tambahan:

Ekspresi kondisi dalam klausul `WHERE`, `HAVING`, dan `JOIN`

Operator agregat (`GROUP BY`, `DISTINCT`)

Operator pengurutan (klausul `ORDER BY`)

Mengatur operator (`UNION`, `INTERSECT`, `EXCEPT`)

Subkueri `EXISTS` dan `NOT EXISTS`

Subkueri `IN` dan `NOT IN`