추석맞이/ MySQL 공부 Chapter 2 - ⓵

flobeeee·2021년 9월 24일

2021.09

강의

목록 보기

8/14

Chapter 2. SELECT 심화

🧐 1. 쿼리 안에 서브쿼리

- 비상관 서브쿼리

SELECT
CategoryID, CategoryName, Description,
(SELECT ProductName FROM Products WHERE ProductID = 1)
FROM Categories;

Categories에서 CategoryID, CategoryName, Description
그리고 Products에서 ProductID가 1인 ProductName을 가져오기

의미있는 쿼리는 아님

SELECT * FROM Products
WHERE Price < (
SELECT AVG(Price) FROM Products
);

Products에서 Price의 평균값보다 작은 Price를 가진 데이터 가져오기

SELECT
CategoryID, CategoryName, Description
FROM Categories
WHERE
CategoryID =
(SELECT CategoryID FROM Products
WHERE ProductName = 'Chais');

Products에서 ProductName이 Chais인 경우 CategoryID는 1이고,
Categories에서 CategoryID가 1인 데이터의 CategoryName이 음료다.

Categories테이블에서 CategoryID, CategoryName, Description을 가져오는데,
CategoryID가 1인 정보를 가져온다.

즉, ProductName이 Chais인 Categories 데이터를 가져온다는 뜻이다.

SELECT
CategoryID, CategoryName, Description
FROM Categories
WHERE
CategoryID IN
(SELECT CategoryID FROM Products
WHERE Price > 50);

Products테이블에서 Price가 50초과인 것들에 대한 Categories 정보들 가져오기

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

SELECT * FROM Products
WHERE Price > ALL (
SELECT Price FROM Products
WHERE CategoryID = 2
);

Products테이블에서 컬럼 전부를 가져오는데, Price의 조건이 CategoryID가 2인 가격들보다 전부 커야한다.

CategoryID = 2 인 데이터들 중 가장 큰 Price보다 큰 데이터들만 가져온다는 뜻이다.

SELECT MAX(Price) FROM Products WHERE CategoryID = 2;
이 쿼리를 활용해서 가장 큰 Price를 알 수 있다.

SELECT
CategoryID, CategoryName, Description
FROM Categories
WHERE
CategoryID = ANY
(SELECT CategoryID FROM Products
WHERE Price > 50);

Categories테이블에서 데이터를 가져오는데, CagegoryID가 Products테이블에서 Price가 50 초과인 데이터를 가져오는 것이다.

ANY 를 IN으로 변경해도 같은 결과가 나온다.

- 상관 서브쿼리

SELECT
ProductID, ProductName,
(SELECT CategoryName FROM Categories C
WHERE C.CategoryID = P.CategoryID) AS CategoryName
FROM Products P

Products 데이터가 하나하나 다 보여지는데 해당 데이터와 연결된 Categories의 CategoryName을 명시한다.
C나 P는 편한대로 이름을 지으면 된다.

SELECT
SupplierName, Country, City,
(
SELECT COUNT() FROM Customers C
WHERE C.Country = S.Country
) AS CustomersInTheCountry,
(
SELECT COUNT() FROM Customers C
WHERE C.Country = S.Country
AND C.City = S.City
) AS CustomersInTheCity
FROM Suppliers S;

Suppliers테이블에서 SupplierName, Country, City 를 가져오고
CustomersInTheCountry 컬럼은 Suppliers 각 데이터의 Country와 같은 고객의 수,
CustomersInTheCity 컬럼은 Suppliers 각 데이터의 City와 같은 고객의 수를 가져온다.

SELECT
CategoryID, CategoryName,
(
SELECT MAX(Price) FROM Products P
WHERE P.CategoryID = C.CategoryID
) AS MaximumPrice,
(
SELECT AVG(Price) FROM Products P
WHERE P.CategoryID = C.CategoryID
) AS AveragePrice
FROM Categories C;

Categories테이블에서 CategoryID, CategoryName을 가져오고
MaximumPrice는 CategoriesID마다 Products테이블에서 가장 비싼 금액,
AveragePrice는 CategoruesID마다 Products테이블의 평균금액 가져오기

SELECT
ProductID, ProductName, CategoryID, Price
-- ,(SELECT AVG(Price) FROM Products P2
-- WHERE P2.CategoryID = P1.CategoryID)
FROM Products P1
WHERE Price < (
SELECT AVG(Price) FROM Products P2
WHERE P2.CategoryID = P1.CategoryID
);

같은 테이블로 서브쿼리를 날릴 수 있다.
Products테이블에서 ProductID, ProductName, CategoryID, Price를 가져오는데
Price가 CategoryID별로 평균값보다 낮은 데이터를 가져온다.

주석을 풀어서

SELECT
ProductID, ProductName, CategoryID, Price
,(SELECT AVG(Price) FROM Products P2
WHERE P2.CategoryID = P1.CategoryID)
FROM Products P1
WHERE Price < (
SELECT AVG(Price) FROM Products P2
WHERE P2.CategoryID = P1.CategoryID
);

실제로 평균보다 낮은 가격을 가져왔다는 것을 알 수 있다.

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
);

Categories테이블 데이터마다 Price가 80이 넘는 Products가 있는 Categories 데이터 가져오기

주석을 해제하면

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
);

80 보다 Price가 큰 데이터가 있다는 것을 확인할 수 있다.

🧐 2. JOIN - 여러 테이블 조립하기

JOIN(INNER JOIN) - 내부 조인

양쪽 모두 값이 있는 경우 반환 (NOT NULL)

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

Categories 테이블과 Products 테이블 조인

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

-- ambiguous 주의!
첫 줄에서 CategoryID는 Products, Categories테이블 둘 다 존재하지만 C라고 명시해서
어떤 테이블의 컬럼을 가져올 것인지 명시해줘야한다.
명시하지 않으면 ambiguous 에러 발생

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;

Products 테이블과 Suppliers테이블 조인
Product 컬럼에서는 ProductName + 'by' + SupplierName 형식으로 데이터를 가공해서 보여줌
Price은 50 초과하는 데이터만
ProductName 순으로 정렬해서 가져오기

여러 테이블을 조인할 수 있다.

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;

Categories, Products, OrderDetails, Orders 테이블들을 조인해서
각 테이블에 있는 컬럼 중에 보고싶은 컬럼만 선택해서 볼 수 있다.
Categories테이블에서는 CategoryID, CategoryName
Products테이블에서는 ProductName
Orders테이블에서는 OrderDate
OrderDetails테이블에서는 Quantity를 가져왔다.

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;

Categories, Products, OrderDetails, Orders를 조인
CategoryID로 그룹화해서
각 CategoryID별로 CategoryName, 가장빠른 주문날짜, 가장늦은 주문날짜, TotalQuantity를 가져온다.

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;

Categories, Products, OrderDetails, Orders 테이블들 조인
CategoryID, ProductID로 그룹화해서
CategoryName, ProductName, 가장빠른 주문날짜, 가장늦은 주문날짜, TotalQuantity를 가져온다.

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;

Employee 테이블을 조인해서 현재 Employee와 다음번호 Employee를 한 레코드로 가져온다.

INNER JOIN이라서 마지막 번호가 9 인 경우, 9와 다음 Employee데이터(NULL)는 표기하지 않는다.

LEFT/RIGHT OUTER 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
LEFT JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID
ORDER BY E1.EmployeeID;

LEFT (OUTER) JOIN 은 LEFT에 있는 데이터를 보여주고
그와 조인된 테이블의 데이터가 NULL이라도 다 가져온다.
OUTER은 생략 가능이다.

IFNULL을 넣어서 NULL이면 0을 출력하게 하는 쿼리

RIGHT인 경우

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;

Customers테이블과 Suppliers테이블 LEFT JOIN
Customers에서는 CustomerName, City, Country 컬럼 가져옴
Suppliers테이블에서는 SupplierName가져옴

결과보면 SupplierName이 Null이어도 가져옴

RIGHT JOIN

SupplierName이 Null인 경우 없음. CustomerName, City, Country가 Null인 경우 있음.

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;

IFNULL로 빈 값 대신 넣을 값을 설정할 수 있다.