JOIN INNER OUTER LEFT RIGHT: Wszystko co chcesz wiedzieć

JOIN Operacja JOIN jest najprawdopodobniej najczęściej używaną operacją w jakiejkolwiek bazie danych. Nie jest to niczym zaskakującym, ponieważ system został  stworzony w taki sposób ,aby wyrażać związki pomiędzy rekordami bez  dublowania informacji.

Nikt nie trzyma wszystkiego w jednej tabelce, gdzie pewne kolumny  przechowywałyby powtarzające się wartości.

Dosyć jednak tego wstępu . W tym wpisie opiszę wszystkie operację JOIN, jakie oferuje nam system bazo danowych. Wpis tyczy SQL Server-a ,ale tę wiedzę łatwo przelać do każdego innego systemu.

Standardowo utworzymy przykładowe tabelki i uzupełnimy je danymi.

CREATE TABLE [dbo].[Region](
	[RegionId] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED 
(
	[RegionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[Country](
	[CountryId] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[RegionId] [int] NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
(
	[CountryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[Country]  WITH CHECK ADD  CONSTRAINT [FK_Country_Region] FOREIGN KEY([RegionId])
REFERENCES [dbo].[Region] ([RegionId])
GO

ALTER TABLE [dbo].[Country] CHECK CONSTRAINT [FK_Country_Region]
GO

Tabelka Region zawiera tylko ID i nazwę regionu. W tej tabelce będą znajdować się kontynenty naszego świata.

Tabelka Country zawiera ID , nazwę kraju oraz id regionu, które jest kluczem obcym. Warto zaznaczyć ,że id regionu może być puste. Dany kraj może nie posiadać regionu.

Uzupełnimy obie tabelki danymi.

INSERT INTO [dbo].[Region]
           ([RegionId]
           ,[Name])
     VALUES
           (1,'Europe'),(2,'Asia'),(3,'Africa'),
           (4,'South America'),(5,'North America'),(6,'Australia')


INSERT INTO [dbo].[Country]
           ([CountryId]
           ,[Name]
           ,[RegionId])
     VALUES
           (1,'Poland',1),(2,'Japan',2),
           (3,'Algeria',3),(4,'Argentina',4),
           (5,'New Zealand',NULL),(6,'Federated States of Micronesia',NULL)

Na co warto zwrócić uwagę:

• Regiony "5,North America" oraz "6,Australia" nie są powiązane z żadnym krajem.
• Kraje “5,'New Zealand" i "6,'Federated States of Micronesia'" nie mają swojego regionu.

Ten Join zwraca  wiersze, w których  obie wartość  w obydwu tabelkach się zgodziły.

Mówiąc łatwiej ten JOIN, zwraca część wspólną zbiorów danych, z którymi łączymy obie tabele.

SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
INNER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId

Wyniki tego zapytania powinien zwrócić tylko kraje  powiązane z regionami, jak i  regiony, które mają przypisane  kraje. Rekordy zostały powiązane po RegionId.

Kraje niemające RegionID zostały zignorowane. Podobny los trafił na regiony, które nie zostały przypisane do żadnego kraju.

Pisząc tylko JOIN wykonujemy operację “INNER JOIN”?

LEFT OUTER JOIN

OUTER JOIN ma ,aż trzy różne metody .Oto pierwsza z nich.

Ten JOIN zwraca wszystkie wiersze tabelki po lewej plus wiersze, które mają uzupełnienie z prawej tabelki.

Jeśli nie ma żadnych wartości odpowiadającej prawej tabelce, to jest zwracana wartość NULL.

SELECT t1.Name,t2.Name FROM dbo.Country AS t1
LEFT OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId

Jak widać  zostały zwrócone wszystkie kraje ,a w przypadku braku powiązania występuje wartość NULL.

Lewa tabelka to ta, do której odwołujemy się w klauzurze FROM przy SELEC-ie.

RIGHT OUTER JOIN

Ten JOIN zwraca wszystkie wiersze z prawej tabelki plus wiersze powiązane z tabelką po lewej.

Analogicznie do poprzedniego przykładu, gdy nie ma powiązania wartości danej kolumny w jej miejscu, to wstępuje wartość NULL.

SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
RIGHT OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId

W tym przypadku zostały zwrócone wszystkie regiony.

Prawa tabelka to ta, do której się odwołujemy w klauzurze JOIN.

FULL OUTER JOIN

Ten JOIN jest kombinacją lewego i prawego OUTER JOIN-a.

Zwraca ona wszystkie wiersze z obu tabel bez względu na to czy powiązanie wystąpiło, czy nie.

Gdy nie ma powiązania po któreś stronie w jego miejsce, to jest wstawiona wartość NULL.

SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
FULL OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId

Jak widać, zostały zwrócone wszystkie kraje i wszystkie regiony.

CROSS JOIN

JOIN kartezjański nie potrzebuje żadnego warunku do powiązania. Rezultatem tego JOIN-a są wiersze pomnożone przez liczbę rekordów z obu tabel.

SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
CROSS JOIN dbo.Region AS t2

--Alternatywnie
SELECT t1.Name AS Country,t2.Name AS Region 
FROM dbo.Country AS t1, dbo.Region AS t2

Nie widzę praktycznego zastosowania tego JOIN-a.

Z NULL-em

To były wszystkie operację JOIN, jakie oferuję SQL.

Jedno nie są to  wszystkie kombinację zbiorów, jakie można uzyskać wyniku operacji JOIN.

Czasami programiści potrzebuje  wierszy niepowiązanych wcale.

Przykładowo, jeśli chce pobrać wszystkie kraje, które nie mają przypisanego regionu to robię to w ten sposób.

SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
LEFT OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId
WHERE t2.RegionId IS NULL

Można zadać sobie pytanie, czy w takim wypadku JOIN w ogóle nam jest potrzebny. Zapytanie poniżej zwróci na te same rekordy.

SELECT [CountryId]
      ,[Name]
      ,[RegionId]
  FROM [dbo].[Country] WHERE RegionID IS NULL

Skoro i tak nas nie interesują powiązane wiersze to po co w ogóle JOIN-ujemy w takim wypadku. Po samym pustym ID regionu jesteśmy w stanie to ustalić.

Sprawa wygląda inaczej w RIGHT OUTER JOIN.

Teraz nie mam żadnej informacji o uzupełnieniu w samej tabelce Regiony . Muszę wykonać operację JOIN z tabelką Country, by to sprawdzić.

SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
RIGHT OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId
WHERE t1.RegionId IS NULL

OPPOSITE INNER JOIN

W terminologii SQL nie ma definicji odwrotności INNER JOIN-a ,ale używając klauzury WHERE oraz FULL OUTER JOIN można uzyskać taki efekt.

To zapytanie zwróci wyniki z obu tabel, gdzie kolumny nie zostały ze sobą powiązane.

SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
FULL OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId
WHERE t1.RegionId IS NULL OR t2.RegionId IS NULL