Semantik NULL
Berlaku untuk: pemeriksaan 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 direpresentasikan sebagai NULL
. Bagian ini merinci semantik NULL
penanganan nilai di berbagai operator, ekspresi, dan konstruksi lainnya SQL
.
Berikut ini menggambarkan tata letak skema dan data tabel bernama person
. Data berisi NULL
nilai dalam age
kolom 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
ketika salah satu operan atau kedua operan tidak diketahui atau NULL
. Untuk membandingkan nilai untuk kesetaraan NULL
, Azure Databricks menyediakan operator sama aman-null (<=>
), yang mengembalikan False
ketika salah satu operand adalah NULL
dan kembali True
ketika kedua operan adalah NULL
. Tabel berikut mengilustrasikan perilaku operator perbandingan ketika satu atau kedua operan adalah NULL
:
Operand Kiri | Operand Kanan | > |
>= |
= |
< |
<= |
<=> |
---|---|---|---|---|---|---|---|
NULL | Nilai apa pun | NULL | NULL | NULL | NULL | NULL | Palsu |
Nilai apa pun | NULL | NULL | NULL | NULL | NULL | NULL | Palsu |
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 logika
Azure Databricks mendukung operator logis standar seperti AND
, OR
dan NOT
.
Operator ini mengambil Boolean
ekspresi sebagai argumen dan mengembalikan Boolean
nilai.
Tabel berikut mengilustrasikan perilaku operator logis ketika satu atau kedua operan adalah NULL
.
Operand Kiri | Operand Kanan | ATAU | DAN |
---|---|---|---|
Benar | NULL | Benar | NULL |
Palsu | NULL | NULL | Palsu |
NULL | Benar | Benar | NULL |
NULL | Palsu | NULL | Palsu |
NULL | NULL | NULL | NULL |
operan | 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 intoleran null
- Ekspresi yang dapat memproses
NULL
operand nilai- Hasil dari ekspresi ini tergantung pada ekspresi itu sendiri.
Ekspresi intoleran null
Ekspresi intoleran null kembali NULL
ketika satu atau beberapa 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 NULL
nilai. Hasil ekspresi bergantung pada ekspresi itu sendiri. Sebagai contoh, ekspresi isnull
fungsi mengembalikan true
input null dan false
pada input non-null di mana sebagai fungsi coalesce
mengembalikan nilai bukan NULL
pertama dalam daftar operand- nya. Namun, coalesce
kembali NULL
ketika semua operannya adalah NULL
. Di bawah ini adalah daftar ekspresi kategori ini yang tidak lengkap.
- COALESCE
- 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 NULL
nilai ditangani oleh fungsi agregat.
NULL
nilai diabaikan dari pemrosesan oleh semua fungsi agregat.- Hanya pengecualian untuk aturan ini adalah fungsi COUNT(*).
- Beberapa fungsi agregat kembali
NULL
ketika semua nilai input adalahNULL
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 WHERE
klausa , 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 dalam operator Perbandingan, dua NULL
nilai tidak sama. Namun, untuk tujuan pengelompokan dan pemrosesan yang berbeda, dua nilai atau lebih dengan NULL data
dikelompokkan 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 (ORDER BY
klausa)
Azure Databricks mendukung spesifikasi urutan null dalam ORDER BY
klausul. Azure Databricks memproses ORDER BY
klausul dengan menempatkan semua nilai pada awalnya NULL
atau terakhir tergantung pada spesifikasi pengurutan null. Secara default, semua nilai ditempatkan pada awalnya NULL
.
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
nilai dibandingkan dengan cara null-safe untuk kesetaraan dalam konteks operasi yang ditetapkan. Itu berarti saat membandingkan baris, dua NULL
nilai dianggap sama dengan operator reguler EqualTo
(=
).
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
EXISTS
dan NOT EXISTS
subkueri
Di Azure Databricks, EXISTS
dan NOT EXISTS
ekspresi diizinkan di dalam WHERE
klausa.
Ini adalah ekspresi Boolean yang mengembalikan baik TRUE
atau FALSE
. Dengan kata lain, EXISTS
adalah kondisi keanggotaan dan kembali TRUE
ketika subkueri yang dirujuknya mengembalikan satu atau beberapa baris. Demikian pula, NOT EXISTS adalah kondisi non-keanggotaan dan kembali TRUE
ketika tidak ada baris atau baris nol yang dikembalikan dari subkueri.
Kedua ekspresi ini tidak terpengaruh oleh keberadaan NULL dalam hasil subkueri. Mereka biasanya lebih cepat karena dapat dikonversi ke semijoin dan anti-semijoin tanpa ketentuan khusus untuk kesadaran null.
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
IN
dan NOT IN
subkueri
Di Azure Databricks, IN
dan NOT IN
ekspresi diizinkan di dalam WHERE
klausa kueri. EXISTS
Tidak seperti ekspresi, IN
ekspresi dapat mengembalikan TRUE
nilai , FALSE
atau UNKNOWN (NULL)
. Secara konseptual IN
ekspresi 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 menyangkut nilai penanganan NULL
, semantik dapat disimpulkan dari NULL
penanganan nilai dalam operator perbandingan(=
) dan operator logis(OR
).
Untuk meringkas, di bawah ini adalah aturan untuk menghitung hasil IN
ekspresi.
TRUE
dikembalikan ketika nilai non-NULL yang dimaksud ditemukan dalam daftarFALSE
dikembalikan ketika nilai non-NULL tidak ditemukan dalam daftar dan daftar tidak berisi nilai NULLUNKNOWN
dikembalikan ketika nilai adalahNULL
, atau nilai non-NULL tidak ditemukan dalam daftar dan daftar berisi setidaknya satuNULL
nilai
NOT IN
selalu mengembalikan 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
---- ---