[SQL] SQL 서브쿼리 총정리

SQL에서 서브쿼리는 굉장히 중요한 도구이다. 쿼리를 작성할 땐 성능이 굉장히 중요한 요소인데 서브쿼리를 잘 활용하면 필요없는 조인 연산을 줄이거나, 두번에 걸쳐 쿼리를 작성해야 하는 등의 비효율을 줄일수 있다.

 

SQL 서브쿼리

 

WHERE절 서브쿼리

 

WHERE절 서브쿼리에는 크게 서브쿼리에서 메인쿼리의 정보를 참고하는지 아닌지에 따라서 연관서브쿼리, 비연관서브쿼리로 나뉘고, 서브쿼리의 결과값이 하나의 값이면 단일값, 여러값이면 다중값, 여러행이면(여러 컬럼에 대한 정보이면) 다중행 서브쿼리가 된다.

 

예를 들어

SELECT A.EMP_NO, A.EMP_NM, A.DEPT_CD FROM TB_EMP A 
WHERE A.DEPT_CD = (
SELECT DEPT_CD FROM TB_EMP
WHERE EMP_NO = '1000000005' );

여기서 사용된 WHERE절 서브쿼리를 보면 단일값이 나오고, 메인쿼리의 데이터를 이용하고있지 않기에 비연관 단일값 서브쿼리라고 할수 있다. 만약 서브쿼리를 사용하지 않았다면 EMP_NO = '100000005'인 값을 찾은 뒤 다시 그 값을 검색하는 쿼리를 작성해야겠지만, 서브쿼리를 활용하면 이 과정을 한번에 처리할수 있다.

또한 단일값 서브쿼리는 그 결과값이 단일값이기에 항상 비교 연산자(=, <. <=, >, >=, <>) 등을 사용한다.

 

 

SELECT A.EMP_NO, COUNT(*) CNT FROM TB_EMP_CERTI A 
WHERE A.CERTI_CD IN
(
SELECT K.CERTI_CD FROM SQLD.TB_CERTI K 
WHERE K.ISSUE_INSTI_NM = '한국데이터베이스진흥원 '
) 
GROUP BY A.EMP_NO ORDER BY A.EMP_NO

여기선  'K.ISSUE_INSTI_NM = '한국데이터베이스진흥원'를 만족시키는 여러  A.CERTI_CD 값이 나오므로 다중값 비연관 서브쿼리라고 할 수 있다. 다중값 서브쿼리는 여러 값이 나오기에 다중값 비교 연산자(IN, ALL, ANY, SOME, EXISTS)와 함께 쓰인다.

 

SELECT A.EMP_NO , A.EMP_NM , A.DEPT_CD , B.DEPT_NM , A.BIRTH_DE
FROM TB_EMP A , TB_DEPT B 
WHERE (A.DEPT_CD, A.BIRTH_DE) IN (
SELECT K.DEPT_CD, MIN(K.BIRTH_DE) AS MIN_BIRTH_DE FROM TB_EMP K 
GROUP BY K.DEPT_CD HAVING COUNT(*) > 1
) 
AND A.DEPT_CD = B.DEPT_CD ORDER BY A.EMP_NO
;

이번엔 다중행 서브쿼리이다. 메인 쿼리의 조건 절에 여러 컬럼을 동시에 비교할 수 있고, 서브 쿼리와 메인 쿼리의 컬럼 수와 컬럼 순서가 동일해야 한다.

 

 

예제1

 

SELECT CONCAT(firstName, ' ', lastName) AS '성명', jobtitle AS '직책'
FROM s_employees
WHERE employeeId IN (
    SELECT salesRepId
    FROM s_customers c
    WHERE c.customerId NOT IN (
        SELECT DISTINCT o.customerId
        FROM s_orders o
    )
)
ORDER BY '성명';

 

WHERE절을 이중으로 사용하였다. 우선 orders 테이블에서 존재하는 모든 customerId를 빼낸 이후 여기에 존재하지 않는 salesRepId를 뽑아냈다. 서브쿼리에서 나온 salesRepId 데이터중 employeeId가 포함되는 즉, 주문을 하지 않은 고객의 판매담당 직원 아이디를 뽑아낼수 있었다.

 

 

예제2

SELECT c.name, concat(e.firstName, ' ', e.lastName) '직원 성명'
FROM s_customers c 
    LEFT JOIN s_employees e on c.salesRepId = e.employeeId
WHERE customerId NOT IN (
    SELECT customerId
    FROM s_orders
    )
ORDER BY 1

 

이번엔 출력해야하는 데이터가 고객과 직원에대한 정보 모두를 포함하니 customers 컬럼과 employees 컬럼을 조인할수밖에 없었다. 이를 통해 SELECT절에서 각각 다른 테이블의 컬럼값이 필요하다면 조인을 사용하고 그렇지 않다면 최대한 서브쿼리를 사용하면 되겠다는 나름의 기준을 세울수 있었다.

 

판매담당 직원이 없는 고객도 출력에 포함해야하기에 customers 테이블쪽으로 LEFT JOIN하였다. 이번엔 조인을 통해 메인쿼리에서 고객과 관련된 정보까지 이미 연결되어 있기에 예제 1과 동일한 조건이지만 예제 2의 코드에서는 customer와 관련된 정보를 굳이 employee에 연결할 필요가 없었다.

 

 

복잡한 서브쿼리

서브쿼리는 where절 이외에도 다양하게 사용할수 있다. 이들을 포함한 복잡한 서브쿼리를 작성하는 능력이 중요하다.

 

 

예제3

SELECT o.orderNo, o.status
FROM s_orders o
WHERE o.orderNo IN (
    SELECT od1.orderNo
    FROM s_orderDetails od1
    JOIN s_orderDetails od2 ON od1.orderNo = od2.orderNo
    WHERE od1.productCode = 'S10_1678' AND od2.productCode = 'S10_4698'
)
ORDER BY o.orderNo;

만족하는 productCode가 's10_1678'과 's10_4698'이기에 orderDetails 컬럼 자기 자신을 조인했다. 하나의 테이블에서 두가지 조건을 사용하고 싶으면 이런 방법을 사용할수 있다. 

 

 

예제4

 

WITH temp AS
(
	SELECT	country 국가, COUNT(*) 고객수
	FROM 	s_customers
	GROUP 	BY country
)
SELECT	국가, 고객수, 
        (
		SELECT	AVG(고객수)
		FROM	temp
	) 평균고객수
FROM 	temp
WHERE 고객수 > (
		SELECT	AVG(고객수)
		FROM	temp
	)
ORDER BY 2 DESC ,1

WITH temp AS 임시 서브쿼리와 SELECT절 서브쿼리, WHERE절 서브쿼리를 함께 사용한 쿼리이다.

우선 고객수의 평균을 구하기 위해선 집계함수를 두번 적용해야하기에 WITH temp AS 즉, 임시 서브쿼리를 만든 이후에 SELECT 서브쿼리를 통해 고객수 값의 평균을 가져왔고 고객수가 국가별 평균보다 큰 값들만 불러오기 위해 WHERE절 서브쿼리를 사용하였다.