JOIN문 정리

최대환·2022년 8월 6일

서브쿼리는 가독성이 좋지만 조인에 비해 성능이 좋은편은 아니다.
그래서 꼭 필요한 경우가 아니면 조인문을 쓰는게 좋다.

기본적인 조인문

SELECT * FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID; 
Categories테이블과 Products테이블을 CategoryID로 조인했다.
그리고 모든 칼럼을 출력하면 Categories테이블과 Products테이블에 있는 모든 칼럼들이 조회된다.
categoryID도 2번이나 조회된다.

ambiguous

SELECT C.CategoryID, C.CategoryName, P.ProductName
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID; 
이렇게하면 모든테이블에서 원하는 칼럼을 뽑을 수 있는데 여기서 ambiguous를 주의해야한다.
SELECT CategoryID, C.CategoryName, P.ProductName
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID; 
이런식으로 앞에 C.를 빼면 어떤 테이블의 칼럼인지 모호하기때문에 ambiguous에러가 난다.
하지만 CategoryName이나 ProductName 같은 경우 하나의 테이블에만 들어있는 값이기 때문에 앞에 별칭을 빼도 된다.

조인문에서 WHERE 과 ORDER BY 사용하기

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;

이런식으로 ORDER BY나 WHERE 문과 함께 사용하는 모습이다.

여러테이블 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;

이와같이 여러 테이블을 JOIN해서 사용할 수 있다.

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
ORDER BY E1.EmployeeID;
이런식으로 셀프조인도 할 수 있다.
하지만 E2의 첫번째 EmployeeID나 E1의 9번째 EmployeeID가 안나타는걸 볼수 있다.
이처럼 기준 테이블과 조인 테이블 모두 데이터가 존재해야 조회되는걸 INNER JOIN이라고 한다. 우리가 지금까지 써왔단 JOIN문은 모두 INNER JOIN이다.
반대로 기준 테이블에만 데이터가 존재하면 조회가 되는걸 OUTER JOIN이라고 한다.

LEFT/RIGHT OUTER 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 LEFT JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID
ORDER BY E1.EmployeeID;
위에 썼던것중 JOIN을 LEFT (OUTER) JOIN으로만 바꿨다.
그러니까 왼쪽 테이블을 기준에 있는 데이터를 기준으로 모든 데이터를 조회했다.
SELECT
  IFNULL(E1.EmployeeID, '--') EmployeeID, 
  IFNULL(CONCAT(E1.FirstName, ' ', E1.LastName), '----') Employee,
  E2.EmployeeID, 
  CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee
FROM Employees E1 RIGHT JOIN Employees E2
반대로 RIGHT JOIN을 하면 오른 쪽 테이블 기준으로 모든 데이터값들이 조회된걸 볼 수 있다.
IFNULL을 사용해 안나오는 값에 다른 문자를 넣어줄 수 있다.

IFNULL 과 CONCAT_WS

SELECT
  IFNULL(E1.EmployeeID, '--') EmployeeID, 
  IFNULL(CONCAT_WS(' ', E1.FirstName, E1.LastName), '----') 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;
블로그를 쓰면서 새로 깨달은 사실인데 CONCAT_WS와 IFNULL은 같이 사용하기어려운거 같다.
기본적으로 CONCAT_WS(' ', bla, bla2) 를 해주기 때문에 만약 bla, bla2 에 해당하는 값이 없다면 ' ' 값을 보여준다. 그럼으로 CONCAT_WS를 CONCAT으로 바꿔서 해결했다.

CROSS JOIN

SELECT
  E1.LastName, E2.FirstName
FROM Employees E1
CROSS JOIN Employees E2
ORDER BY E1.EmployeeID;
다시 본론으로 돌아와서 현재 LastName 과 FirstName은 각각 9개씩인데 CROSS JOIN을 하게되면 모든 조합 81 개를 볼 수 있다.
양쪽 테이블 모두 기준으로 모든 데이터를 조회하기때문이다.