Połącz lewy SQL vs. wiele tabel na linii FROM?

Większość dialektów SQL akceptuje oba następujące zapytania:

SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x

SELECT a.foo, b.foo
FROM a
LEFT JOIN b ON a.x = b.x

Oczywiście, kiedy potrzebujesz zewnętrznego sprzężenia, wymagana jest druga składnia. Ale kiedy robię wewnętrzne sprzężenie, dlaczego miałbym preferować drugą składnię niż pierwszą (lub odwrotnie)?

Stara składnia, zawierająca tylko listę tabel i wykorzystująca WHEREklauzulę do określenia kryteriów łączenia, jest przestarzała w większości nowoczesnych baz danych.

Nie chodzi tylko o pokaz, stara składnia może być dwuznaczna, gdy użyjesz złączeń INNER i OUTER w tym samym zapytaniu.

Dam ci przykład.

Załóżmy, że masz w systemie 3 tabele:

Company
Department
Employee

Każda tabela zawiera wiele wierszy połączonych ze sobą. Masz wiele firm i każda firma może mieć wiele działów, a każdy dział może mieć wielu pracowników.

Ok, teraz chcesz wykonać następujące czynności:

Wymień wszystkie firmy i wszystkie ich działy oraz wszystkich ich pracowników. Pamiętaj, że niektóre firmy nie mają jeszcze żadnych działów, ale pamiętaj o ich uwzględnieniu. Upewnij się, że pobierasz tylko te działy, które mają pracowników, ale zawsze wymieniasz wszystkie firmy.

Więc robisz to:

SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
  AND Department.ID = Employee.DepartmentID

Zwróć uwagę, że ostatnia jest łączenie wewnętrzne, aby spełnić kryteria, że ​​chcesz tylko działy z ludźmi.

Ok, więc co się teraz stanie. Problem polega na tym, że zależy to od silnika bazy danych, optymalizatora zapytań, indeksów i statystyk tabeli. Pozwól mi wyjaśnić.

Jeśli optymalizator zapytań ustali, że sposobem na to jest najpierw zabranie firmy, a następnie znalezienie działów, a następnie połączenie wewnętrzne z pracownikami, nie uzyskasz żadnych firm, które nie mają działów.

Powodem tego jest to, że WHEREklauzula określa, które wiersze kończą w wyniku końcowym, a nie poszczególne części wierszy.

W tym przypadku, ze względu na lewe łączenie, kolumna Department.ID będzie miała wartość NULL, a zatem jeśli chodzi o INNER JOIN to Employee, nie ma sposobu, aby spełnić to ograniczenie dla wiersza Employee, a więc nie będzie zjawić się.

Z drugiej strony, jeśli optymalizator zapytań zdecyduje się najpierw rozwiązać dołączenie do pracownika działu, a następnie wykonać lewe połączenie z firmami, zobaczysz je.

Tak więc stara składnia jest niejednoznaczna. Nie ma sposobu, aby określić, co chcesz, bez radzenia sobie ze wskazówkami dotyczącymi zapytań, a niektóre bazy danych nie mają żadnej możliwości.

Wprowadź nową składnię, z tym możesz wybrać.

Na przykład, jeśli chcesz, aby wszystkie firmy, jak podano w opisie problemu, napisałbyś to:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID

W tym miejscu określasz, że chcesz, aby łączenie pracownika działu było wykonywane jako jedno połączenie, a następnie pozostawienie dołączenia wyników tego połączenia do firm.

Dodatkowo, powiedzmy, że chcesz tylko działów, które zawierają literę X w ich nazwie. Ponownie, przy sprzężeniach w starym stylu, ryzykujesz również utratę firmy, jeśli nie ma żadnych działów z X w nazwie, ale dzięki nowej składni możesz to zrobić:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID AND Department.Name LIKE '%X%'

Ta dodatkowa klauzula służy do łączenia, ale nie jest filtrem dla całego wiersza. Tak więc wiersz może pojawiać się z informacjami o firmie, ale może mieć wartości NULL we wszystkich kolumnach działu i pracownika dla tego wiersza, ponieważ nie ma działu o nazwie X dla tej firmy. Jest to trudne przy starej składni.

To dlatego, między innymi dostawcami, Microsoft wycofał starą zewnętrzną składnię złączeń, ale nie starą wewnętrzną składnię złączeń, od SQL Server 2005 i wyższych. Jedynym sposobem na rozmowę z bazą danych działającą na Microsoft SQL Server 2005 lub 2008, przy użyciu składni sprzężenia zewnętrznego w starym stylu, jest ustawienie tej bazy danych w trybie zgodności 8.0 (inaczej SQL Server 2000).

Dodatkowo, stary sposób, rzucając kilka tabel w optymalizator zapytań, z kilkoma klauzulami WHERE, był podobny do powiedzenia „proszę bardzo, zrób co możesz”. Dzięki nowej składni optymalizator zapytań ma mniej pracy, aby dowiedzieć się, które części idą w parze.

Więc masz to.

POŁĄCZENIE W LEWO i WEWNĘTRZNE to fala przyszłości.

Składnia JOIN utrzymuje warunki w pobliżu tabeli, której dotyczą. Jest to szczególnie przydatne, gdy dołączasz do dużej liczby tabel.

Nawiasem mówiąc, możesz również wykonać łączenie zewnętrzne z pierwszą składnią:

WHERE a.x = b.x(+)

Lub

WHERE a.x *= b.x

Lub

WHERE a.x = b.x or a.x not in (select x from b)

Pierwszy sposób to starszy standard. Druga metoda została wprowadzona w SQL-92, http://en.wikipedia.org/wiki/SQL . Kompletny standard można obejrzeć na stronie http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt .

Wiele lat zajęło firmom baz danych przyjęcie standardu SQL-92.

Tak więc powodem, dla którego preferowana jest druga metoda, jest standard SQL zgodnie z komitetem norm ANSI i ISO.

Zasadniczo, gdy twoja klauzula FROM wyświetla takie tabele:

SELECT * FROM
  tableA, tableB, tableC

wynik jest iloczynem wszystkich wierszy w tabelach A, B, C. Następnie zastosujesz ograniczenie, WHERE tableA.id = tableB.a_idktóre wyrzuci ogromną liczbę rzędów, a następnie dalej ... AND tableB.id = tableC.b_idi powinieneś uzyskać tylko te wiersze, które naprawdę Cię interesują w.

DBMS wiedzą, jak zoptymalizować ten SQL, aby różnica w wydajności pisania tego przy użyciu JOIN była znikoma (jeśli w ogóle). Użycie notacji JOIN sprawia, że ​​instrukcja SQL jest bardziej czytelna (IMHO, nieużywanie złączeń zamienia instrukcję w bałagan). Używając produktu krzyżowego, musisz podać kryteria łączenia w klauzuli WHERE, a to jest problem z notacją. Tłoczysz swoją klauzulę WHERE takimi rzeczami

    tableA.id = tableB.a_id 
AND tableB.id = tableC.b_id 

który służy wyłącznie do ograniczenia produktu krzyżowego. GDZIE klauzula powinna zawierać OGRANICZENIA w zestawie wyników. Jeśli połączysz kryteria łączenia tabeli z ograniczeniami zestawu wyników, (i innym) Twoje zapytanie będzie trudniejsze do odczytania. Zdecydowanie powinieneś użyć JOIN i zachować klauzulę FROM klauzulę FROM, a klauzulę WHERE klauzulę WHERE.

Drugi jest preferowany, ponieważ jest znacznie mniej prawdopodobne, że spowoduje przypadkowe połączenie krzyżowe, zapominając o umieszczeniu w klauzuli where. Łączenie bez klauzuli „on” zakończy się niepowodzeniem sprawdzania składni, łączenie w starym stylu bez klauzuli „where” nie zawiedzie, spowoduje to łączenie krzyżowe.

Dodatkowo, gdy później trzeba wykonać lewe połączenie, pomocne jest utrzymanie, aby wszystkie były w tej samej strukturze. Stara składnia jest nieaktualna od 1992 roku, więc jest już czas, aby przestać jej używać.

Ponadto odkryłem, że wiele osób, które używają wyłącznie pierwszej składni, tak naprawdę nie rozumie złączeń, a zrozumienie złączeń ma kluczowe znaczenie dla uzyskania poprawnych wyników podczas zapytania.

Myślę, że istnieje kilka dobrych powodów na tej stronie, aby przyjąć drugą metodę - używając jawnych JOIN. Clincher jest jednak taki, że po usunięciu kryteriów JOIN z klauzuli WHERE znacznie łatwiej jest zobaczyć pozostałe kryteria selekcji w klauzuli WHERE.

W naprawdę złożonych instrukcjach SELECT czytelnikowi łatwiej jest zrozumieć, co się dzieje.

SELECT * FROM table1, table2, ...Składnia jest ok na kilka stolików, ale staje się wykładniczo ( niekoniecznie matematycznie dokładne zestawienie ) trudniej odczytać jako liczbę stołów wzrasta.

Składnia JOIN jest trudniejsza do napisania (na początku), ale wyraźnie określa, jakie kryteria wpływają na które tabele. To znacznie utrudnia popełnienie błędu.

Ponadto, jeśli wszystkie sprzężenia są INNER, wówczas obie wersje są równoważne. Jednak w momencie, gdy dołączasz do ZEWNĘTRZNIE w dowolnym miejscu w oświadczeniu, sprawy stają się znacznie bardziej skomplikowane i jest to praktycznie gwarancja, że ​​to, co piszesz, nie będzie kwestionować tego, co myślisz, że napisałeś.

Gdy potrzebujesz zewnętrznego sprzężenia, druga składnia nie zawsze jest wymagana:

Wyrocznia:

SELECT a.foo, b.foo
  FROM a, b
 WHERE a.x = b.x(+)

MSSQLServer (chociaż jest przestarzały w wersji 2000) / Sybase:

SELECT a.foo, b.foo
  FROM a, b
 WHERE a.x *= b.x

Ale wracając do twojego pytania. Nie znam odpowiedzi, ale prawdopodobnie jest to związane z faktem, że złączenie jest bardziej naturalne (przynajmniej składniowo) niż dodawanie wyrażenia do klauzuli where , gdy robisz dokładnie to: łączenie .

Słyszę, że wiele osób narzeka, że ​​pierwsza jest zbyt trudna do zrozumienia i niejasna. Nie widzę z tym problemu, ale po tej dyskusji używam drugiej nawet dla INNER JOINS dla przejrzystości.

W bazie danych są one takie same. Dla ciebie jednak będziesz musiał użyć tej drugiej składni w niektórych sytuacjach. Ze względu na edycję zapytań, które w końcu muszą z niego skorzystać (odkrycie, że potrzebujesz złączenia z lewej strony w miejscu, w którym miałeś złączenie proste), a dla zachowania spójności wzorowałbym się tylko na drugiej metodzie. Ułatwi to czytanie zapytań.

Cóż, pierwsze i drugie zapytanie może dać różne wyniki, ponieważ LEFT JOIN zawiera wszystkie rekordy z pierwszej tabeli, nawet jeśli w odpowiedniej tabeli nie ma odpowiednich rekordów.