Oskar Skibski, grupa nr 4, sala 2043

Bazy danych 2015/16

Laboratorium 2 (12.10.2015) - SQL / ŁĄCZENIE TABEL

Na dzisiejszych zajęcia dowiemy się jak łączyć tabelę i jak można ich nie łączyć, a i tak dostać co się chce.

Zapytania na wielu tabelach

Dotychczas wszystkie nasze zapytania wybierały wiersze z jednej tabeli. W tej sekcji nauczymy się wybierać wiersze z tabel złączonych ze sobą.

Jeżeli chcemy wybrać wiersze z wielu tabel naraz wystarczy że wymienimy je wszystkie w sekcji FROM. Jeżeli któreś pola w tabelach powtarzają się musimy odwoływać się do nich poprzedzając je nazwą tabeli z kropką, np. dept.deptno. Możemy także użyć aliasów i dzięki nim odwoływać się do tabel w krótszy sposób.

SELECT ... FROM emp, dept WHERE ...;

Bardzo ważne jest dobre zrozumienie jak działa mechanizm łączenia tabel. Wykonanie zapytania polega bowiem na stworzeniu iloczynu kartezjańskiego z wszystkich tabel wypisanych w sekcji FROM, następnie wybraniu wierszy spełniających warunki z WHERE i na koniec wypisanie wyniku (wyliczając wartości kolumn wypisanych w SELECT). Jeżeli chcemy na przykład wypisać nazwisko pracownika razem z nazwą departamentu i miasta w którym ten departament się znajduje musimy dodać do WHERE warunek złączenia tabel:

SELECT ename, dname, loc FROM emp, dept WHERE emp.deptno = dept.deptno;

Pole deptno występuje w obu tabelach, dlatego musimy wyspecyfikować o którym polu mówimy poprzedzając je nazwą tabeli.

Tabele łączyć możemy także z nią samą. Ta możliwość przydaje się, kiedy chcemy porównać wiersze między sobą. Przykładowo:

SELECT A.ename, A.sal, B.ename, B.sal FROM emp A, emp B WHERE A.mgr = B.empno;

Powyższe zapytanie zwraca mniej rekordów niż jest pracowników. Z czego wynika ta rozbieżność? Otóż na liście nie ma pracowników, którzy nie mają przełożonego - warunek WHERE wyeliminował wszystkie ich wiersze z iloczynu kartezjańskiego, ponieważ nie odpowiadał im żaden wiersz mający empno równe NULL. Jak poradzić sobie z tym problemem?

Z pomocą przychodzą nam inne sposoby łączenia tabel. W SQL istnieje ich cztery:

• JOIN - łączy dwie tabele według warunku odrzucając wszystkie wiersze dla których nie istnieje odpowiednik w drugiej tabeli
• LEFT/RIGHT JOIN - łączy dwie tabele według warunku odrzucając tylko te wiersze dla których nie istnieje odpowiednik w prawej/lewej tabeli
• FULL JOIN - łączy dwie tabele według warunku nie odrzucając żadnych wierszy

Aby wybrać wiersze ze złączonych tabel używamy następującej konstrukcji:

SELECT ... FROM tabela_1 JOIN tabela_2 ON warunek WHERE ...;

Powyższe zapytanie możemy zatem poprawić na takie:

SELECT A.ename, A.sal, B.ename, B.sal FROM emp A LEFT JOIN emp B ON A.mgr = B.empno;

Teraz otrzymaliśmy wszystkich pracowników i dla tych którzy nie mają przełożonego trzecia i czwarta kolumna są puste (formalnie jest tam NULL).

Popatrzmy jeszcze na zapytanie wybierające miasto w którym pracuje dany pracownik. Warunek emp.deptno = dept.deptno wydaje się być oczywisty. W takich sytuacjach bardzo wygodnym skrótem notacyjnym jest NATURAL JOIN, który łączy wskazane tabele po kolumnach o takich samych nazwach:

SELECT * FROM emp NATURAL JOIN dept;

Z tą konstrukcją trzeba jednak uważać, zdarza się bowiem, że przy bardziej skomplikowanych złożeniach Oracle zbytnio optymalizując zapytanie czasem nie znajduje kolumn o takich samych nazwach (true story).

Zapytania w zapytaniach

Język SQL dopuszcza używanie podzapytań w zapytaniach. Praktycznie w każdym miejscu w którym wstawiamy stałą możemy wstawić zapytanie, które zwraca tabelę z jedną komórką.

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'TURNER');

W sekcji FROM także możemy podać tabelę zwracaną przez zapytanie. Może być to pomocne przy konstruowaniu bardziej skomplikowanych konstrukcji. Poniżej unikamy słowa AND:

SELECT * FROM (SELECT * FROM emp WHERE job='CLERK') clerks WHERE sal > 2000;

Na tym nie kończą się jednak możliwości zagnieżdżania zapytań. Za pomocą słowa specjalnego IN możemy sprawdzić obecność wartości w zwracanej podzapytaniem tabeli (z jedną kolumną!). Słowa ALL oraz ANY poprzedzone operatorem porównania pozwalają przyrównać wartość także w inne sposoby z każdą komórką podzapytania. W końcu zamiast wartości logicznej możemy wstawić dowolne podzapytanie poprzedzone słowem EXISTS - warunek będzie spełniony jeżeli zapytanie zwróci jakieś wiersze.

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

W podzapytaniach używać możemy także wartości z naszego głównego zapytania. Trzeba jednak pamiętać o tym, że wartości z podzapytania przykrywają wartości z poza niego.

SELECT ename, (SELECT loc FROM dept WHERE deptno = emp.deptno) loc FROM emp;

Widzimy zatem że podzapytania są bardzo potężnym narzędziem i jak z każdym potężnym narzędziem trzeba bardzo uważać, żeby nie uderzyć się nim w palec. Duża ilość podzapytań prowadzi do małej czytelności, ponadto może spowodować że zapytanie będzie się wykonywać bardzo długo.

Operacje na wynikach zapytania

SQL dopuszcza operacje na tabelach będących wynikiem zapytań. Zapytania możemy dodawać (UNION), odejmować (MINUS) oraz brać część wspólną (INTERSECT). Powyższe operacje eliminują powtarzające się wiersze, jeżeli chcemy je zachować musimy dodać słówko ALL po nazwie operacji. Tabele na których wykonujemy operacje muszą mieć oczywiście zgodne typami kolumny.

(SELECT ename, job, deptno FROM emp WHERE job='CLERK') 
INTERSECT 
(SELECT ename, job, emp.deptno FROM emp, dept WHERE emp.deptno = dept.deptno AND loc = 'CHICAGO');