Kuma's Curious Paradise
[DB] 조인과 서브쿼리 기초 본문
데이터베이스에서 원하는 결과를 얻는 방법으로는 두 가지가 있다: 조인과 서브쿼리.
조인 (JOIN)
조인은 두 개 이상의 테이블을 결합하여 데이터를 가져오는 방법이다. 조건을 걸지 않고 두 개 이상의 테이블을 결합하면 데이터베이스는 카티전 프로덕트(Cartesian Product)를 생성한다. 예를 들어, 고객 5명과 구매 기록 10개가 있으면, 5 * 10개의 가능한 모든 조합을 생성한다. 이때 조건을 사용하여 테이블 간 '조인'을 걸고 필요한 데이터를 얻을 수 있다.
WHERE Orders.custid = Customer.custid
이 조건은 고객과 주문 ID가 일치하는 경우에만 데이터를 반환한다.
조인을 할 때 주의할 사항
1. 모호한 컬럼 (Ambiguous Columns)
SELECT c.name, custid
FROM Customer c, Orders o
WHERE c.custid = o.custid
ORDER BY c.name ASC, o.saleprice DESC;
이 쿼리는 오류를 발생시킨다. custid 컬럼이 Customer와 Orders 테이블 중 어디에 속한 custid인지 명확하지 않기 때문이다. 이럴 때는 c.custid 또는 o.custid처럼 테이블 별칭을 사용하여 명확히 해야 한다.
2. 동명이인 처리
SELECT c.name, SUM(saleprice)
FROM Customer c, Orders o
WHERE c.custid = o.custid
GROUP BY c.name
ORDER BY c.name DESC;
이 쿼리는 손님이 각각 얼마를 썼는지 정렬하여 보여 준다. 이때 손님 이름으로 그룹을 짓기 때문에 동명이인을 분리해서 처리하지 못한다. 이럴 때는 GROUP BY c.custid를 사용하여 유니크 값인 custid로 그룹을 지으면 동명이인을 올바르게 처리할 수 있다.
다양한 조인 방법
조인은 JOIN 키워드를 사용하여 다음과 같이 작성할 수 있다. JOIN 키워드는 기본적으로 INNER JOIN을 의미한다 :
SELECT c.name, o.saleprice
FROM Customer c JOIN Orders o
ON c.custid = o.custid;
위 쿼리는 INNER JOIN과 ON 키워드를 사용하여 다음과 같이도 작성할 수 있다 :
SELECT c.name, o.saleprice
FROM Customer c INNER JOIN Orders o
ON c.custid = o.custid;세 개의 테이블을 조인하고 싶을 때는 다음과 같이 작성한다 :
SELECT c.name, b.bookname
FROM Orders o INNER JOIN Customer c ON o.custid = c.custid
JOIN Book b ON o.bookid = b.bookid;
이 쿼리는 Orders를 중심으로 Customer와 Book 테이블을 조인한 것이다.
셀프 조인 (Self Join)
같은 테이블을 두 번 사용하여 조인하는 경우를 '셀프 조인'이라고 한다. 예를 들어, Employees 테이블을 한 번은 매니저로, 한 번은 직원으로 간주하여 조인할 수 있다.
SELECT staff.*
FROM employees staff, employees manager
WHERE manager.first_name = 'Alexander'
AND manager.last_name = 'Hunold'
AND staff.manager_id = manager.employee_id;
셀프 조인은 같은 테이블의 특정 컬럼이 다른 컬럼과 같은 값을 가질 때 주로 사용한다. 여기서는 manager_id와 employee_id를 비교하여 staff의 manager_id가 manager의 employee_id와 일치하는 경우를 찾는다.
외부 조인 (Outer Join)
조건에 맞지 않아도 모든 데이터를 보고 싶을 때는 LEFT OUTER JOIN을 사용한다. 이는 손님의 구매 금액을 정렬할 때, 주문이 없는 고객까지 포함하여 데이터를 가져온다:
SELECT c.name, o.saleprice
FROM Customer c LEFT OUTER JOIN Orders o
ON c.custid = o.custid;대규모 데이터셋 처리할 때 조인을 활용하려면?
큰 데이터셋을 효율적으로 처리하기 위해서는 다음과 같은 방법을 사용할 수 있다.
예를 들어, 고객이 1000명이고 즐겨찾기가 10000개인 경우, 장소 10개 중 고객 1이 즐겨찾기한 2개의 장소를 보여주려면 :
-- 10개의 장소를 찾는다
SELECT *
FROM Place p
WHERE ... 조건;
-- 멤버의 즐겨찾기를 찾는다
SELECT *
FROM Like l
WHERE l.member_id = ... 조건;위의 데이터를 각각 별도로 추출한 후 LEFT OUTER JOIN을 사용하여 결합한다.
서브쿼리 (Subquery)
쿼리를 문제 해결 과정이라고 생각해 보자. 한 번에 문제가 풀리지 않을 수도 있다. 그럴 때는 문제를 쪼개서 작은 문제에 대한 답을 구한 뒤, 그 답을 큰 문제를 해결하는 데 쓸 수 있다. 서브쿼리는 하나의 쿼리 안에 든 또 다른 쿼리, 즉 작은 문제를 해결하는 쿼리를 말한다.
예를 들어 :
SELECT bookname
FROM Book
WHERE price = (SELECT MAX(price) FROM Book);
위 예시에서 SELECT MAX(price) FROM Book을 먼저 처리한 후, SELECT bookname FROM Book WHERE price = ___ 를 처리한다.
서브쿼리와 WHERE 절 사용 시 주의 사항
서브쿼리를 쓸 때는 서브쿼리가 반환하는 행과 열의 수에 주의해야 한다.
- 단일 행 단일 열: SELECT MAX(price) FROM Book;
- 다중 행 다중 열: SELECT bookid, bookname FROM Book;
- 다중 행 단일 열: SELECT bookid FROM Book;
- 단일 행 다중 열: SELECT bookid, bookname FROM Book WHERE bookid = 3;
서브쿼리가 반환하는 결과와 WHERE 절 조건이 맞지 않으면 에러가 발생할 수 있다. 예를 들어:
SELECT bookname
FROM Book
WHERE price = (SELECT bookid FROM Book);다중 열 서브쿼리 에러 예시:
SELECT bookname
FROM Book
WHERE (price) IN (SELECT bookid, bookname FROM Book);위 쿼리는 서브쿼리가 반환하는 열의 수와 WHERE 절의 열 수가 맞지 않아 에러가 발생한다. 조건과 서브쿼리 간의 컬럼 수를 맞춰야 한다.
서브쿼리의 장단점
서브쿼리는 값을 메모리에 적재하므로 빠르게 처리된다는 장점이 있다. 하지만 서브쿼리의 결과가 매우 큰 경우 메모리 사용량이 많아질 수 있어, 이 경우 조인을 사용하는 것이 좋다.
서브쿼리 사용 예제
예제 1: 특정 출판사의 책을 주문한 고객 찾기
SELECT name FROM Customer WHERE custid IN (
SELECT custid FROM Orders o WHERE bookid IN (
SELECT b.bookid FROM Book b WHERE publisher = '대한미디어')
);이 쿼리를 조인으로 바꾸면:
SELECT c.name
FROM Customer c JOIN Orders o ON c.custid = o.custid
JOIN Book b ON o.bookid = b.bookid
WHERE b.publisher = '대한미디어';예제 2: 출판사별 평균 가격보다 높은 책 찾기
SELECT b1.* FROM Book b1 WHERE b1.price > (
SELECT AVG(b2.price) FROM Book b2 WHERE b2.publisher = b1.publisher );
위 쿼리를 조인으로 바꾸면:
SELECT b1.* FROM Book b1 JOIN (
SELECT publisher, AVG(price) avg_price FROM Book GROUP BY publisher ) avg_book
ON b1.publisher = avg_book.publisher WHERE b1.price > avg_book.avg_price;*** 더 알아보기
예제 2 쿼리의 b2.publisher = b1.publisher 부분은 group by와 같은 역할을 수행한다. publisher에 따라 책가격의 평균치를 추출한다. 하지만 실제로 group by를 쓰면 쿼리가 수행되지 않는다.
select b1.*
from Book b1
where b1.price = (select avg(b2.price) b2price from Book b2 group by publisher);
에러 메시지는 다음과 같다.
Error Code: 1242. Subquery returns more than 1 row
b1.price와 비교를 하려면 subquery의 값이 단일행, 단일열이어야 한다. group by를 하면 (하나가 아니라) 각각의 publisher 책의 평균치가 도출되기 때문에 one : many가 되어 비교가 불가능한 것.