YDASYS1

1. YDASYS1 Ing. Monika Šimková

2. Typy spojení tabulek • V mnoha případech potřebujeme do výsledku dotazu získat data sloupců, obsažených v různých tabulkách a proto je nutné tabulky spojit. • Jazyk SQL umožňuje vytvoření různých typů spojení: • vnitřní spojení (INNER JOIN) – odpovídá přirozenému spojení relací • vnější spojení (OUTER JOIN) LEFT OUTER JOIN • RIGHT OUTER JOIN • FULL OUTER JOIN • křížové spojení (CROSS JOIN) – odpovídá kartézskému součinu relací

3. Syntaxe dotazu na spojené tabulky SELECT {jménoSloupce} [,...] FROM { jménoTabulky I jménoPohledu } [[ INNER | { LEFT | RIGHT | FULL } OUTER] JOIN { jménoTabulky I jménoPohledu } ON VyhledávacíPodmínky ] [,...n] • JOIN definuje spojované tabulky • ON udává propojovací sloupce tabulek • Je-li uveden pouze JOIN, vykoná se INNER JOIN

4. Alternativní syntaxe vnitřního spojení tabulek Dotaz se spojením tabulek lze zapsat i bez použití klauzule JOIN a ON. Tabulky uvedeme v klauzuli FROM jako seznam oddělený čárkami a spojovací podmínku uvedeme v klauzuli WHERE. SELECT {jménoSloupce} [,...] FROM { jménoTabulky} [,...] WHERE spojovacíPodmínky

5. Příklad: Spojení 2 tabulek Vytvořte seznam titulů, kde bude uveden název vydavatele a název vydaných titulů - seřazeno dle vydavatele a názvu titulu. SELECT vydavatele.vyd_nazev, tituly.nazevFROM vydavatele INNER JOIN tituly ON vydavatele.vyd_id = tituly.vyd_id ORDER BY vydavatele.vyd_nazev, tituly.nazev • Poznámky: • INNER nemusíme uvádět (předpokládá se implicitně) • Názvy tabulek lze v klauzuli FROM pro účely příkazu přejmenovat (zkrátit) a tím zpřehlednit příkaz.

6. Příklad: Použití ALIAS Předchozí příkaz lze za použití ALIAS zapsat následovně: SELECT V.vyd_nazev, T.nazev FROM vydavatele V INNER JOIN tituly T ON V.vyd_id = T.vyd_id ORDER BY V.vyd_nazev, T.nazev Poznámka: V klauzuli FROM byla tabulka vydavatele přejmenována na tabulku V a tabulka tituly na tabulku T. V ostatních částech příkazu proto musíme použít nový název tabulek.

7. Příklad: Spojení 2 tabulek a group by (1) • Kolik titulů vydali jednotliví vydavatelé? Ve výsledku uveďte název vydavatele a počet vydaných titulů. SELECT V.vyd_nazev, COUNT(T.nazev) AS Pocet_titulu FROM tituly T INNER JOIN vydavatele V ON T.vyd_id = V.vyd_id GROUP BY V.vyd_nazev • Kolik je autorů jednotlivých žánrů (typů)? select typ, count (distinct AT.au_id) from autor_titul AT join tituly T on AT.titul_id= T.titul_id groupby T.typ

8. Příklad: Spojení 2 tabulek a group by (2) Kolik titulů jednotlivých typů vydali jednotliví vydavatelé? Ve výsledku uveďte název vydavatele, typ a počet vydaných titulů. SELECT V.vyd_nazev, typ, COUNT(T.nazev) AS Pocet_titulu FROM tituly T JOIN vydavatele V ON T.vyd_id = V.vyd_id GROUP BY V.vyd_nazev, typ ORDER BY V.vyd_nazev

9. Příklad: Spojení 2 tabulek a group by (3) • Kolik autorů mají jednotlivé tituly? Ve výsledku uveďte název titulu a počet autorů selectAT.titul_id, T.nazev, count (AT.au_id) PocetAutoru from autor_titul AT join tituly T on AT.titul_id = T.titul_id groupby AT.titul_id, T.nazev

10. Příklad: Spojení více tabulek • Zobrazte následující údaje potřebné pro výpis řádků faktury: číslo faktury, identifikace titulu, název titulu, příjmení a jméno 1. autora titulu, objednané množství, dodané množství

11. Příklad: Spojení více tabulek - rešení SELECT DISTINCT RO.faktura_id, RO.mnozstviObj, RO.mnozstviDodane, RO.titul_id, T.nazev, A.au_id, AT.poradiAut, A.au_prijmeni, A.au_jmeno FROM radekObjednavky RO INNER JOIN tituly T ON RO.titul_id = T.titul_id INNER JOIN autor_titul AT ON T.titul_id = AT.titul_id INNER JOIN autori A ON AT.au_id = A.au_id WHERE (AT.poradiAut = 1) ORDER BY RO.faktura_id, RO.titul_id

12. Příklad: Spojení tabulky se sebou Zobrazte všechny informace o autorech, kteří bydlí v tomtéž městě. selectdistinct A1.* from autori A1, autori A2 where A1.mesto = A2.mesto and A1.au_id != A2.au_id order by A1.mesto, A1.au_prijmeni • Poznámka: Tabulka vystupuje v příkazu ve dvou rolích a toto umožní porovnávat mezi sebou hodnoty stejného sloupce v rámci jedné tabulky.

13. Příklad: Použití alternativního klíče Zobrazte jméno a příjmení editorů, kteří jsou zároveň i autory. Pozor: Editoři a autoři jsou v nezávislých tabulkách, proto v dotazu je vhodné použít kandidátní klíč jednoznačně identifikující autora i editora a tím může být například telefonní číslo, případně kombinace jména, příjmení a telefonu (všechny položky by ale měly být nastaveny na NOT NULL). use Nakladatelstvi selected_id, ed_prijmeni, ed_jmeno fromeditori, autori where editori.tel = autori.tel

14. Vnější spojení • V některých situacích potřebujeme získat při spojení tabulek všechny řádky jedné nebo obou tabulek bez ohledu na to, zda splňují podmínku spojení. • Například můžeme potřebovat seznam všech vydavatelů doplněný o informaci který z editorů bydlí ve stejném městě. • Pro tyto případy je možné využít levé, pravé nebo plné vnější spojení.

15. Příklad: Levé vnější spojení • Vytvořte seznam všech vydavatelů doplněný o informaci který z editorů bydlí ve stejném městě. selectV.vyd_nazev, E.ed_prijmeni, E.ed_jmeno, E.poziceEd, E.tel, E.mesto from vydavatele V LEFT OUTER join editori E on V.mesto= E.mesto

16. Příklad: Pravé vnější spojení Vytvořte seznam všech editorů doplněný o informaci který z vydavatelů sídlí ve stejném městě. selectV.vyd_nazev, E.ed_prijmeni, E.ed_jmeno, E.poziceEd, E.tel, E.mesto from vydavatele V RIGHT OUTER join editori E on V.mesto= E.mesto

17. Příklad: Plné vnější spojení selectV.vyd_nazev, E.ed_prijmeni, E.ed_jmeno, E.poziceEd, E.tel, E.mesto from vydavatele V FULL OUTER join editori E on V.mesto= E.mesto

18. Příklad: Vnější spojeni vs. poddotaz Který titul nebyl dosud objednán? selecttitul_idfrom tituly where titul_id not in (select titul_id from radekObjednavky) Alternativně: select T.titul_id, nazev, RO.titul_id from tituly T LEFT OUTER JOIN radekObjednavky RO on T.titul_id= RO.titul_id whereRO.titul_id IS NULL

19. Poddotazy • Poddotazy představují dotazy vložené dovnitř jiného dotazu, vkládat lze teoreticky do libovolné hloubky. • Poddotazmůže vrátit jednu hodnotu, kterou lze porovnat s jinou hodnotou v rámci WHERE. • Poddotazmůže vrátit relaci, kterou lze různě použít v rámci klauzule WHERE. • Poddotazse může použít v rámci klauzule FROM stejně jako každá jiná relace. • Poddotazymohoubýtpoužityi v příkazech INSERT, UPDATE, and DELETE. • Poddotazypožíváme často v dotazech, které závisí na výsledku jiného dotazu.

20. Vnořený a souvztažný poddotaz • Poddotazy můžeme psát jako vnořené nebo souvztažné (korelované). • Vnořený poddotaz se při vykonávání vnějšího dotazu vykoná pouze jednou. • Souvztažný poddotaz se při provádění vnějšího dotazu vykoná jednou pro každý navrácený řádek vnějšího dotazu.

21. Příklad: Vnořený poddotaz • Kterých titulů se prodalo více než je průměr a o kolik? SELECT titul_id, nazev, prodanoKs, prodanoKs- (select avg(prodanoKs) from tituly) AS rozdil FROM tituly WHERE prodanoKs > (SELECT avg(prodanoKs) FROM tituly)

22. Příklad: Vnořený poddotaz – více úrovní Kteří autoři participovali na titulech z oblasti psychologie (typ = ‘psychology’)? Uveďte jméno a příjmení autorů. selectau_jmeno, au_prijmeni fromautori whereau_id in (selectau_id fromautor_titul wheretitul_id in (selecttitul_id from tituly where typ='psychology'))

23. Pravidla pro použití vnořených poddotazů • Poddotaz, který vrací jednu hodnotu, můžeme ve vnějším dotazu zapsat kdekoli na místě výrazu. • Do klauzule WHERE můžeme poddotaz zapsat s vhodným operátorem porovnání. • V případě, že poddotaz vrací celý sloupec hodnot, můžeme využít operátory IN – na testování příslušnosti k množině hodnot, ALL – na testování, zda všechny hodnoty vrácené poddotazemsplňují danou podmínku, ANY – na testování, zda alespoň jedna hodnota vrácenápoddotazemsplňuje danou podmínku.

24. Souvztažné poddotazy • U souvztažného poddotazu využívá vnitřní dotaz (poddotaz) také informace z vnějšího dotazu, takže se musí provádět zvlášť pro každý jednotlivý řádek vnějšího dotazu. • Souvztažné poddotazy se často dají přeformulovat do tvaru spojení tabulek. U operace spojení tabulek dokáže optimalizátor dotazů SQL serveru správně rozhodnout o nejefektivnější realizaci dotazu, takže dotaz se spojenými tabulkami je vhodnější než souvztažný poddotaz.

25. Operátory EXISTS a NOT EXISTS • Pomocí operátorů EXISTS a NOT EXISTS lze snadno omezit výslednou množinu vnějšího dotazu jen na ty řádky, které vyhovují podmínce poddotazu. • Jako výsledek tyto operátory produkují pouze true/false • True právě tehdy, když existuje alespoň jeden řádek v tabulce, kterou vrátí poddotaz. • False právě tehdy, když poddotaz vrátí prázdnou tabulku. • Protože EXISTS testuje pouze existenci řádků ve výsledné tabulce poddotazu, výsledek poddotazu může obsahovat libovolný počet sloupců. Vhodné je použití * jako zástupného symbolu.

26. Příklad: Použití EXISTS • Máme najít vydavatele, kteří vydávají tituly typu ‘business’ • selectdistinctvyd_nazev • from vydavatele • whereexists • (select * • from tituly • wheretituly.vyd_id = vydavatele.vyd_id • and tituly.typ = 'business')