Lab 2

`NATURAL JOIN`

NATURAL JOIN to bardzo brzydkie narzędzie. Łatwo wyobrazić sobie sytuację, w której nazwa kolumny w jednej z tabel się zmienia i wtedy zapytanie przestanie działać jak powinno. Generalnie poleganie na konwencjach nazewniczych z reguły prowadzi do trudnego do debugowania kodu, więc polecam pisać joiny explicite wymieniając warunek złączenia.

Zadanie 1.

Jest kilka sposobów na zrobienie tego, jeden z nich jest bardzo naturalny dla programisty, drugi bardzo naturalny od strony algebry zapytań i logiki. Na koniec labu wrócimy do tego przykładu.

Zadanie 2.

“Bez łączenia tabel” znaczy bez użycia słowa JOIN, ale z wykorzystaniem podzapytań. W rzeczywistości podzapytania są z reguły wykonywane poprzez złączenie podzapytania z główną tabelą – będziemy o tym mówić przy analizie wykonania zapytań. Tekst skryptu trochę wprowadza w błąd, dlatego to zaznaczam.

Zapytania w tym zadaniu są takie same jak w pierwszym, tylko teraz nie używamy JOINów.

"Mała czytelność’’ – `WITH ... AS`

Autor twierdzi, że podzapytania proawdzą do małej czytelności. Ale jest tak tylko dlatego, że postanowił wybrać tę nieczytelną wersję pisania podzapytań. W rzeczywistości podzapytania wręcz zwiększają czytelność, gdyż pozwalają wyciągnąć kawałek zapytania do osobnej klauzuli, tak jak w normalnych językach gdy tworzymy funkcje pomocnicze. Korzysta się do tego z konstrukcji WITH .. AS. Na przykład, zapytanie:

SELECT * 
  FROM emp 
  WHERE deptno NOT IN (
    SELECT deptno 
      FROM dept 
      WHERE loc = 'CHICAGO'
   );

możemy zapisać jako:

WITH chicago_dept AS
(
  SELECT deptno
    FROM dept
    WHERE loc = 'CHICAGO'
)
SELECT *
  FROM emp
  WHERE deptno NOT IN (SELECT deptno FROM chicago_dept);

Robi się to widocznie przydatniejsze przy długich podzapytaniach, zwłaszcza gdy zaczniemy je bardziej zagnieżdżać.

Weźmy następujące zapytanie: chcemy wybrać wszystkich pracowników, którzy pracują w innym mieście niż ich manager. Bez podzapytań napisalibyśmy coś takiego:

SELECT *
  FROM emp e1
  JOIN emp e2
    ON e1.mgr = e2.empno
  JOIN dept d1
    ON e1.deptno = d1.deptno
  JOIN dept d2
    ON e2.deptno = d2.deptno
  WHERE d1.loc != d2.loc

Używając konstrukcji WITH AS możemy usunąć stąd pewien wspólny element: znalezienie lokalizacji dla każdego pracownika:

WITH emp_loc AS (
  SELECT e.empno, e.mgr, d.loc
    FROM emp e
    JOIN dept d
      ON e.deptno = d.deptno
)
SELECT *
  FROM emp_loc e1
  JOIN emp_loc e2
    ON e1.mgr = e2.empno
  WHERE e1.loc != e2.loc

WITH .. AS to prosty sposób na uniknięcie kiludziesięciolinijkowych potworków, które czasem powstają przy bardziej skomplikowanych zapytaniach.

Co ciekawe, w przypadku Oracla postać zapytania z WITH ... AS jest kompilowana efektywniej niż ta z zagnieżdżonym podzapytaniem inline.

Notabene: jeśli chcemy mieć więcej niż jedno podzapytanie to składnia jest taka:

WITH subquery1 AS (
  ...
),
subquery2 AS (
  ...
),
...
subqueryN AS (
  ...
)
SELECT ...

Podpunkt 6

Przypomnijmy zadanie: “Który pracownik pracuje w firmie najdłużej?”

Wspomnieliśmy, że da się to zrobić na wiele różnych sposobów. Po pierwsze, być może naturlanym sposobem wydaje się posortowanie pracowników po dacie i wybranie pierwszego:

SELECT *
  FROM emp e
ORDER BY e.hiredate
FETCH NEXT 1 ROW ONLY;

Jednakże to nie musi działać tak, jak moglibyśmy chcieć. Jeśli kilku pracowników będzie miało tę samą datę zatrudnienia, wybierzemy tylko jednego z nich. Dodatkowo SQL nie daje żadnych gwarancji na temat kolejności zwracanych wierszy (poza tym co wyspecyfikujemy w ORDER BY), więc będzie to niedeterministycznie wybrany pracownik.

Możemy to naprawić podwyrażeniem:

WITH min_hiredate AS (
   SELECT e.hiredate
     FROM emp e
   ORDER BY e.hiredate
   FETCH NEXT 1 ROW ONLY
)
SELECT *
  FROM emp e
  WHERE e.hiredate = (SELECT hiredate FROM min_hiredate);

Jak przekonamy się na kolejnych labach, SQL udostępnia nam różne sposoby agregacji danych. Tutaj pomocna jest funkcja minimum, która upraszcza nasze zapytanie:

WITH min_hiredate AS (
  SELECT MIN(e.hiredate) AS hiredate
    FROM emp e
)
SELECT e.*
  FROM emp e
  WHERE e.hiredate = (SELECT hiredate FROM min_hiredate);

Jak powinniście wiedzieć z wykładu, algebra relacji nie zawiera agregacji (ani czegoś podobnego do ORDER BY). Korzystając jednak z równoważności pomiędzy logiką pierwszego rzędu a algebrą relacji możemy dotrzeć do zapytania SQL.

Predykat “pracownik $x \in Emp$ jest najstarszym zatrudnionym” jest dość prosty:

$oldest(x) := \forall_{y \in Emp}~hiredate(x) \leq hiredate(y)$

Czyli to samo co:
$oldest(x) := \neg \exists_{y \in Emp}~hiredate(x) > hiredate(y)$

Tłumacząc to na algebrę relacji dostajemy operację na zbiorze $Emp \times Emp$ :

$\pi_1 (Emp \times Emp - \sigma_{1.hiredate > 2.hiredate} (Emp \times Emp))$

Skąd już łatwo dostać interesującego nas SQL-a:

SELECT e1.*
  FROM emp e1
  LEFT JOIN emp e2
    ON e1.hiredate > e2.hiredate
  WHERE e2.empno IS NULL

Oczywiście to zapytanie będzie mniej efektywne z powodu potencjalnie kwadratowego JOINa, aczkolwiek baza danych całkiem nieźle radzi sobie z optymalizacją tego. Jest to za to na pewno ładne zobrazowanie związku FO z RA i SQLem.

SQL pozwala nam też bezpośrednio przetłumaczyć oryginalną formułe w FO na zapytanie: