Podmíněné výrazy
Tato kapitola měla být původně součástí
kapitoly o funkcích. Ale protože by byla
už neúměrně dlouhá, vytvořil jsem pro podmíněné výrazy vlastní
kapitolu. Naučíte se tu další důležité funkce a direktivu CASE
.
Podmíněné výrazy
Podmíněný výraz (conditional expression)
by se dal zjednodušeně popsat jako „Když něco, tak udělej toto, když něco
jiného, tak udělej tamto, když ani to ne, udělej tohleto“.
Podmíněné výrazy reprezenuje především direktiva CASE
.
Kromě direkrvy CASE
se v PostgreSQL ještě k podmíněným výrazům počítají
některé funkce – COALESCE
, NULLIF
, GREATEST
,
a LEAST
.
CASE
Direktiva CASE
vypadá takto:
Obšlehnuto z dokumentace PostgreSQL
Popis directivy CASE v dokumentaci SQLite
Direktiva vždy začíná klíčovým slovem CASE
,
vždy končí klíčovým slovem END
a vždy má alespoň jednu WHEN … THEN …
část.
Podmínka condition
se vyhodnotí jako booleovský výraz (pravda nebo nepravda).
Pokud je pravda, výsledkem celého CASE
je vyraz
.
Pokud je nepravda, zkusí se to v další WHEN … THEN …
části.
Když není splněna žádná condition
, výsledkem je vyraz
za ELSE
.
Když není ELSE vyraz
definováno, tak už zbývá jako výsledek jenom NULL
.
Možná to zní složitě, ale z příkladu se to dá pochopit snadno:
Nezapomeňte na ukončení CASE
klíčovým slovem END
.
Na to se rádo zapomíná.
Ještě jeden příklad:
Jedna se sice rovná jedné, ale výsledkem CASE
je první
WHEN … THEN …
, kde je podmínka (condition) pravda.
Pokud porovnáváte jednu hodnotu s nějakými dalšími hodnotami, dá se zápis CASE
zjednodušit. Porovnáním myslím použití operátoru =
ve všech podmínkách.
Pozor! Nelze, resp. nemá smysl, porovnávat cokoliv s NULL
, protože NULL
se ničemá nerovná, ani sama sobě (viz předchozí příklad).
Následující dva příkazy dělají totéž.
V druhém příkazu je za CASE
hodnota,
která se porovnává s hodnotami za WHEN
(může to být sloupeček, nebo nějaký výraz, který se vyhodnotí a pak porovnává):
CASE
WHEN nazev = 'Sekretariat' THEN 3000
WHEN nazev = 'Pravni oddeleni' THEN 2000
ELSE 0
END AS "prémie"
FROM oddeleni;
nazev | prémie
-----------------+--------
Sekretariat | 3000
Pravni oddeleni | 2000
Pravni oddeleni | 2000
(3 řádky)
rimmer1=> SELECT nazev,
CASE nazev
WHEN 'Sekretariat' THEN 3000
WHEN 'Pravni oddeleni' THEN 2000
ELSE 0
END AS "prémie"
FROM oddeleni;
nazev | prémie
-----------------+--------
Sekretariat | 3000
Pravni oddeleni | 2000
Pravni oddeleni | 2000
(3 řádky)
Výhoda druhého zápisu je čistě v tom, že vám ušetří trochu psaní.
CASE
můžete používat v SELECTu, v podmínce WHERE, v ORDER BY – prostě
všude tam, kde se dají používat aritmetické výrazy, funkce nebo sloupečky.
CASE plat/10000 WHEN 1 THEN 1 ELSE 0 END
FROM zamestnanci
ORDER BY CASE plat/10000 WHEN 1 THEN 1 ELSE 0 END DESC, plat ASC;
jmeno | prijmeni | plat | case
---------+----------+-------+------
Jana | Pavova | 10000 | 1
Lenka | Pavova | 10000 | 1
Martin | Luter | 12000 | 1
Jana | Mala | 12000 | 1
Leopold | King | 13000 | 1
Lenka | Pavova | 15000 | 1
Tom | Jerry | 15000 | 1
Vasek | Trn | 16000 | 1
Stary | Osel | 9000 | 0
Tomas | Mann | 22000 | 0
Pan | Novy | | 0
(11 řádek)
plat/10000
je celočíselné dělení, tj. výsledek je beze zbytku (bez desetinného čísla).
Výsledek je seřazen podle platu s tím, že ti co mají plat mezi 10000 a 19999 jsou mezi prvními.
COALESCE
COALESCE
je funkce, která vrátí svůj první argument, který není NULL
.
Když jsou NULL
všechny argumenty, pak vrátí NULL
.
Všechny argumenty musí být stejného typu (není možné, aby ve výsledné tabulce v jednom sloupci
byli někde čísla a někde text. Hodnoty sloupce v tabulce musí být vždy jednoho datového typu).
Sloupec plat je typu integer, proto očekává COALESCE
že další argumenty už taky budou integer.
Naštěstí se dá sloupec vždycky přetypovat na text:
NULL
jsem přidal do příkladu jen proto, abyste viděli, že COALESCE
může mít víc než jenom 2 argumenty.
Podmínka WHERE
by se dala přepsat trochu čitelnějším způsobem takto:
WHERE plat NOT BETWEEN 10000 and 19999 OR plat IS NULL;
.
To co dělá COALESCE
by šlo napsat i pomocí CASE
(vyzkoušejte za domácí úkol),
COALESCE
je ale stručnější a tak i čitelnější.
NULLIF
Funkce NULLIF
vrací NULL
, když se její první
argument rovná jejímu druhému argumentu, jinak vrátí první argument.
Může se použít jako inverzní funkce k COALESCE
. Taktéž platí,
že se její funkce dá přepsat pomocí CASE
, jen by to bylo o dost ukecanější.
COALESCE(plat::text, '-'),
NULLIF(COALESCE(plat::text, '-'), '-')::integer
FROM zamestnanci
WHERE plat NOT BETWEEN 10000 and 19999 OR plat IS NULL;
jmeno | prijmeni | plat | coalesce | nullif
-------+----------+-------+----------+--------
Tomas | Mann | 22000 | 22000 | 22000
Stary | Osel | 9000 | 9000 | 9000
Pan | Novy | | - |
(3 řádky)
Výsledek NULLIF
jsem schválně přetypoval na integer,
aby se sloupeček zarovnal do prava, jak je u čísel zvykem.
GREATEST a LEAST
Funkce GREATEST
a LEAST
vrací největší, resp. nejmenší hodnotu ze svých argumentů.
Hodnoty NULL
ignoruje, takže když se mezi argumenty objeví, nebude výsledek NULL
(ač by k tomu logika významu NULL
sváděla).
MySQL/MariaDB
MySQL nedělí dvě celá čísla celočíselně, ale výsledkem je číslo s desetinnou částí.
Proto se musí z výsledku dělení odstanit desetinná část pomocí funkce FLOOR
.
Jinak příklad s CASE
funguje jako v Postgresu.
MySQL automaticky převede datový typ na text, když je potřeba, takže následující příkaz funguje.
FROM zamestnanci WHERE CASE FLOOR(plat/10000) WHEN 1 THEN 1 ELSE 0 END = 0;
+-------+----------+-------+--------------------+
| jmeno | prijmeni | plat | COALESCE(plat,'-') |
+-------+----------+-------+--------------------+
| Tomas | Mann | 22000 | 22000 |
| Stary | Osel | 9000 | 9000 |
| Pan | Novy | NULL | - |
+-------+----------+-------+--------------------+
3 rows in set (0.00 sec)
Napoak MySQL neumí přetypovávání datových typů pomocí dvou dvouteček
::
(na přetypování
používá něco jiného). Takže musíte uvádět příkazy bez nich:
COALESCE(plat, '-') AS coalesce,
NULLIF(COALESCE(plat, '-'), '-') AS nullif
FROM zamestnanci WHERE plat NOT BETWEEN 10000 and 19999 OR plat IS NULL;
+-------+----------+-------+----------+--------+
| jmeno | prijmeni | plat | coalesce | nullif |
+-------+----------+-------+----------+--------+
| Tomas | Mann | 22000 | 22000 | 22000 |
| Stary | Osel | 9000 | 9000 | 9000 |
| Pan | Novy | NULL | - | NULL |
+-------+----------+-------+----------+--------+
3 rows in set (0.00 sec)
MySQL neignoruje NULL
hodnoty ve funkcích GREATEST
či LEAST
:
IF
MySQL má navíc funkci IF
, která vypadá takto:
Funkce IF
vrací result1
, pokud se condition
vyhodnotí jako
pravda, jinak result2
.
IFNULL
Funkce IFNULL
(neplést s NULLIF
) dělá něco podobného jako
COALESCE
. Má jen dva argumenty. Pokud je první NULL
,
vrací druhý argument, jinak první.
FROM zamestnanci
WHERE CASE FLOOR(plat/10000) WHEN 1 THEN 1 ELSE 0 END = 0;
+-------+----------+-------+--------+
| jmeno | prijmeni | plat | ifnull |
+-------+----------+-------+--------+
| Tomas | Mann | 22000 | 22000 |
| Stary | Osel | 9000 | 9000 |
| Pan | Novy | NULL | - |
+-------+----------+-------+--------+
3 rows in set (0.00 sec)
SQLite
SQLite, stejně jako MySQL, neumí přetypovávat pomocí dvou dvouteček ::
,
takže všechny příklady se musí psát bez tohoto přetypování (a fungují bez něj).
SQLite dělí celá čísla celočíselně, takže jako v PostgreSQL se nemusí ořezávat pomocí
funkce FLOOR
(kterou SQLite stejně nemá :-).
SQLite nezná funkce GREATEST
ani LEAST
, ani podmínku
IF
, zato umí IFNULL
(i NULLIF
).
Vše ostatní funguje dle očekávání.
Oracle
Oracle dělí celá čísla neceločíselně, takže se musí desetinná část oddělit pomocí
funkce FLOOR
, stejně jako v MySQL.
Oracle ve funkci COALESCE
automaticky nepřetypuje hodnoty arguemtnů na text,
když je potřeba, ani neumí použít ::
,
takže se musí použít funkce TO_CHAR
:
Totéž platí pro další funkce:
COALESCE(TO_CHAR(plat), '-') AS coalesce,
TO_NUMBER(NULLIF(COALESCE(TO_CHAR(plat), '-'), '-')) AS nullif
FROM zamestnanci
WHERE plat NOT BETWEEN 10000 and 19999 OR plat IS NULL;
JMENO PRIJMENI PLAT COALESCE NULLIF
Stary Osel 9000 9000 9000
Tomas Mann 22000 22000 22000
Pan Novy - - -
Oracle neignoruje NULL
ve funkcích GREATEST
a LEAST
.
DECODE, NVL, NVL2
Oracle neumí jako MySQL funkce IF
ani IFNULL
. Zato má pár
vlastních:
DECODE
,
NVL
(totéž co IFNULL
) a
NVL2
.
Z příkladů z dokumentace je jasné co dělají a jak se používají, tak vám jejich studium nechám za domácí úkol.
A mimochodem, jako vždy, všechny tyto funkce lze nahradit pomocí CASE
.