SQL-työhaastatteluun valmistautuminen · Oppitunti

NULL-arvot koostefunktioissa, liitoksissa ja DISTINCTissä

Miten NULL käyttäytyy eri tavoin ryhmittelyssä, liitoksissa ja yksikäsitteisyydessä

Oppitunti 4/413 vaihetta

NULL-arvot koostefunktioissa, liitoksissa ja DISTINCTissä on ilmainen SQL-työhaastatteluun valmistautuminen-oppitunti CoddyKitissä. Tämä on oppitunti 4/4. Voit lukea koko oppitunnin alta ilmaiseksi ja harjoitella sen jälkeen käytännössä selaimessa sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla. Oppitunti kuuluu SQL-työhaastatteluun valmistautuminen-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.

NULL kolmessa yllättävässä paikassa

NULL ei käyttäydy kaikkialla samalla tavalla. Viimeinen oppitunti käsittelee kolmea asiayhteyttä, joissa sen toiminta yllättää ehdokkaat useimmin: aggregaattifunktiot, JOINit ja DISTINCT / GROUP BY.

Toistuva yllätys on se, että aggregaattifunktiot ja suodatus käsittelevät NULL-arvon muodossa »ohita minut«, kun taas ryhmittely ja DISTINCT käsittelevät NULL-arvoa arvona, joka on yhtä suuri kuin muut NULL-arvot. Juuri tätä epäjohdonmukaisuutta haastattelijat testaavat.

Kun hallitsette nämä asiat, olette käsitelleet SQL-haastattelujen yleisimmät NULL-kysymykset kattavasti.

Aggregaattifunktiot ohittavat NULL-arvot

Pääsääntö on seuraava: aggregaattifunktiot ohittavat NULL-arvot. SUM, AVG, MIN, MAX ja COUNT(column) jättävät NULL-syötteet kokonaan huomiotta eivätkä käsittele niitä nollina.

Siksi AVG voi palauttaa eri luvun kuin odotatte. Se jakaa muiden kuin NULL-arvojen summan muiden kuin NULL-arvojen määrällä, ei rivien kokonaismäärällä.

-- 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(*) vs COUNT(column)

Tämä on yleisin aggregaattien NULL-arvoja koskeva kysymys. COUNT(*) laskee rivit, myös NULL-arvoja sisältävät rivit. COUNT(column) laskee vain rivit, joissa kyseinen sarake on muu kuin NULL.

Niiden välinen ero on siis täsmälleen kyseisen sarakkeen NULL-arvojen määrä. COUNT(DISTINCT column) menee vielä pidemmälle: se ohittaa myös NULL-arvot ja poistaa kaksoiskappaleet.

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 vs SUM/COUNT(*): klassinen ansa

Haastattelijat kysyvät: »Onko AVG(x) sama kuin SUM(x) / COUNT(*)?« Vastaus on ei, kun mukana on NULL-arvoja.

AVG(x) vastaa lauseketta SUM(x) / COUNT(x) ja jakaa summan muiden kuin NULL-arvojen määrällä. Jos käytätte sen sijaan COUNT(*)-funktiota, käsittelette NULL-arvot ikään kuin ne olisivat nollia, mikä pienentää keskiarvoa.

Jos todella haluatte laskea NULL-arvot nollina, se on ilmaistava erikseen COALESCE-funktiolla.

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

Kaikki NULL-arvoja sisältävien aggregaattien reunatapaus

Mitä aggregaatti palauttaa, kun jokainen syötearvo on NULL tai rivejä ei ole lainkaan? Haastattelijat arvostavat seuraavaa täsmällistä erottelua:

SUM-, AVG-, MIN- ja MAX-funktiot palauttavat NULL-arvon, kun kaikki syötearvot ovat NULL-arvoja tai rivejä ei ole.
COUNT palauttaa aina arvon 0, ei koskaan NULL-arvoa.

Jos raportissa näkyy tyhjiä summia, syynä on todennäköisesti NULL-arvoja sisältävä SUM. Käärikää se COALESCE-funktioon, jotta näytettäväksi arvoksi tulee 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-arvot JOIN-ehdoissa

JOINin ON-lausekkeessa NULL = NULL on edelleen UNKNOWN, joten NULL-avaimet eivät koskaan täsmää equi-joinissa. Kahta NULL-arvoa sisältävää JOIN-avainta ei yhdistetä toisiinsa.

Tämä aiheuttaa ongelmia, kun liitetään tauluja valinnaisten viiteavainten perusteella. Jos NULL-arvojen on tarkoitus täsmätä toisiinsa, tarvitsette NULL-turvallisen operaattorin (IS NOT DISTINCT FROM tai <=>) aiemmasta oppitunnista.

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

Ulkoisten JOINien tuottamat NULL-arvot

Ulkoiset JOINit tuottavat NULL-arvoja täsmäämättömille riveille. LEFT JOINin jälkeen jokainen oikean puolen sarake on NULL-arvoinen niillä vasemman puolen riveillä, joille ei löytynyt vastinetta.

Tämä muodostaa anti-join-mallin perustan: suodattakaa WHERE right_table.key IS NULL löytääksenne rivit, joille ei ole vastinetta, kuten asiakkaat, joilla ei ole tilauksia.

Olkaa kuitenkin varovaisia: ulkoisen JOINin sarakkeen suodattaminen WHERE-lausekkeessa voi vahingossa muuttaa sen takaisin sisäiseksi JOINiksi. Tätä käsitellään seuraavassa osiossa.

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

Ulkoisen JOINin WHERE-ehtoon liittyvä NULL-ansa

Tämä on haastattelijoiden suosima sudenkuoppa. Teette LEFT JOINin orders-tauluun ja lisäätte sitten ehdon WHERE o.status = 'shipped'. Yhtäkkiä asiakkaat, joilla ei ole tilauksia, katoavat, jolloin ulkoinen JOIN muuttuu käytännössä sisäiseksi JOINiksi.

Miksi? Täsmäämättömillä riveillä o.status on NULL, ja NULL = 'shipped' on UNKNOWN, joten WHERE-lauseke poistaa nämä rivit. Säilyttääksenne täsmäämättömät rivit siirtäkää ehto sen sijaan ON-lausekkeeseen.

-- 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 käsittelee kaikkia NULL-arvoja samanarvoisina

Tässä on ristiriita, joka yllättää kaikki. Koostefunktiot ohittavat NULL-arvot, mutta DISTINCT säilyttää täsmälleen yhden NULL-arvon ja käsittelee kaikkia NULL-arvoja toistensa duplikaatteina.

Kun siis arvoille 100, 100, NULL, NULL suoritetaan SELECT DISTINCT bonus, tuloksena on kolme riviä: 100, NULL ja siinä kaikki. Kaksi NULL-arvoa yhdistyvät yhdeksi, vaikka NULL = NULL on muualla UNKNOWN.

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

GROUP BY kokoaa NULL-arvot yhdeksi ryhmäksi

GROUP BY noudattaa samaa sääntöä kuin DISTINCT: kaikki NULL-avaimet kootaan yhdeksi ryhmäksi. Tämä on päinvastaista kuin vertailulogiikassa, jossa NULL-arvot eivät koskaan ole keskenään samanarvoisia.

Kun siis ryhmittelet nullable-sarakkeen perusteella, saat yhden rivin, joka edustaa kaikkia NULL-avaimeen liittyviä tietueita. Tämä on yleensä juuri sitä, mitä raportoinnissa halutaan. Mainitkaa tämä ryhmittelyn ja vertailun välinen ero osoittaaksenne ymmärtävänne aiheen syvällisesti.

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

Haastattelun keskeiset kohdat

Yhdistävä yhteenveto, joka tekee haastattelijaan vaikutuksen:

Koostefunktiot ohittavat NULL-arvot; AVG jakaa luvulla COUNT(column), ei luvulla COUNT(*).
COUNT(*) laskee rivit; COUNT(col) ja COUNT(DISTINCT col) ohittavat NULL-arvot.
Kun rivejä ei ole, SUM/AVG/MIN/MAX palauttavat NULL-arvon; COUNT palauttaa 0:n.
Liitoksissa NULL-avaimet eivät koskaan täsmää; ulkoisesti liitetyn sarakkeen suodattaminen WHERE-osassa muuttaa kyselyn huomaamatta sisäiseksi liitokseksi.
DISTINCT ja GROUP BY käsittelevät kaikkia NULL-arvoja samanarvoisina, mikä on päinvastaista kuin vertailulogiikassa.

Yhden virkkeen tiivistys: 'NULL ohitetaan koostamisessa ja vertailussa, mutta duplikaatteja poistettaessa kaikki NULL-arvot ryhmitellään yhteen.'

Pikatarkistus

Testatkaa ryhmittelyn ja koostamisen välistä eroa.

Vastaa interaktiivisessa oppitunnissa

Kertaus

Olette suorittaneet NULL-arvojen käsittelyä koskevan haastatteluosuuden:

Koostefunktiot ohittavat NULL-arvot; AVG jakaa ei-NULL-arvojen määrällä, ja pelkistä NULL-arvoista koostuva SUM palauttaa NULL-arvon, kun taas COUNT palauttaa 0:n.
COUNT(*) sisältää NULL-arvoja sisältävät rivit; COUNT(col) ei sisällä niitä, ja näiden erotus vastaa NULL-arvojen määrää.
NULL-arvoja sisältävät liitosavaimet eivät koskaan täsmää; ulkoisesti liitettyjen sarakkeiden suodattaminen WHERE-osassa voi muuttaa liitoksen sisäiseksi liitokseksi.
DISTINCT ja GROUP BY kokoavat kaikki NULL-arvot yhdeksi, mikä on käänteistä vertailulogiikkaan nähden.

Muistakaa tämä periaate: NULL ohitetaan koostamisessa ja vertailussa, mutta duplikaatteja poistettaessa kaikki NULL-arvot ryhmitellään yhteen. Tämä yksittäinen oivallus auttaa vastaamaan useimpiin NULL-arvoja koskeviin haastattelukysymyksiin.

Aloita maksutta

Opi SQL tekoälytuutorin avulla — ilmaiseksi

Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.

Kurssit: 30
Oppitunnit: 120

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”NULL-arvot koostefunktioissa, liitoksissa ja DISTINCTissä” ilmainen?

Kyllä – oppitunnin ”NULL-arvot koostefunktioissa, liitoksissa ja DISTINCTissä” koko tekstin voi lukea täällä verkossa ilmaiseksi. Jos haluat harjoitella interaktiivisesti sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla sekä avata koko SQL-työhaastatteluun valmistautuminen-kurssin, päivitä CoddyKit PROhon. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”NULL-arvot koostefunktioissa, liitoksissa ja DISTINCTissä”?

Miten NULL käyttäytyy eri tavoin ryhmittelyssä, liitoksissa ja yksikäsitteisyydessä Harjoittelet SQL-työhaastatteluun valmistautuminen-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni SQL-työhaastatteluun valmistautuminen-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin SQL-työhaastatteluun valmistautuminen-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 4/4.

Kuinka kauan ”NULL-arvot koostefunktioissa, liitoksissa ja DISTINCTissä”-oppitunnin suorittaminen kestää?

Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.

Voinko kirjoittaa ja suorittaa koodia tällä SQL-työhaastatteluun valmistautuminen-oppitunnilla?

Kyllä. Jokainen SQL-työhaastatteluun valmistautuminen-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.

Kaikki tämän kurssin oppitunnit

Kolmiarvoinen logiikka ja UNKNOWN
IS NULL, IS NOT NULL ja NULL-turvallinen yhtäsuuruus
COALESCE, NULLIF ja ISNULL
NULL-arvot koostefunktioissa, liitoksissa ja DISTINCTissä

← Takaisin: SQL-työhaastatteluun valmistautuminen