강의는 메타코드 사이트(https://mcode.co.kr/)에서 구매할 수 있다.
이제 두 번 정도?만 더 쓰면 부트캠프 강의 정리 글은 마무리될 것 같다. 파이팅🔥🔥
🔥 상관 서브쿼리
지난 글에서는 스칼라 서브쿼리(단일값), 테이블 서브쿼리(행과 열 형태)에 대해 정리했다. 이번엔 "상관" 서브쿼리
상관 서브쿼리는, 다른 서브쿼리와 달리 메인 쿼리의 행을 참조하여 수행한다. 메인 쿼리의 각 행마다 한 번씩 실행되고, 현재 행과 연관된 결과를 반환한다.
뭔가 특별한 서브쿼리 같긴 한데 이해가 잘 되지 않는다면.. 약간 for문을 생각하면 될 것 같다. 반복문처럼 메인 쿼리의 행이 100개면, 각 행에 대해 상관 서브쿼리도 100번 반복하여 돌아가며, 메인쿼리의 값들을 참조해 결과를 반환한다.
예를 살펴보면 조금 더 이해하기 쉽다. 메인 쿼리의 SELECT 외에도 괄호 속에서 SELECT문이 한 번 더 사용된 것을 볼 수 있다. 여기까지 봐서 알 수 있는 것은 '서브쿼리구나!'.
SELECT c.customerName, o.orderDate
FROM customers c, orders o
WHERE c.customerNumber = o.customerNumber
AND o.orderDate =
(SELECT MAX(orderDate) FROM orders WHERE customerNumber = c.customerNumber);
그런 다음 서브쿼리 안을 보자.
서브쿼리를 실행하면 최대값(MAX) 하나만 출력될 거니까 스칼라 서브쿼리인가? 라고 생각하기 쉽다.
그런데! 서브쿼리 내 WHERE 구문을 보면 c.customerNumber를 참조하고 있다.
c는 메인쿼리에서 customer 테이블에 지정한 별칭이고, customer 테이블에서 customerNumber를 참조한다는 의미가 된다. 이런 식으로 괄호 속에서 정의하지 않은, 메인쿼리에 있는 테이블의 값 등을 참조하는 서브쿼리가 상관 서브쿼리다.
상관 서브쿼리를 구분하기 어렵다면 괄호 속 쿼리만 따로 실행해 보면 더 이해하기 쉽다.
상관 서브쿼리는 메인쿼리를 참조하기 때문에 서브쿼리만 단독으로 실행되지 않는다.
문제 1. 2003년에 주문한 모든 고객의 이름을 조회하세요(orders, customers 테이블).
일단, 항상 그렇듯 원래 테이블 먼저 살펴보자. orders 테이블은 아래와 같이 생겼고,
customers 테이블은 아래와 같이 생겼다. (너무 컬럼(열)이 많아서 정보창을 추가로 첨부함)
총 13개의 열이 있는 customers 테이블.
"2003년"에 주문한 고객의 "이름"을 조회해야 하는 문제이다. 주문일자와 관련된 정보는 orders 테이블의 'orderDate'에 저장되어 있고, 고객 이름은 customers 테이블에 있는 'customerName' 에 있다. 안타깝게도 한 테이블로는 해결이 안 되니까 join(연결)을 해야 한다. 아니면 상관 서브쿼리를 사용할 수도 있다.
1) 조인을 사용한 방법
JOIN을 수행하기 위해 두 테이블이 공통적으로 가지고 있는 열을 찾아보자. customerNumber 라는 key를 둘 다 가지고 있다.
SELECT *
FROM orders o
JOIN customers c
ON o.customerNumber = c.customerNumber;
위 쿼리를 수행하면 7개 컬럼 + 13개 컬럼이 합쳐져 총 20개의 컬럼이 붙은 걸 확인할 수 있다.
이제 조건(where 문)을 걸어서, 주문일자가 2003년일 때만 추출하자.
SELECT *
FROM orders o
JOIN customers c ON o.customerNumber = c.customerNumber
WHERE YEAR(orderDate) = 2003;
컬럼이 너무 많으니까, SELECT에서 고객 이름을 불러내고, 고객 이름 순으로 정렬까지 해주자.
SELECT c.customerName
FROM orders o
JOIN customers c ON o.customerNumber = c.customerNumber
WHERE YEAR(orderDate) = 2003
ORDER BY c.customerName;
어라? 정렬을 하고 보니까 고객이름이 같은 게 있네! 같은 고객이 2003년에 두 번 이상 주문을 하면 두 번씩 찍혀서 적절하지 않은 결과가 출력된다. 이럴 땐 DISTINCT(중복제거)를 사용해서 아래와 같이 출력하면 올바른 결과를 볼 수 있다.
SELECT distinct(c.customerName) AS customerName
FROM orders o
JOIN customers c ON o.customerNumber = c.customerNumber
WHERE YEAR(orderDate) = 2003
ORDER BY c.customerName;
이렇게 풀어도 되지만, JOIN 대신 서브쿼리를 쓸 수도 있다. 개념만 크게 설명하면...
고객 테이블에서 고객 명을 가져올 건데,
customers 테이블의 고객번호가 (orders 테이블의 주문일자가 2003년인 고객번호)와 일치하는 것만 뽑을 것이다.
2) 서브쿼리를 사용한 방법
고객 테이블에서 고객 명을 가져올 것인데..
SELECT customerName
FROM customers;
조건이, customers 테이블의 고객번호 = 서브쿼리 내 orders 테이블의 고객번호
SELECT customerName
FROM customers
WHERE customerNumber IN (SELECT customerNumber FROM orders WHERE YEAR(orderDate) = 2003);
IN은 다중행 연산자로 괄호 속에 하나의 컬럼을 넣는다. 상관서브쿼리는 메인쿼리의 행 수만큼 실행된다고 했다. 그러므로 customers 테이블에서 1행부터 마지막 행까지 주문일자가 2003년에 해당하는 고객번호를 찾을 것이고, 해당 고객번호를 가진 고객명을 출력할 것이다.
요렇게 계산하면 중복 없이 깔끔하게 쿼리도 엄청 짧게! 출력할 수 있다. :)
뭔가 부산에서 서울로 가는데 KTX를 타도 되고, SRT를 타도 되는 느낌이랄까? 결국 서울로만 가면 된다. 즉, 결과값만 바르게 출력되면 된다. 그렇지만 두 방법 모두 알아두고 자유자재로 사용할 수 있도록 연습해야겠지..! WITH 구문만 설명하면 부트캠프 정리 끝!