[ MySQL ] 상관,비상관 서브쿼리와 JOIN

이 게시글에 모든 예시는 아래 링크를 통해서 사용해 볼 수 있다.스키마 이름이나 칼럼이름 역시 같아 결과값이 그대로 적용되어 보여진다.

https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_select_all

MySQL Tryit Editor v1.0

 

비상관 서브쿼리

연계되지않고 독립적으로 반환되는 서브쿼리를 뜻한다.

 

예제

SELECT
  CategoryID, CategoryName, Description,
  (SELECT ProductName FROM Products WHERE ProductID = 1)
FROM Categories;
-- 위의 Category ID, NAME, DESCRIPTION 모두 Categories 테이블에
-- 속하는 데이터이지만 괄호안에 서브쿼리는 Products 테이블에서 가져온 데이터이다.


SELECT * FROM Products
WHERE Price < (
  SELECT AVG(Price) FROM Products
);
-- Price 가 Products테이블에서 평균 Price보다 낮은 경우만 보여준다.


SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID =
  (SELECT CategoryID FROM Products
  WHERE ProductName = 'Chais');
-- Products 테이블에서 CategoryID기준으로 ProductName = 'Chais'인 데이터를
-- 가져와서 Categories테이블에서 CategoryID와 같은 CategoryID, CategoryName, Description의 데이터를 보여준다.


SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID IN
  (SELECT CategoryID FROM Products
  WHERE Price > 50);
-- 위와 마찬가지로 역순으로 괄호부터 해석해나가면 좀 더 쉽게 접근이 가능하다.

 

ALL, ANY

• ~ ALL(서브쿼리) = 서브쿼리의 모든 결과에 대해 ~하다
• ~ ANY(서브쿼리) = 서브쿼리의 하나 이상의 결과에 대해 ~하다

 

연산자 + ANY(SUBQUERY)

> ANY	>= ANY	< ANY	<= ANY	= ANY	!= ANY
최소값보다 크면	최소값보다 크거나 같으면	최대값보다 작으면	최대값보다 작거나 같으면	IN과 같은 기능	NOT IN과 같은 기능

 

연산자 + ALL(SUBQUERY)

> ALL	>= ALL	< ALL	<= ALL	= ALL	!= ALL
최소값보다 크면	최소값보다 크거나 같으면	최대값보다 작으면	최대값보다 작거나 같으면	서브 쿼리의 결과가 1건이면 괜찮지만 여러 건이면 오류가 발생	서브 쿼리의 결과가 1건이면 괜찮지만 여러 건이면 오류가 발생

 

예제

SELECT * FROM Products
WHERE Price > ALL (
  SELECT Price FROM Products
  WHERE CategoryID = 2
);
-- 서브쿼리 = CategoryID가 2인 Price들을 모두 가져온다. 그리고
-- 그 모든 데이터 들중 어떤 것보다(ALL의 의미이며 현재는 수의 비교이기 때문에
-- 데이터들 중에서 가장 큰 값이 기준이 된다.) Price가 큰 것을 가져온다.


SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID = ANY
  (SELECT CategoryID FROM Products
  WHERE Price > 50);
-- 위와 같은 조건인 경우 in을 쓰는게 더 적합하다.
-- ANY 의 경우는 IN으로 사용하면 같은 결과를 반환한다.

 

 

상관 서브쿼리

반환되는 쿼리로부터 연계되는 서브쿼리를 뜻한다.

 

예제

SELECT
  ProductID, ProductName,
  (
    SELECT CategoryName FROM Categories C
    WHERE C.CategoryID = P.CategoryID
  ) AS CategoryName
FROM Products P;
-- Categories C 이것은 AS를 생략한 문구로 C로 별명을 지어준 것이다.
-- 마찬가지로 Products P를 통해서 P로 별명을 만들어준 후 P. 으로 CategoryID에
-- 접근하고 C. 을 통해 CategoryID에 접근한 것이다. AS를 생략할 경우에는 절대로
-- 문자열을 사용하면 안된다. 공백 또한 안된다. 공백을 넣고 싶다면 백틱으로 별명을
-- 지어주고 접근할때역시 백틱을 사용해줘야 한다.

 

EXISTS / NOT EXISTS 연산자

예제

SELECT
  CategoryID, CategoryName
  -- ,(SELECT MAX(P.Price) FROM Products P
  -- WHERE P.CategoryID = C.CategoryID
  -- ) AS MaxPrice
FROM Categories C
WHERE EXISTS (
  SELECT * FROM Products P
  WHERE P.CategoryID = C.CategoryID
  AND P.Price > 80
);
-- EXISTS의 서브쿼리에 맞게 존재하는 결과들만 반환한다.

 

JOIN(INNER JOIN) 내부조인

 

예제

SELECT * FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID; 
-- Categories 와 Products 두 테이블을 합쳐준다(JOIN) 어떤 기준으로?(ON)
-- 각 테이블의 CategoryID를 기준으로 합쳐준다.

SELECT C.CategoryID, C.CategoryName, P.ProductName
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID; 
-- ambiguous 주의!(같은 이름의 column이 있는경우 뜨는 에러)
-- 조인으로 Products에서 Categories테이블의 CategoryID와 Products의
-- CategoryID가 같은경우만 반환하고 그 후 P. 으로 접근하여 ProductName를 반환한다.

 

여러 테이블 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하여 사용할 수 있다.

 

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;
-- C.CategoryID의 기준으로 GROUPING 해줬다. C.CategoryID는 1~8까지
-- 중복되는 숫자가 없고 종류별로 있기 때문에 종류별로 퍼스트,라스트오더와 팔린갯수를
-- 모두 볼 수 있다.


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;
-- C.CategoryID를 첫 기준으로 오름차순으로 정렬 된 후 P.ProductID도
-- 오름차순으로 정렬한다.

 

SELF JOIN - 같은 테이블끼리 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번의 전, 마지막 번호의 다음이 없다.(외부 조인으로 해결가능)
-- 위와같이 같은 테이블이지만 별명을 다르게 만들어줘서 셀프조인이 가능하다.

 

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;

-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것
-- 반환된 양쪽 데이터에서 오른쪽이나 왼쪽데이터가 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는 SupplierName을 가지지 않은 CustomerName도 반환한다. RIGHT는 반대이다.


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로 바꿔서도 실행해 볼 것
-- IFNULL을 사용해서 NULL일 경우의 반환 될 값을 넣어 줄 수 있다.

 

CROSS JOIN - 교차조인

SELECT
  E1.LastName, E2.FirstName
FROM Employees E1
CROSS JOIN Employees E2
ORDER BY E1.EmployeeID;
-- 직원들의 각각 LastName에 이름을 추가한 데이터를 모두 조합한다. 직원은 모두 9명이 있는데
-- LastName + FirstName을 반복 조합하기 때문에 총 81개의 이름조합 데이터를 가져온다.

 

UNION - 집합으로 다루기

• UNION = 중복을 제거한 집합
  
• UNION ALL = 중복을 제거하지 않은 집합

SELECT CustomerName AS Name, City, Country, 'CUSTOMER'
FROM Customers
UNION
SELECT SupplierName AS Name, City, Country, 'SUPPLIER'
FROM Suppliers
ORDER BY Name;
-- 위에 쿼리와 아래 쿼리를 같은 별명으로 만들어 줘서 합친 후에 모두 불러오는 기능이다.

 

합집합

SELECT CategoryID AS ID FROM Categories
WHERE CategoryID > 4
UNION
SELECT EmployeeID AS ID FROM Employees
WHERE EmployeeID % 2 = 0;

-- UNION ALL로 바꿔볼 것

-- CategoryID는 1~8이고 EmployeeID는 1~9이다. UNION을 사용했기 때문에 5, 6, 7, 8
-- 그리고 EmployeeID의 2의 배수인 2, 4를 반환한다. 그리고 6, 8은 중복이기 때문에 생략됐다.

-- 만약 UNION ALL 을 사용하게 되면 합집합. 즉, 생략하지 않고 모두 반환하기 때문에
-- 값은 5, 6, 7, 8, 2, 4, 6, 8 을 반환하게 된다.

 

 

교집합

SELECT CategoryID AS ID
FROM Categories C, Employees E
WHERE 
  C.CategoryID > 4
  AND E.EmployeeID % 2 = 0
  AND C.CategoryID = E.EmployeeID;
-- FROM에서 Employees E를 선언해 준 것은 WHERE에서 써먹기 위함이고 조건으로
-- C.CategoryID = E.EmployeeID 를 추가해서 교집합을 가져온다.

 

차집합

SELECT CategoryID AS ID
FROM Categories
WHERE 
  CategoryID > 4
  AND CategoryID NOT IN (
    SELECT EmployeeID
    FROM Employees
    WHERE EmployeeID % 2 = 0
  );
-- 조건을 통해서 차집합을 만들어준다.

 

대칭 차집합

SELECT ID FROM (
  SELECT CategoryID AS ID FROM Categories
  WHERE CategoryID > 4
  UNION ALL
  SELECT EmployeeID AS ID FROM Employees
  WHERE EmployeeID % 2 = 0
) AS Temp 
GROUP BY ID HAVING COUNT(*) = 1;
-- 조건을 통해서 두 쿼리사이에 교집합이 아닌 모든 것들을 반환하게 만들었다.

 

마치며...

MySQL에 대한 기술포스팅은 여기까지 해야겠다... 무궁무진하게 많기 때문에 가장 자주 쓰일 것 같은 내용만 포스팅 했다. 실제로 MySQL내에서도 사용하면서 응용만 한다면 어떤 데이터든 가져 올 수 있겠다는 자신감 조금은 생긴 것 같다.

 

예시자료 출처 -  https://www.yalco.kr/lectures/sql/