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:

CASE WHEN condition THEN vyraz
     [WHEN ...]
     [ELSE vyraz]
END

Obšlehnuto z dokumentace PostgreSQL

SQLite CASE

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:

rimmer1=> SELECT CASE
WHEN 1 < 0 THEN 'jedna je mensi jak 0'
WHEN 1 > 0 THEN 'jedna je vetsi jak 0'
ELSE 'jedna se rovna nula'
END
FROM dual;
         case        
----------------------
 jedna je vetsi jak 0
(1 řádka)

Nezapomeňte na ukončení CASE klíčovým slovem END. Na to se rádo zapomíná.

Ještě jeden příklad:

rimmer1=> SELECT CASE
WHEN NULL = NULL THEN 'NULL se rovna NULL'
WHEN NULL IS NULL THEN 'NULL je NULL'
WHEN 1 = 1 THEN 'jedna je jedna'
END AS "Co je co"
FROM dual;
   Co je co  
--------------
 NULL je NULL
(1 řádka)

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á):

rimmer1=> SELECT nazev,
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.

rimmer1=> SELECT jmeno, prijmeni, plat,
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).

rimmer1=> SELECT jmeno, prijmeni, plat, COALESCE(plat,'-')
FROM zamestnanci WHERE CASE plat/10000 WHEN  1 THEN 1 ELSE 0 END = 0;
ERROR:  invalid input syntax for integer: "-"
ŘÁDKA 2: COALESCE(plat,'-')

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:

rimmer1=> SELECT jmeno, prijmeni, plat, COALESCE(plat::text, NULL, '-')
FROM zamestnanci WHERE CASE plat/10000 WHEN  1 THEN 1 ELSE 0 END = 0;
 jmeno | prijmeni | plat  | coalesce
-------+----------+-------+----------
 Tomas | Mann     | 22000 | 22000
 Stary | Osel     |  9000 | 9000
 Pan   | Novy     |       | -
(3 řádky)

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ší.

rimmer1=> SELECT jmeno, prijmeni, plat,
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).

rimmer1=> SELECT GREATEST(3, 5,-100, NULL, 50, 3.7) FROM dual;
 greatest
----------
       50
(1 řádka)

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.

SELECT jmeno, prijmeni, plat,
CASE FLOOR(plat/10000) WHEN 1 THEN 1 ELSE 0 END
FROM zamestnanci
ORDER BY CASE FLOOR(plat/10000) WHEN 1 THEN 1 ELSE 0 END DESC, plat ASC;

MySQL automaticky převede datový typ na text, když je potřeba, takže následující příkaz funguje.

mysql> SELECT jmeno, prijmeni, plat, COALESCE(plat,'-')
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:

mysql> SELECT jmeno, prijmeni, plat,
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:

SELECT GREATEST(3, 5,-100, NULL, 50, 3.7) FROM dual;
+------------------------------------+
| GREATEST(3, 5,-100, NULL, 50, 3.7) |
+------------------------------------+
|                               NULL |
+------------------------------------+
1 row in set (0.00 sec)

IF

MySQL má navíc funkci IF, která vypadá takto:

IF(condition, result1, result2)

Funkce IF vrací result1, pokud se condition vyhodnotí jako pravda, jinak result2.

mysql> SELECT IF(1 < 0, 'jedna je mensi jak 0', 'jedna neni mensi jak 0') AS result FROM dual;
+------------------------+
| result                 |
+------------------------+
| jedna neni mensi jak 0 |
+------------------------+
1 row in set (0.00 sec)

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

mysql> SELECT jmeno, prijmeni, plat, IFNULL(plat,'-') AS ifnull
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:

oracle> SELECT jmeno, prijmeni, plat, COALESCE(TO_CHAR(plat),  '-')
FROM zamestnanci
WHERE CASE FLOOR(plat/10000) WHEN  1 THEN 1 ELSE 0 END = 0;

JMENO        PRIJMENI        PLAT        COALESCE(TO_CHAR(PLAT),'-')
Stary        Osel            9000        9000
Tomas        Mann           22000        22000
Pan          Novy               -        -

Totéž platí pro další funkce:

oracle> SELECT jmeno, prijmeni, plat,
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.

oracle> SELECT GREATEST(3, 5,-100, NULL, 50, 3.7) AS greatest FROM dual;
GREATEST
-

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.

Komentář Hlášení chyby
Created: 11.12.2013
Last updated: 10.9.2015
Tato stánka používá ke svému běhu cookies, díky kterým je možné monitorovat, co tu provádíte (ne že bych to bez cookies nezvládl). Také vás tu bude špehovat google analytics. Jestli si myslíte, že je to problém, vypněte si cookies ve vašem prohlížeči, nebo odejděte a už se nevracejte :-). Prohlížením tohoto webu souhlasíte s používáním cookies. Dozvědět se více..