Pomozte vývoji webu a sdílení článku s přáteli!
Úvod do SQL COALESCE
COALESCE je předdefinovaná vestavěná funkce SQL (Structured Query Language), která se používá ke zpracování hodnot NULL v záznamech dat. Postupně vyhodnotí sadu nebo seznam vstupních parametrů a vrátí mezi nimi první hodnoty, které nejsou NULL. Funkce COALESCE se ukončí, jakmile narazí na první hodnotu, která není NULL, a vrátí ji. Pokud jsou všechny argumenty ve vstupním seznamu hodnoty NULL, funkce vrátí hodnotu NULL.
Je to obecná funkce, která je podporována ve všech databázích, jako je MYSQL, SQL Server (od roku 2008), Azure SQL databáze, PostgreSQL, Oracle atd.
Některá použití funkce SQL COALESCE jsou:
- Zpracování hodnot NULL
- Spuštění dvou nebo více dotazů jako jednoho dotazu
- Zkratková alternativa ke zdlouhavým a časově náročným příkazům CASE
Syntaxe a parametry
Základní syntaxe pro použití funkce COALESCE v SQL je následující:
SELECT COALESCE(hodnota_1, hodnota_2,hodnota_3,hodnota_4,… hodnota_n);
Parametry uvedené ve výše uvedené syntaxi jsou:
COALESCE() : Funkce SQL, která vrací první nenulovou hodnotu ze seznamu vstupů.
value_1, value_2,value_3,value_4,… value_n : Vstupní hodnoty, které je třeba vyhodnotit.
Vzhledem k tomu, že funkce COALESCE je výraz sám o sobě, lze ji použít v jakémkoli příkazu nebo klauzuli, která přijímá výrazy jako SELECT, WHERE a HAVING.
Funkci COALESCE lze považovat za specializovanou verzi příkazu CASE v SQL.
Syntaxe příkazu CASE znázorňující funkci COALESCE funkce je následující:
SELECT název_sloupce1,název_sloupce2, název_sloupce3,
FROM table_name;
PŘÍPAD
KDYŽ název_sloupce1 NENÍ NULL, TAK název_sloupce1
KDYŽ název_sloupce2 NENÍ NULL, TAK název_sloupce2 ELSE NULL
END jako 'case_name'
Parametry uvedené ve výše uvedené syntaxi jsou:
Název_sloupce1, název_sloupce2: Hodnoty sloupců, které se musí sloučit, tj. pokud výsledky některého z těchto sloupců vedou k hodnotám NULL, bude vrácena první hodnota z nich, která není NULL. Ale pokud se oba vyhodnotí jako NULL, pak CASE vrátí NULL.
Postupně budeme diskutovat o výše uvedené funkci COALESCE velmi podrobně.
Abychom lépe porozuměli konceptu, použijeme na pomoc tabulku zaměstnanců (obsahuje osobní údaje všech zaměstnanců).
Údaje v tabulce „zaměstnanci“ jsou následující:
Příklady SQL COALESCE
Zde je několik příkladů pro lepší pochopení SQL COALESCE.
Příklad 1
Dotaz SQL pro ilustraci použití jednoduché funkce COALESCE.
Kód:
SELECT COALESCE(NULL, NULL, NULL, 'EduCBA', NULL, NULL);
Výstup:
V tomto příkladu můžeme vidět, že funkce COALESCE vybere první hodnotu, která není NULL, narazí na a poté skončí.
Příklad 2
Dotaz SQL k nalezení prvních nenulových hodnot z křestního jména a příjmení zaměstnance.
Kód:
VYBRAT ID zaměstnance,jméno, příjmení,
OD zaměstnanců;
COALESCE(křestní jméno,příjmení) jako 'křestní nenulové jméno'
Výstup:
V tomto příkladu je vráceno první jiné než NULL.
Příklad 3
Dotaz SQL pro ilustraci funkce COALESCE jako speciální případ CASE.
Kód:
VYBRAT ID zaměstnance,jméno, příjmení,
OD zaměstnanců;
PŘÍPAD
KDYŽ křestní jméno NENÍ NULL, TAK křestní jméno
KDYŽ příjmení NENÍ NULL, TAK příjmení ELSE NULL
END jako 'první ne nulová hodnota'
Výstup:
Porovnejte výsledky tohoto dotazu s výsledky dotazu ve třetím příkladu. Vidíme, že oba dávají stejný výsledek. Je to proto, že funkci COALESCE lze považovat za speciální případ CASE.
Příklad 4
Dotaz SQL pro ilustraci použití funkce COALESCE na hodnotách s různými datovými typy.
Kód:
VYBRAT ID zaměstnance,jméno, příjmení,
OD zaměstnanců;
COALESCE(id zaměstnance,jméno,příjmení) jako 'křestní ne nulové jméno'
Výstup:
Ve výše uvedeném příkladu vidíme, že ID zaměstnance nelze spojit s datovými hodnotami jména a příjmení, protože mají různé datové typy.
Abychom tento problém vyřešili, můžeme změnit typ dat zaměstnance pomocí funkce CONVERT, jak je uvedeno níže.
Kód:
VYBRAT ID zaměstnance,jméno, příjmení,
OD zaměstnanců;
COALESCE(CONVERT(varchar(50);id zaměstnance);jméno,příjmení) jako 'první nenulové hodnoty'
Výstup:
Příklad 5
Složitý SQL dotaz pro ilustraci použití funkce COALESCE.
Předpokládejme, že chceme znát platy všech zaměstnanců ve firmě. Ale v tabulce zaměstnanců vidíme, že platy všech zaměstnanců nebyly uvedeny. Byly však zmíněny hodinové sazby a provize zaměstnanců s chybějícími platovými poli. Můžeme to použít k porovnání platů zaměstnanců následujícím způsobem.
Kód:
SELECT COALESCE(CONVERT(varchar(50),id zaměstnance),křestní jméno,příjmení) jako 'Identifikátor zaměstnance',
OBJEDNEJTE DO 2 DOLŮ;
COALESCE(plat,hodinová_sazba830 + COALESCE(provize,0))jako 'kompenzace'
OD zaměstnanců
Výstup:
Pomocí první funkce COALESCE jsme se pokusili identifikovat zaměstnance spojením ID zaměstnance, jména a příjmení dohromady a pomocí druhé funkce COALESCE jsme se pokusili vybrat plat nebo vypočítanou náhradu.
Závěr
Funkce SQL COALESCE je vestavěná funkce v databázích, jako je SQL Server, Azure atd., která vrací první hodnotu než NULL ze vstupního seznamu argumentů. Je velmi užitečný při práci s hodnotami null a slouží jako rychlá a snadná alternativa k příkazům SQL CASE.