Persediaan Temu Duga Pengaturcaraan · Pelajaran

NULL dalam Agregat, Cantuman dan DISTINCT

Cara NULL berkelakuan secara berbeza dalam pengumpulan, cantuman dan keunikan

Pelajaran 4 daripada 413 langkah

NULL dalam Agregat, Cantuman dan DISTINCT ialah pelajaran Persediaan Temu Duga Pengaturcaraan percuma di CoddyKit. Ini ialah pelajaran 4 daripada 4. Anda boleh membaca keseluruhan pelajaran di bawah secara percuma — kemudian berlatih secara praktikal dalam pelayar menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Persediaan Temu Duga Pengaturcaraan, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Persediaan Temu Duga Pengaturcaraan merangkumi sejumlah 4 pelajaran.

NULL dalam Tiga Konteks yang Mengejutkan

NULL tidak berkelakuan sama dalam semua keadaan. Pelajaran terakhir ini merangkumi tiga konteks yang paling kerap mengejutkan calon: fungsi agregat, cantuman dan DISTINCT / GROUP BY.

Perbezaan yang berulang ialah fungsi agregat dan penapisan menganggap NULL sebagai 'abaikan saya', tetapi pengumpulan dan DISTINCT menganggap NULL sebagai 'nilai yang sama dengan NULL lain'. Ketidakselarasan inilah yang sering diuji oleh penemu duga.

Kuasai perkara ini dan Anda akan melengkapkan pemahaman tentang soalan NULL yang paling biasa dalam saringan SQL.

Fungsi Agregat Mengabaikan NULL

Peraturan utamanya: fungsi agregat melangkau NULL. SUM, AVG, MIN, MAX dan COUNT(lajur) semuanya mengabaikan masukan NULL sepenuhnya, bukannya menganggapnya sebagai sifar.

Inilah sebabnya AVG boleh mengembalikan nombor yang berbeza daripada jangkaan Anda. Fungsi ini membahagikan jumlah nilai bukan NULL dengan bilangan nilai bukan NULL, bukan dengan jumlah keseluruhan baris.

-- bonus values: 100, 200, NULL
SELECT
  SUM(bonus) AS total,   -- 300 (NULL ignored)
  AVG(bonus) AS average, -- 150 = 300 / 2, not / 3
  COUNT(bonus) AS cnt    -- 2 (NULL not counted)
FROM employees;

COUNT(*) berbanding COUNT(lajur)

Inilah soalan agregat-NULL yang paling kerap ditanya. COUNT(*) mengira baris, termasuk baris yang mengandungi NULL. COUNT(column) hanya mengira baris yang lajurnya bukan NULL.

Oleh itu, perbezaan antara kedua-duanya ialah tepat jumlah NULL dalam lajur tersebut. COUNT(DISTINCT column) pergi lebih jauh dengan turut mengabaikan NULL sambil membuang pendua.

SELECT
  COUNT(*)              AS rows_total,    -- all rows
  COUNT(bonus)          AS non_null_bonus, -- excludes NULLs
  COUNT(DISTINCT bonus) AS distinct_bonus, -- excludes NULLs + dups
  COUNT(*) - COUNT(bonus) AS null_bonus
FROM employees;

AVG berbanding SUM/COUNT(*): Perangkap Klasik

Penemu duga bertanya: 'Adakah AVG(x) sama dengan SUM(x) / COUNT(*)?' Jawapannya ialah tidak apabila terdapat NULL.

AVG(x) sama dengan SUM(x) / COUNT(x), iaitu membahagi dengan bilangan nilai bukan NULL. Jika sebaliknya Anda membahagi dengan COUNT(*), NULL dianggap sebagai sifar dan purata menjadi lebih rendah.

Jika Anda benar-benar mahu NULL dikira sebagai sifar, Anda mesti menyatakannya dengan jelas menggunakan COALESCE.

-- These differ when bonus has NULLs:
SELECT
  AVG(bonus)                       AS avg_ignoring_nulls,
  SUM(bonus) * 1.0 / COUNT(*)      AS avg_nulls_as_zero,
  AVG(COALESCE(bonus, 0))          AS explicit_nulls_as_zero
FROM employees;

Kes Pinggir Agregat yang Semuanya NULL

Apakah hasil fungsi agregat apabila setiap masukan ialah NULL atau tiada baris? Berikut ialah perbezaan tepat yang disukai oleh penemu duga:

SUM, AVG, MIN dan MAX pada baris yang semuanya NULL (atau sifar baris) mengembalikan NULL.
COUNT sentiasa mengembalikan 0, tidak pernah NULL.

Jadi, jika laporan memaparkan jumlah kosong, SUM yang semuanya NULL mungkin menjadi puncanya. Balut fungsi tersebut dengan COALESCE untuk memaparkan 0.

-- No matching rows or all bonuses NULL:
SELECT SUM(bonus) FROM employees WHERE 1 = 0;  -- NULL
SELECT COUNT(bonus) FROM employees WHERE 1 = 0; -- 0

-- Present a clean zero:
SELECT COALESCE(SUM(bonus), 0) FROM employees;

NULL dalam Syarat JOIN

Dalam klausa ON bagi cantuman, NULL = NULL masih menjadi UNKNOWN, jadi kunci NULL tidak pernah sepadan dalam cantuman kesamaan. Dua baris yang kedua-duanya mempunyai kunci cantuman NULL tidak akan dipasangkan.

Perkara ini sering mengejutkan orang yang membuat cantuman berdasarkan kunci asing pilihan. Jika padanan NULL dengan NULL memang dikehendaki, Anda memerlukan pengendali yang selamat untuk NULL (IS NOT DISTINCT FROM atau <=>) daripada pelajaran terdahulu.

-- Rows with region IS NULL on both sides do NOT match
SELECT *
FROM a JOIN b ON a.region = b.region;

-- To match NULL-to-NULL (ANSI):
SELECT *
FROM a JOIN b ON a.region IS NOT DISTINCT FROM b.region;

NULL yang Dihasilkan oleh Cantuman Luaran

Cantuman luaran menghasilkan NULL untuk baris yang tiada padanan. Selepas LEFT JOIN, setiap lajur di sebelah kanan ialah NULL bagi baris kiri yang tidak menemui padanan.

Inilah asas corak cantuman anti: tapis dengan WHERE right_table.key IS NULL untuk mencari baris tanpa padanan, seperti pelanggan tanpa pesanan.

Namun, berhati-hati: menapis lajur daripada cantuman luaran dalam WHERE boleh menukarkannya kembali kepada cantuman dalaman secara tidak sengaja. Perkara ini akan dibincangkan dalam babak seterusnya.

-- Find customers who have never ordered (anti-join)
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Perangkap NULL WHERE pada Cantuman Luaran

Ini ialah perangkap yang sering digunakan. Anda membuat LEFT JOIN pada pesanan, kemudian menambah WHERE o.status = 'shipped'. Tiba-tiba pelanggan tanpa pesanan hilang, lalu cantuman luaran Anda berubah menjadi cantuman dalaman secara berkesan.

Mengapa? Bagi baris tanpa padanan, o.status ialah NULL dan NULL = 'shipped' ialah UNKNOWN, jadi WHERE menggugurkannya. Untuk mengekalkan baris tanpa padanan, pindahkan syarat tersebut ke dalam klausa ON.

-- Accidental inner join: drops customers with no orders
SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';

-- Correct: keep unmatched customers
SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'shipped';

DISTINCT Menganggap Semua NULL Sama

Berikut ialah ketidakselarasan yang mengejutkan semua orang. Fungsi agregat mengabaikan NULL, tetapi DISTINCT mengekalkan tepat satu NULL, dengan menganggap semua NULL sebagai pendua antara satu sama lain.

Jadi, SELECT DISTINCT bonus terhadap nilai 100, 100, NULL, NULL mengembalikan tiga baris: 100, NULL dan itu sahaja. Dua NULL digabungkan menjadi satu, walaupun NULL = NULL ialah UNKNOWN di tempat lain.

-- bonus: 100, 100, NULL, NULL, 200
SELECT DISTINCT bonus FROM employees;
-- Returns: 100, 200, NULL  (the two NULLs become one row)

GROUP BY Menggabungkan NULL Menjadi Satu Kumpulan

GROUP BY mengikut peraturan yang sama seperti DISTINCT: semua kunci NULL dihimpunkan dalam satu kumpulan. Ini berlawanan dengan logik perbandingan, yang menyebabkan NULL tidak pernah sama antara satu sama lain.

Jadi, pengelompokan berdasarkan lajur yang boleh bernilai NULL memberikan satu baris yang mewakili semua rekod berkunci NULL, dan biasanya itulah yang diperlukan untuk pelaporan. Nyatakan perbezaan ini (pengelompokan berbanding perbandingan) untuk menunjukkan pemahaman yang mendalam.

-- All employees with NULL department form ONE group
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;
-- A single row where department is NULL totals all of them

Isi Penting Temu Duga

Ringkasan menyeluruh yang mengagumkan penemu duga:

Fungsi agregat mengabaikan NULL; AVG membahagi dengan COUNT(column), bukan COUNT(*).
COUNT(*) mengira baris; COUNT(col) dan COUNT(DISTINCT col) melangkau NULL.
SUM/AVG/MIN/MAX apabila tiada baris mengembalikan NULL; COUNT mengembalikan 0.
Dalam JOIN, kunci NULL tidak pernah sepadan; menapis lajur daripada JOIN luar dalam WHERE secara senyap-senyap menukarkannya kepada JOIN dalaman.
DISTINCT dan GROUP BY menganggap semua NULL sama, iaitu berlawanan dengan logik perbandingan.

Ayat ringkasnya: 'NULL diabaikan semasa pengagregatan dan perbandingan, tetapi dihimpunkan semasa pendua dibuang.'

Semakan Pantas

Uji perbezaan antara pengelompokan dengan pengagregatan.

Jawab dalam pelajaran interaktif

Ringkasan

Anda telah selesai mempelajari pengendalian NULL untuk temu duga:

Fungsi agregat melangkau NULL; AVG membahagi dengan bilangan bukan NULL, dan SUM yang semuanya NULL ialah NULL manakala COUNT ialah 0.
COUNT(*) merangkumi baris NULL; COUNT(col) tidak merangkuminya, dan perbezaannya sama dengan bilangan NULL.
Kunci JOIN yang bernilai NULL tidak pernah sepadan; menapis lajur daripada JOIN luar dalam WHERE boleh mengubahnya menjadi JOIN dalaman.
DISTINCT dan GROUP BY menggabungkan semua NULL menjadi satu, iaitu berlawanan dengan logik perbandingan.

Ingat ungkapan ini: NULL diabaikan semasa pengagregatan dan perbandingan, tetapi dihimpunkan semasa pendua dibuang. Satu pemahaman ini sahaja dapat menjawab kebanyakan soalan temu duga tentang NULL.

Percuma untuk bermula

Pelajari Persediaan Temu Duga Pengaturcaraan dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 90
Pelajaran: 360

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “NULL dalam Agregat, Cantuman dan DISTINCT” percuma?

Ya — teks penuh “NULL dalam Agregat, Cantuman dan DISTINCT” boleh dibaca secara percuma di web ini. Untuk berlatih secara interaktif menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7, serta membuka kunci baki kursus Persediaan Temu Duga Pengaturcaraan, tingkat taraf kepada CoddyKit PRO. Kursus Persediaan Temu Duga Pengaturcaraan merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “NULL dalam Agregat, Cantuman dan DISTINCT”?

Cara NULL berkelakuan secara berbeza dalam pengumpulan, cantuman dan keunikan Anda berlatih Persediaan Temu Duga Pengaturcaraan menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan Persediaan Temu Duga Pengaturcaraan?

Tiada pengalaman terdahulu diperlukan. Pembelajaran Persediaan Temu Duga Pengaturcaraan di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 4 daripada 4.

Berapa lamakah pelajaran “NULL dalam Agregat, Cantuman dan DISTINCT” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran Persediaan Temu Duga Pengaturcaraan ini?

Ya. Setiap pelajaran Persediaan Temu Duga Pengaturcaraan menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

Logik Tiga Nilai dan UNKNOWN
IS NULL, IS NOT NULL dan Kesamaan Selamat NULL
COALESCE, NULLIF dan ISNULL
NULL dalam Agregat, Cantuman dan DISTINCT

← Kembali ke Persediaan Temu Duga Pengaturcaraan