Data: 11.07.2021

MS SQL złączenia tabel

Spis treści

• MS SQL złączenia tabel
  • MS SQL złączenia tabel
  • Rodzaje złączeń w MS SQL Server
    • Aliasy tabel
  • Złączenie wewnętrzne JOIN
  • Złączenie zewnętrzne lewostronne
  • Złączenie zewnętrzne prawostronne
  • Złączenie zewnętrzne obustronne
  • Iloczyn kartezjański
  • Złączenie tabeli z samą sobą
  • Złączenia wielokrotne

W artykule MS SQL złączenia tabel znajdziesz informacje na temat odczytywania danych z różnych tabel w języku MS SQL.

MS SQL złączenia tabel

Złączenia tabel dokonujemy, jeśli chcemy połączyć i odczytać dane z więcej niż jednej tabeli.

Przed utworzeniem zapytania łączącego tabele powinniśmy sprawdzić:

• czy w łączonych tabelach są unikalne rekordy – jeśli w tabelach są duplikaty (ten sam rekord występuje więcej niż raz), złączenie tabel spowoduje duplikację rekordów
• według których kolumn chcemy połączyć tabele
• czy złączenie tabel nie spowoduje problemów z wydajnością bazy – im więcej złączeń tym dłuższy czas realizacji zapytania przez serwer

Rodzaje złączeń w MS SQL Server

• złączenie wewnętrzne – INNER JOIN (JOIN)
• złączenie zewnętrzne lewostronne – LEFT OUTER JOIN (LEFT JOIN)
• złączenie zewnętrzne prawostronne- RIGHT OUTER JOIN (RIGHT JOIN)
• złączenie zewnętrzne obustronne- FULL OUTER JOIN (FULL JOIN)

Aliasy tabel

Aliasy tabel definiujemy w klauzuli FROM. Po zdefiniowaniu aliasu dla tabeli, w pozostałych klauzulach zapytania nie możemy już używać oryginalnej nazwy tabeli. Musimy używać aliasu. Słowo kluczowe AS jest opcjonalne.

Aliasy tabel są szczególnie przydatne w przypadku łączenia tabel. Dzięki aliasom nie musimy podawać w zapytaniu pełnej nazwy tabeli.

SELECT ST.SalesYTD as wyniki
FROM Sales.SalesTerritory as ST;

Złączenie wewnętrzne JOIN

W wyniku złączenia wewnętrznego (INNER JOIN, JOIN) otrzymujemy tylko te rekordy, dla których warunki złączenia będą spełnione (wynik będzie TRUE).

Poszczególne kroki łączenia tabel w JOIN:

1. wykonanie iloczynu kartezjańskiego obu tabel – połączenie każdego wiersza z tabeli A z każdym wierszem z tabeli B
2. sprawdzenie dla każdej pary wierszy warunku określonego w operatorze ON.
3. odrzucenie wszystkich wierszy niespełniających warunków określonych w operatorze ON. W zbiorze wynikowym pozostają tylko te wierze, dla których warunek określony w operatorze ON jest spełniony (przyjmuje wartość TRUE).

JOIN jest złączeniem symetrycznym – nie ma znaczenia w jakiej kolejności łączymy tabele A z B czy odwrotnie. Nie ma też znaczenia kolejność, w jakiej wypiszemy warunki złączenia w słowie kluczowym ON.

W przykładzie mam dwie tabele dbo.Customers i dbo.Address, które połączę ze sobą za pomocą złączenia wewnętrznego JOIN.

SELECT *
FROM Customers a
JOIN Address b
ON a.CustomerID = b.CustomerID;

Tabele łączę według danych znajdujących się w kolumnie CustomerID. W każdej z tabel było 5 rekordów. W wyniku złączenia otrzymuję tylko te trzy rekordy, dla których wartość w kolumnie CustomerID była taka sama.

Jeżeli tabele, które chcemy złączyć nie mają „wspólnej” kolumny (zawierającej takie same dane w obydwu tabelach), musimy utworzyć złączenie na podstawie innej tabeli tzw. tabeli łącznikowej, która posiada „wspólną” kolumnę z jedną z łączonych tabel lub z obydwoma.

W poniższym przykładzie chcę połączyć tabelę a (Person.Person) z tabelą c (Person.Address). Nie ma takiej możliwości, gdyż tabela a i c nie posiadają kolumny, na podstawie której mógłbym dokonać złączenia. Dołączam zatem do tabeli a tabelę b (Person.BusinessEntityAddress) i dopiero do tabeli b mogę dołączyć tabelę c (Person.Address).

SELECT *
FROM Person.Person a
JOIN Person.BusinessEntityAddress b
ON a.BusinessEntityID = b.BusinessEntityID
JOIN Person.Address c
ON b.AddressID = c.AddressID;

Powinniśmy uważnie sprawdzić, czy dokonujemy złączenia według właściwej kolumny.

W poniższym przykładzie połączę tabelę Customers z tabelą Address według kolumny, która zawiera zupełnie inne ID. W kolumnie CustomerID znajduje się ID identyfikujące klienta. Natomiast w kolumnie AddressID znajduje się ID identyfikukące adres. Typ danych w kolumnach, według którch łączę jest zgodny. W związku z tym tabele zostaną złączone mimo tego, że złączenie nie jest realizowane po odpowiadających sobie kolumnach.

Dane wejściowe:

SELECT a.*, b.Street, b.PostalCode, b.City
FROM Customers a
JOIN Address b
ON a.CustomerID = b.AddressID;

W przypadku złączenia JOIN, warunki złączenia możemy wymienić w klauzuli ON, tak jak w poniższym przykładzie.

SELECT *
FROM [Order Details] a
JOIN Products b
ON a.ProductID = b.ProductID
JOIN Orders c
ON a.OrderID = c.OrderID
JOIN Categories d
ON b.CategoryID = d.CategoryID
WHERE YEAR(c.OrderDate) = 1998;

Możemy też wymienić je w klauzuli WHERE, tak jak poniżej:

SELECT *
FROM [Order Details] a,  Products b, Orders c, Categories d
WHERE a.ProductID = b.ProductID
AND a.OrderID = c.OrderID
AND b.CategoryID = d.CategoryID
AND YEAR(c.OrderDate) = 1998;

W tym przypadku nie używamy klauzuli JOIN. Wszystkie tabele wymieniamy w klauzuli FROM, a warunki złączenia w klauzuli WHERE.

Moim zdaniem łatwiejsze jest analizowanie zapytania, jeśli złączenie realizujemy przy wykorzystaniu klauzuli JOIN, gdyż:

• w klauzuli JOIN mamy wymienione wszystkie łączone tabele i warunki ich złączenia
• w klauzuli WHERE mamy jedynie warunki ograniczające liczbę rekordów (filtrujące rekordy) i nie musimy analizować, które warunki dotyczą złączenia, a które filtrowania rekordów.

Przed przystąpieniem do łączenia tabel, warto sprawdzić, czy w łączonych tabelach są duplikaty. Jeśli są, spowoduje to duplikację rekordów po złączeniu.

W danych wejściowych w tabeli dbo.Customers rekord posiadający ID numer 1 jest zduplikowany.

SELECT a.*,  b.Street, b.PostalCode, b.City
FROM Customers a
JOIN Address b
ON a.CustomerID = b.CustomerID;

Po złączeniu tabel rekord z ID numer 1 będzie występował w wyniku zapytania dwukrotnie i dwukrotnie zostanie przyporządkowany do niego rekord z tabeli dbo.Address.

Tabele możemy łączyć po więcej niż jednej kolumnie. Wszystkie warunki złączenia wymieniamy w klauzuli ON. W przykładzie łączę tabele dbo.Customers i dbo.Address używając kolum CustomerID oraz PESEL.

Dane wejściowe:

SELECT a.*, b.Street, b.PostalCode, b.City
FROM Customers a
JOIN Address b
ON a.CustomerID = b.CustomerID
AND a.PESEL = b.PESEL;

Wynik zapytania:

W warunkach używanych do złączenia tabel możemy stosować funkcje. W poniższym przykładzie chcę użyć do złączenia tabel kolumny PESEL. W tabeli dbo.Address, w kolumnie tej są spacje. Użyję funkcji RTRIM(), aby je wyeliminować.

Używanie funkcji w warunkach złączenia nie jest efektywne, gdyż funkcja uruchamia się dla każdego rekordu. Wydłuża to czas działania zapytania. Lepiej jest wyczyścić dane w tabeli i np. usunąć spacje, jeśli jest taka możliwość.

Dane wejściowe:

SELECT a.*, b.Street, b.PostalCode, b.City
FROM Customers a
JOIN Address b
ON a.CustomerID = b.CustomerID
AND a.PESEL = LTRIM(b.PESEL);

Wynik zapytania:

Możemy łączyć tabele, które znajdują się w różnych bazach danych. Aby to zrobić, w zapytaniu należy podać pełną ścieżkę tj. nazwa bazy danych, nazwa schematu, nazwa tabeli. W poniższym przykładzie:

• bazy danych: AdventureWorks, northwind
• schematy: Production, dbo
• tabele: Product, Products

Schemat dbo jest domyślnym schematem w MS SQL Server.

SELECT *
FROM AdventureWorks.Production.Product a
JOIN northwind.dbo.Products b
ON a.ProductID = b.ProductID;

Złączenie zewnętrzne lewostronne

W złączeniu typu LEFT JOIN łączone są najpierw wewnętrznie dwa zbiory (realizowany jest INNER JOIN). Następnie do wyniku zapytania dodawane są wszystkie niedopasowane elementy tabeli po PRAWEJ stronie operatora JOIN.

W wyniku złączenia LEFT JOIN otrzymamy:

• wszystkie rekordy z tabeli po LEWEJ stronie operatora JOIN
• dopasowane rekordy z tabeli po PRAWEJ stronie operatora JOIN
• niedopasowane rekordy z tabeli po PRAWEJ stronie operatora JOIN

W danych wejściowych mamy dwie tabele dbo.Customers i dbo.Address

Chcemy napisać zapytanie, za pomocą którego wybierzemy wszystkich klientów z tabeli dbo.Customers oraz te, adresy które uda się znaleźć z tabeli dbo.Address

SELECT *
FROM Customers a
LEFT JOIN [Address] b
ON a.CustomerID = b.CustomerID;

Złączenie zewnętrzne prawostronne

Tak samo, jak w przypadku złączenia LEFT JOIN, w złączeniu RIGHT JOIN łączone są najpierw wewnętrznie dwa zbiory (INNER JOIN). Następnie do wyniku zapytania dodawane są wszystkie niedopasowane elementy tabeli po LEWEJ stronie operatora JOIN.

W wyniku złączenia RIGHT JOIN otrzymamy:

• wszystkie rekordy z tabeli po PRAWEJ stronie operatora JOIN
• dopasowane rekordy z tabeli po LEWEJ stronie operatora JOIN
• niedopasowane rekordy z tabeli po LEWEJ stronie operatora JOIN

W danych wejściowych mamy dwie tabele dbo.Customers i dbo.Address

Chcemy napisać zapytanie, za pomocą którego wybierzemy wszystkie adresy z tabeli dbo.Address oraz tych klientów, których uda się znaleźć w tabeli dbo.Customers.

SELECT *
FROM Customers a
RIGHT JOIN [Address] b
ON a.CustomerID = b.CustomerID;

Złączenie zewnętrzne obustronne

Złączenie zewnętrzne obustronne (FULL JOIN) zwraca wszystkie rekordy z obydwu złączonych tabel, w tym te, które nie spełniają warunku złączenia.

W danych wejściowych mamy dwie tabele dbo.Customers i dbo.Address

Chcemy napisać zapytanie, za pomocą którego wybierzemy wszystkie rekordy z tabeli dbo.Customers i wszystkie rekordy z tabeli dbo.Address.

SELECT *
FROM Customers a
FULL JOIN [Address] b
ON a.CustomerID = b.CustomerID;

Iloczyn kartezjański

Iloczyn kartezjański (CROSS JOIN) – zapytanie wykonuje iloczyn kartezjański na podstawie łączonych tabel – łączy każdy wiersz tabeli a z każdym wierszem tabeli b.

SELECT *
FROM Customers a
CROSS JOIN Employees b;

W wyniku otrzymamy:

Złączenie tabeli z samą sobą

Złączenie tabeli z samą sobą występuje, gdy po lewej i prawej stronie złączenia występuje ta sama tabela. Konieczne jest w nim użycie aliasów.

Ma zastosowanie, jeżeli chcemy porównać wybrane rekordy tabeli z innymi rekordami tej samej tabeli.

Dane wejściowe

Chcemy napisać zapytanie, za pomocą którego znajdziemy pary klientów posiadających takie samo imię. W klauzuli WHERE podajemy dwa warunki:

• imię z tabeli a ma równać się imieniu z tabeli b
• ID klienta (Customer_ID) w tabeli a ma być większe lub mniejsze niż ID klienta w tabeli b. Jeśli nie podamy tego warunku, w wyniku zapytania pojawią się pary klientów, w których klient połączony jest sam ze sobą. Jeśli z kolei podamy warunek, w którym ID jest różne, każda para klientów pojawi się dwa razy – raz w kolejności klient 1 – klient 2, a drugi raz w kolejności klient 2 – klient 1. Jedynie warunek, w którym użyjemy znaku więszkości lub mniejszości umożliwia odczytanie każdej pary klientów tylko raz. Pokazani zostaną tylko ci klienci, w przypadku których ID w tabeli a jest większe lub mniejsze niż ID w tabeli a.

SELECT a.Customer_ID, a.FirstName, a.LastName,
b.Customer_ID, b.FirstName, b.LastName
FROM Customers a, Customers b
WHERE a.Customer_ID > b.Customer_ID
AND a.FirstName = b.FirstName
ORDER BY a.FirstName ;

W wyniku otrzymamy:

Złączenia wielokrotne

W jednym zapytaniu możemy odwołać się do wielu tabel, jednak z każdą następną dołączoną tabelą spada wydajność zapytania.

Dane wejściowe:

Chcemy napisać zapytanie, za pomocą którego odczytamy dane wszystkich klientów oraz ich adresy i składane przez nich zamówienia.

SELECT *
FROM Customers a
LEFT JOIN Address b
ON a.CustomerID = b.CustomerID
LEFT JOIN Orders c
ON a.CustomerID = c.CustomerID;

Przy złączeniach wielokrotnych trzeba zwrócić uwagę, do której tabeli dołączamy kolejne tabele. W poniższym przykładzie:

• tabelą bazową jest tabela dbo.Customers
• do tabeli dbo.Customers dołączyłem tabelę dbo.Address. Dzięki temu mam dopisane wszystkie adresy klientów, które udało się znaleźć
• do tabeli dbo.Customers dołączyłem kolejną tabelę tj. dbo.Orders. Dzięki temu mam dopisane wszystkie zamówienia. Jest również zamówienie, które zrealizował klient o id numer 7, mimo że nie mam adresu tego klienta (Klienta nie było w tabeli dbo.Address).

W poniższym przykładzie tabelę dbo.Orders dołączę do tabeli dbo.Address

• wybieram wszystkie rekordy z tabeli dbo.Customers
• do tabeli dbo.Customers dołączyłem tabelę dbo.Address. Dzięki temu mam dopisane wszystkie adresy klientów, które udało się znaleźć
• do tabeli dbo.Address dołączyłem kolejną tabelę tj. dbo.Orders. Mam teraz dopisane zamówienie, jedynie dla tych klientów, którzy byli w tabeli dbo.Address. Nie mam danych dla klienta o id numer 7 mimo, że składał zamówienie, gdyż adresu tego klienta nie było w tabeli dbo.Address, do której dołączam tabelę dbo.Orders.

Jeśli podejmę niewłaściwą decyzję odnośnie sposobu złączenia tabel, mogę „zgubić” informacje. Dlatego warto sprawdzać, czy zapytanie zwróciło dokładnie te dane, o które nam chodziło.

Aby zoptymalizować zapytanie, zamiast złączeń wielokrotnych, można np. stworzyć tabele tymczasowe. Informacje na temat tabel tymczasowych znajdziesz w artykule Tabele tymczasowe w MS SQL Server.

Zapraszamy na kursy SQL, na których przećwiczysz złączenia tabel oraz inne elementy składni języka MS SQL. Na każdy kurs przygotowaliśmy ponad 100 ciekawych ćwiczeń praktycznych.

Ewa Barbara Lelusz
Analityk biznesowy

Udostępnij wpis: