SQL Interview Prep · Lekcja

Zwracanie NULL, gdy nie istnieje n-ta wartość

Przypadek brzegowy uwielbiany przez rekruterów: poprawne obsługiwanie zbyt małej liczby wierszy

Zwracanie NULL, gdy nie istnieje n-ta wartość to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Przypadek brzegowy, o który często pytają rekruterzy

Po poprawnym rozwiązaniu zapytania zwracającego N-tą najwyższą wartość osoba przeprowadzająca rozmowę dodaje: "Co się stanie, jeśli tabela zawiera mniej niż N różnych wynagrodzeń? Chcę otrzymać pojedynczą wartość NULL, a nie pusty wynik."

To pytanie odróżnia osoby, które zapamiętały zapytanie, od tych, które rozumieją zachowanie zbiorów wyników. Wiele rozwiązań po cichu zwraca zero wierszy zamiast jednego wiersza zawierającego NULL.

Ta lekcja dotyczy wymuszenia dokładnie jednego wiersza wynikowego, którego wartością jest NULL, gdy nie istnieje N-ta wartość.

Dlaczego samo DENSE_RANK nie zwraca żadnych wierszy

Przypomnijmy standardowe zapytanie zwracające N-tą najwyższą wartość. Jeśli istnieją tylko dwa różne wynagrodzenia, a zapytanie dotyczy trzeciego, warunek WHERE rnk = 3 niczego nie dopasuje, więc zapytanie zwróci pusty zbiór: zero wierszy.

Pusty zbiór to nie to samo co wiersz zawierający NULL. Jeśli specyfikacja wymaga zwrócenia wartości "NULL", pusty wynik nie przejdzie testu, nawet jeśli podstawowa logika jest poprawna.

SELECT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) t
WHERE rnk = 3;  -- returns NO rows if fewer than 3 distinct salaries

Poprawka 1: opakowanie w zewnętrzny SELECT

Najprostsza niezawodna poprawka polega na umieszczeniu całego zapytania zwracającego N-tą najwyższą wartość jako podzapytania skalarnego wewnątrz pojedynczego SELECT. Podzapytanie skalarne, które nie dopasuje żadnego wiersza, przyjmuje wartość NULL, a zewnętrzny SELECT zawsze zwraca dokładnie jeden wiersz.

To kanoniczna odpowiedź na wariant w stylu LeetCode, w którym należy "zwrócić NULL", i działa w każdym dialekcie SQL.

SELECT (
  SELECT DISTINCT salary
  FROM employee
  ORDER BY salary DESC
  LIMIT 1 OFFSET 2   -- N = 3
) AS third_highest;

Dlaczego działa sztuczka z podzapytaniem skalarnym

Połączenie dwóch zasad zapewnia wymagane zachowanie:

Podzapytanie skalarne może zwrócić najwyżej jedną wartość. Jeśli nie zwróci żadnego wiersza, SQL podstawia NULL.
Zewnętrzny SELECT bez FROM (lub z jednoelementowym źródłem) zawsze zwraca dokładnie jeden wiersz.

Jeśli zapytanie wewnętrzne znajdzie N-tą wartość, otrzymamy tę wartość; jeśli niczego nie znajdzie, otrzymamy jeden wiersz zawierający NULL. Jest to dokładnie wymaganie przedstawione przez osobę przeprowadzającą rozmowę.

Poprawka 1 w wersji z DENSE_RANK

Ta sama otoczka działa również w rozwiązaniu wykorzystującym funkcję okna. Należy umieścić zapytanie z rankingiem wewnątrz podzapytania skalarnego; jeśli żaden wiersz nie ma rangi N, podzapytanie zwróci NULL, a zewnętrzny SELECT nadal zwróci jeden wiersz.

SELECT (
  SELECT salary
  FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employee
  ) t
  WHERE rnk = 3
) AS third_highest;

Poprawka 2: MAX zwraca NULL bez dodatkowych działań

Przypomnijmy sobie pomysł MAX poniżej MAX z lekcji 1. Agregacja wykonywana dla zero wierszy zwraca NULL, a mimo to nadal tworzy jeden wiersz. W przypadku drugiej najwyższej wartości jest to przejrzyste rozwiązanie jednolinijkowe, które już spełnia wymaganie dotyczące wartości NULL.

Wadą jest to, że rozszerzenie zagnieżdżania samego MAX na dowolne N staje się nieporęczne, dlatego to rozwiązanie najlepiej sprawdza się konkretnie dla drugiej najwyższej wartości.

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Poprawka 3: COALESCE z wartością zastępczą

Jeśli środowisko gwarantuje istnienie jednego wiersza, ale wartość może być nieobecna z innego powodu, można opakować wynik w COALESCE, aby podać jawnie określoną wartość domyślną.

Uwaga: COALESCE pomaga dopiero wtedy, gdy wiersz już istnieje. Nie zmienia pustego zbioru wyników w wiersz. Dlatego należy połączyć je z otoczką podzapytania skalarnego, która gwarantuje istnienie wiersza, a następnie zastosować COALESCE do wartości, jeśli zamiast NULL potrzebna jest inna wartość, na przykład 0.

SELECT COALESCE((
  SELECT DISTINCT salary
  FROM employee
  ORDER BY salary DESC
  LIMIT 1 OFFSET 2
), 0) AS third_highest_or_zero;

Co tego nie naprawia

Należy uważać na poprawki, które wyglądają poprawnie, ale nie działają:

Bezpośrednie dodanie COALESCE wokół zapytania zwracającego zero wierszy niczego nie zmienia, ponieważ nie istnieje wiersz, na którym COALESCE mogłoby zadziałać.
IFNULL i ISNULL mają to samo ograniczenie co COALESCE.
Dodanie LIMIT 1 nie tworzy wiersza, gdy żaden wiersz nie spełnił warunków.

Problem liczby wierszy należy rozwiązać za pomocą otoczki podzapytania skalarnego lub agregacji, a nie samych funkcji zastępujących wartości NULL.

Przykład: żądanie trzeciej wartości, gdy istnieją dwie

Wynagrodzenia: 500, 500, 300. Istnieją tylko dwa różne wynagrodzenia: 500 i 300, więc nie ma trzeciej najwyższej wartości.

Zwykłe DENSE_RANK z WHERE rnk = 3: zwraca zero wierszy. Nie spełnia wymagań.
Otoczka podzapytania skalarnego: zapytanie wewnętrzne niczego nie znajduje, więc zewnętrzny SELECT zwraca jeden wiersz: NULL. Rozwiązanie spełnia wymagania.
COALESCE(..., 0): zwraca jeden wiersz: 0, jeśli wymagana była liczbowa wartość domyślna.

Jak omówić rozwiązanie podczas rozmowy

Warto zdobyć punkty, wyjaśniając tok rozumowania:

"Naiwne zapytanie zwraca pusty zbiór, a nie NULL, dlatego opakuję je w podzapytanie skalarne, aby zagwarantować jeden wiersz."
"Podzapytanie skalarne bez pasujących wierszy przyjmuje wartość NULL, co dokładnie spełnia wymaganie."
"Jeśli zamiast NULL potrzebna jest wartość domyślna, na przykład 0, dodam COALESCE wokół podzapytania."

Sednem tego pytania jest wykazanie, że rozumie się różnicę między semantyką liczby wierszy a semantyką wartości.

Połączenie wszystkich elementów

Solidne, parametryzowalne rozwiązanie zwracające N-tą najwyższą wartość albo NULL: należy utworzyć ranking różnych wynagrodzeń, odfiltrować rangę N wewnątrz podzapytania skalarnego i pozwolić, aby zewnętrzny SELECT zagwarantował jeden wiersz.

To pojedyncze zapytanie obsługuje duplikaty (dzięki DENSE_RANK), można je uogólnić na dowolne N, a gdy N przekracza liczbę różnych wynagrodzeń, poprawnie zwraca NULL.

SELECT (
  SELECT salary
  FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employee
  ) t
  WHERE rnk = :n
  LIMIT 1
) AS nth_highest;

Szybki test

Należy przeanalizować różnicę między liczbą wierszy a wartościami NULL.

Podsumowanie

Gdy N przekracza liczbę dostępnych różnych wynagrodzeń, zwykłe zapytanie rankingowe zwraca pusty zbiór, a nie NULL.

Należy opakować zapytanie zwracające N-tą najwyższą wartość w podzapytanie skalarne umieszczone w zewnętrznym SELECT, aby zawsze powstał jeden wiersz, zawierający NULL, gdy żadna wartość nie pasuje.
Postać MAX poniżej MAX automatycznie zwraca NULL w przypadku drugiej najwyższej wartości.
COALESCE podstawia wartość dopiero wtedy, gdy istnieje wiersz; nie może zmienić zero wierszy w jeden wiersz.

Gdy osoba przeprowadzająca rozmowę pyta o bezpieczną obsługę wartości NULL, zawsze należy odróżniać liczbę wierszy od wartości.

Często zadawane pytania

Czy lekcja „Zwracanie NULL, gdy nie istnieje n-ta wartość” jest bezpłatna?

Tak — pełny tekst „Zwracanie NULL, gdy nie istnieje n-ta wartość” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „Zwracanie NULL, gdy nie istnieje n-ta wartość”?

Przypadek brzegowy uwielbiany przez rekruterów: poprawne obsługiwanie zbyt małej liczby wierszy Ćwiczysz SQL Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.

Ile czasu zajmuje lekcja „Zwracanie NULL, gdy nie istnieje n-ta wartość”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Interview Prep?

Tak. Każda lekcja SQL Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

Druga najwyższa pensja na pięć sposobów
N-ta najwyższa wartość za pomocą DENSE_RANK
Najlepiej zarabiająca osoba w dziale
Zwracanie NULL, gdy nie istnieje n-ta wartość

← Powrót do SQL Interview Prep