Klauzule SQL WITH byla zavedena společností Oracle v databázi Oracle 9i release 2. Klauzule SQL WITH vám umožňuje pojmenovat blok dílčího dotazu (proces nazývaný také refaktoring dílčího dotazu), na který lze odkazovat na několika místech v hlavním dotazu SQL.
- Klauzule se používá k definování dočasného vztahu tak, že výstup tohoto dočasného vztahu je k dispozici a je používán dotazem spojeným s klauzulí WITH.
- Dotazy, které mají přidruženou klauzuli WITH, lze také zapisovat pomocí vnořených poddotazů, ale tím se zvyšuje složitost čtení/ladění dotazu SQL.
- Klauzule WITH není podporována všemi databázovými systémy.
- S názvem přiřazeným k dílčímu dotazu se zachází, jako by se jednalo o vložený pohled nebo tabulku
- Klauzule SQL WITH byla zavedena společností Oracle v databázi Oracle 9i release 2.
Syntax:
WITH temporaryTable (averageValue) as (SELECT avg(Attr1) FROM Table) SELECT Attr1 FROM Table, temporaryTable WHERE Table.Attr1>dočasná tabulka.averageValue;>
V tomto dotazu se klauzule WITH používá k definování dočasného vztahu dočasná tabulka, která má pouze 1 atribut averageValue. averageValue obsahuje průměrnou hodnotu sloupce Attr1 popsanou ve vztahu Tabulka. Příkaz SELECT, který následuje po klauzuli WITH, vytvoří pouze ty n-tice, kde je hodnota Attr1 ve vztahu Tabulka větší než průměrná hodnota získaná z příkazu klauzule WITH.
Poznámka: Při provádění dotazu s klauzulí WITH je nejprve vyhodnocen dotaz uvedený v klauzuli a výstup tohoto vyhodnocení je uložen v dočasné relaci. Poté se nakonec provede hlavní dotaz spojený s klauzulí WITH, který použije vytvořený dočasný vztah.
Dotazy
Příklad 1: Najděte všechny zaměstnance, jejichž plat je vyšší než průměrný plat všech zaměstnanců.
Název vztahu: Zaměstnanec
| ID zaměstnance | název | Plat |
|---|---|---|
| 100011 | Kovář | 50 000 |
| 100022 | Účtovat | 94 000 |
| 100027 | Sám | 70550 |
| 100845 | Walden | 80 000 |
| 115585 | Erik | 60 000 |
| 1100070 | Kate | 69 000 |
SQL dotaz:
WITH temporaryTable(averageValue) as (SELECT avg(Salary) from Employee) SELECT EmployeeID,Name, Salary FROM Employee, temporaryTable WHERE Employee.Salary>dočasnéTable.averageValue;>
Výstup:
| ID zaměstnance | název | Plat |
|---|---|---|
| 100022 | Účtovat | 94 000 |
| 100845 | Walden | 80 000 |
Vysvětlení: Průměrný plat všech zaměstnanců je 70591. Všichni zaměstnanci, jejichž plat je vyšší než získaný průměr, tedy leží ve výkonové relaci.
Příklad 2: Najděte všechny letecké společnosti, kde je celkový plat všech pilotů v dané letecké společnosti vyšší než průměr celkového platu všech pilotů v databázi.
Název vztahu: Pilot
| ID zaměstnance | Letecká linka | název | Plat |
|---|---|---|---|
| 70007 | Airbus 380 | Kim | 60 000 |
| 70002 | Boeing | Laura | 20 000 |
| 10027 | Airbus 380 | Vůle | 80050 |
| 10778 | Airbus 380 | Warren | 80780 |
| 115585 | Boeing | Kovář | 25 000 |
| 114070 | Airbus 380 | Katy | 78 000 |
SQL dotaz:
WITH totalSalary(Airline, total) as (SELECT Airline, sum(Salary) FROM Pilot GROUP BY Airline), airlineAverage(avgSalary) as (SELECT avg(Salary) FROM Pilot ) SELECT Airline FROM totalSalary, airlineAverage WHERE totalSalary.total>airlineAverage.avgSalary;>
Výstup:
java převést celé číslo na řetězec
| Letecká linka |
|---|
| Airbus 380 |
Vysvětlení: Celkový plat všech pilotů Airbusu 380 = 298 830 a Boeingu = 45000. Průměrný plat všech pilotů v tabulce Pilot = 57305. Protože pouze celkový plat všech pilotů Airbusu 380 je větší než dosažený průměrný plat, tak Airbus 380 leží ve výkonovém vztahu.
Důležité body:
- Klauzule SQL WITH je vhodná při použití se složitými příkazy SQL, spíše než s jednoduchými
- Umožňuje také rozdělit složité dotazy SQL na menší, což usnadňuje ladění a zpracování složitých dotazů.
- Klauzule SQL WITH je v podstatě náhradou za normální poddotaz.