[SQL] 2-2. JOIN - 여러 테이블 조립하기

chxxrin·2024년 11월 21일

목록 보기

4/5

1. JOIN(INNER JOIN) - 내부 조인

→ 나눈 테이블을 다시 합침!

양쪽 모두에 값이 있는 행(NOT NULL) 반환
'INNER '는 선택사항

SELECT * 
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID;

CategoryID	CategoryName	Description	ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Beverages	Soft drinks, coffees, teas, beers, and ales	1	Chais	1	1	10 boxes x 20 bags	18.00
1	Beverages	Soft drinks, coffees, teas, beers, and ales	2	Chang	1	1	24 - 12 oz bottles	19.00
2	Condiments	Sweet and savory sauces, relishes, spreads, and seasonings	3	Aniseed Syrup	1	2	12 - 550 ml bottles	10.00

SELECT C.CategoryID, C.CategoryName, P.ProductName
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID; 

-- ambiguous 주의!

CategoryID	CategoryName	ProductName
1	Beverages	Chais
1	Beverages	Chang
2	Condiments	Aniseed Syrup

SELECT
  CONCAT(
    P.ProductName, ' by ', S.SupplierName
  ) AS Product,
  S.Phone, P.Price
FROM Products P
JOIN Suppliers S
  ON P.SupplierID = S.SupplierID
WHERE Price > 50
ORDER BY ProductName;

Product	Phone	Price
Carnarvon Tigers by Pavlova, Ltd.	(03) 444-2343	62.50
Côte de Blaye by Aux joyeux ecclésiastiques	(1) 03.83.00.68	263.50

💡 여러 테이블을 JOIN할 수 있습니다

SELECT 
  C.CategoryID, C.CategoryName, 
  P.ProductName, 
  O.OrderDate,
  D.Quantity
FROM Categories C

JOIN Products P 
  ON C.CategoryID = P.CategoryID
  
JOIN OrderDetails D
  ON P.ProductID = D.ProductID
  
JOIN Orders O
  ON O.OrderID = D.OrderID;

CategoryID	CategoryName	ProductName	OrderDate	Quantity
4	Dairy Products	Queso Cabrales	1996-07-04	12
5	Grains/Cereals	Singaporean Hokkien Fried Mee	1996-07-04	10

💡 JOIN한 테이블 GROUP하기

SELECT 
  C.CategoryName,
  MIN(O.OrderDate) AS FirstOrder,
  MAX(O.OrderDate) AS LastOrder,
  SUM(D.Quantity) AS TotalQuantity
FROM Categories C

JOIN Products P 
  ON C.CategoryID = P.CategoryID
  
JOIN OrderDetails D
  ON P.ProductID = D.ProductID
  
JOIN Orders O
  ON O.OrderID = D.OrderID
  
GROUP BY C.CategoryID;

CategoryName	FirstOrder	LastOrder	TotalQuantity
Beverages	1996-07-10	1998-05-06	9532
Condiments	1996-07-08	1998-05-06	5298
Confections	1996-07-09	1998-05-06	7906

SELECT 
  C.CategoryName, P.ProductName,
  MIN(O.OrderDate) AS FirstOrder,
  MAX(O.OrderDate) AS LastOrder,
  SUM(D.Quantity) AS TotalQuantity
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID
JOIN OrderDetails D
  ON P.ProductID = D.ProductID
JOIN Orders O
  ON O.OrderID = D.OrderID
GROUP BY C.CategoryID, P.ProductID;

CategoryName	ProductName	FirstOrder	LastOrder	TotalQuantity
Beverages	Chais	1996-08-20	1998-05-05	828
Beverages	Chang	1996-07-12	1998-05-06	1057
Beverages	Guaraná Fantástica	1996-07-11	1998-05-05	1125

💡 SELF JOIN - 같은 테이블끼리

SELECT
  E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee,
  E2.EmployeeID, CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee
FROM Employees E1 
JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID;

-- 1번의 전, 마지막 번호의 다음은? 알수없다. 왜냐하면 INNER JOIN은 NULL값은 가져오지 않으므로

EmployeeID	Employee	EmployeeID	NextEmployee
1	Nancy Davolio	2	Andrew Fuller
2	Andrew Fuller	3	Janet Leverling
3	Janet Leverling	4	Margaret Peacock

2. LEFT/RIGHT OUTER JOIN - 외부 조인

반대쪽에 데이터가 있든 없든(NULL), 선택된 방향에 있으면 출력 - 행 수 결정
'OUTER '는 선택사항

SELECT
  E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee,
  E2.EmployeeID, CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee
FROM Employees E1
LEFT JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID
ORDER BY E1.EmployeeID;

-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것

7	Robert King	8	Laura Callahan
8	Laura Callahan	9	Anne Dodsworth
9	Anne Dodsworth

→ LEFT JOIN 을 하면 조인하는 곳이 NULL 이더라도 데이터를 가져옴

SELECT
  E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee,
  E2.EmployeeID, CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee
FROM Employees E1
RIGHT JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID
ORDER BY E1.EmployeeID;

-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것

EmployeeID	Employee	EmployeeID	NextEmployee
		1	Nancy Davolio
1	Nancy Davolio	2	Andrew Fuller
2	Andrew Fuller	3	Janet Leverling

→ RIGHT JOIN 을 하면 조인하는 곳이 NULL 이더라도 데이터를 가져옴

LEFT JOIN은 E1을 기준으로,
RIGHT JOIN은 E2을 기준으로 조인을 수행하여, 조인 기준에 맞지 않는 경우 NULL 값을 반환하게 됩니다.

SELECT
  C.CustomerName, S.SupplierName,
  C.City, C.Country
FROM Customers C
LEFT JOIN Suppliers S
ON C.City = S.City AND C.Country = S.Country;

-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것

Mère Paillarde	Ma Maison	Montréal	Canada
Ana Trujillo Emparedados y helados		México D.F.	Mexico
Antonio Moreno Taquería		México D.F.	Mexico

SELECT
  C.CustomerName, S.SupplierName,
  C.City, C.Country
FROM Customers C
RIGHT JOIN Suppliers S
ON C.City = S.City AND C.Country = S.Country;

-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것

Tradição Hipermercados	Refrescos Americanas LTDA	São Paulo	Brazil
	Exotic Liquid
	New Orleans Cajun Delights

SELECT
  IFNULL(C.CustomerName, '-- NO CUSTOMER --'),
  IFNULL(S.SupplierName, '-- NO SUPPLIER --'),
  IFNULL(C.City, S.City),
  IFNULL(C.Country, S.Country)
FROM Customers C
LEFT JOIN Suppliers S
ON C.City = S.City AND C.Country = S.Country;

-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것

Mère Paillarde	Ma Maison	Montréal	Canada
Ana Trujillo Emparedados y helados	-- NO SUPPLIER --	México D.F.	Mexico
Antonio Moreno Taquería	-- NO SUPPLIER --	México D.F.

SELECT
  IFNULL(C.CustomerName, '-- NO CUSTOMER --'),
  IFNULL(S.SupplierName, '-- NO SUPPLIER --'),
  IFNULL(C.City, S.City),
  IFNULL(C.Country, S.Country)
FROM Customers C
RIGHT JOIN Suppliers S
ON C.City = S.City AND C.Country = S.Country;

-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것

Tradição Hipermercados	Refrescos Americanas LTDA	São Paulo	Brazil
-- NO CUSTOMER --	Exotic Liquid	Londona	UK
-- NO CUSTOMER --	New Orleans Cajun Delights	New Orleans	US

왼쪽, 오른쪽 JOIN해서 NULL값 포함 모두 출력하려면

LEFT JOIN
RIGHT JOIN
UNION

SELECT
  IFNULL(C.CustomerName, '-- NO CUSTOMER --') AS CustomerName,
  IFNULL(S.SupplierName, '-- NO SUPPLIER --') AS SupplierName,
  IFNULL(C.City, S.City) AS City,
  IFNULL(C.Country, S.Country) AS Country
FROM Customers C
LEFT JOIN Suppliers S
ON C.City = S.City AND C.Country = S.Country

UNION

SELECT
  IFNULL(C.CustomerName, '-- NO CUSTOMER --') AS CustomerName,
  IFNULL(S.SupplierName, '-- NO SUPPLIER --') AS SupplierName,
  IFNULL(C.City, S.City) AS City,
  IFNULL(C.Country, S.Country) AS Country
FROM Customers C
RIGHT JOIN Suppliers S
ON C.City = S.City AND C.Country = S.Country;

UNION은 중복을 제거하여 결과 집합의 고유한 행만 반환합니다.
UNION ALL은 모든 행을 반환하여 중복을 허용합니다.

3. CROSS JOIN - 교차 조인

조건 없이 모든 조합 반환(A * B)
이것저것 모든 조합을 확인해보고 싶을 때 사용 !!

SELECT
  E1.LastName, E2.FirstName
FROM Employees E1
CROSS JOIN Employees E2
ORDER BY E1.EmployeeID;

LastName	FirstName
Davolio	Michael
Davolio	Janet
Davolio	Robert

chxxrin

이전 포스트

[SQL] 2-1. 쿼리 안에 서브쿼리

다음 포스트

[SQL] 2-2. JOIN - 여러 테이블 조립하기

sql

1. JOIN(INNER JOIN) - 내부 조인

💡 SELF JOIN - 같은 테이블끼리

2. LEFT/RIGHT OUTER JOIN - 외부 조인

왼쪽, 오른쪽 JOIN해서 NULL값 포함 모두 출력하려면

3. CROSS JOIN - 교차 조인

[SQL] 2-1. 쿼리 안에 서브쿼리

[SQL] 2-3. UNION - 집합으로 다루기

0개의 댓글

[SQL] 2-2. JOIN - 여러 테이블 조립하기

sql

1. JOIN(INNER JOIN) - 내부 조인

💡 SELF JOIN - 같은 테이블끼리

2. LEFT/RIGHT OUTER JOIN - 외부 조인

왼쪽, 오른쪽 JOIN해서 NULL값 포함 모두 출력하려면

3. CROSS JOIN - 교차 조인

[SQL] 2-1. 쿼리 안에 서브쿼리

[SQL] 2-3. UNION - 집합으로 다루기

0개의 댓글

[SQL] 2-1. 쿼리 안에 서브쿼리