서브쿼리를 알아보자

jonghyun.log·2023년 3월 7일

WHERE select sql subquery

서브쿼리란

서브쿼리란 쿼리 안에 있는 또다른 쿼리를 의미한다.
바깥에 위치한 쿼리를 메인쿼리 그리고 안쪽에 위치한 쿼리를 서브쿼리라고 한다.

서브쿼리는 두가지 기준으로 분류가 가능한데
첫번째로 상관/비상관 여부
두번째로 쓰이는 위치

이렇게 두가지로 분류가 가능하다.

1. 상관/비상관 여부

1-1. 비상관 서브쿼리

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

위와같이 select 문에 서브쿼리가 들어갈수 있다.

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

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

위의 쿼리문은 ProductName = ‘Chais’ 인 데이터들의 categoryId 를 where 절의 조전으로 사용하고 있다.

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

이 쿼리문은 바로 전의 예제와 비슷하지만 where 절의 서브쿼리가 여러개의 값을 만족하고 그 값들을 조건으로 사용하고 싶으면 = 를 사용하는 것이 아닌 IN 을 사용해야 한다.

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

All 은 조건을 모두 만족해야 참을 리턴하는 연산자로

위의 sql은 다음의 sql과 같다.

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

서브쿼리절의 모든 데이터보다 커야하므로 데이터들의 최대값인 MAX(price) 보다 큰것과 같은 의미를 지니게 된다.

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

any 는 조건의 데이터 중에 단 한개라도 만족하면 참을 리던한다. 즉, 위의 쿼리문은 아래의 쿼리문과 같은 값을 리턴한다.

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

서브쿼리절이 리턴하는 한개의 데이터와도 같으면 참이므로 연산자 IN 을 상용해도 같은 결과를 리턴한다.

위와 같이 서브쿼리가 다른 SQL문과 무관하게 즉, 상관이 없이 독립적일때 비상관 서브쿼리 라고 한다.

1-2. 상관 서브쿼리

반면 상관 서브쿼리는 서브쿼리절이 쿼리절과 맞물려서 사용되는 쿼리절을 의미한다.

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

위의 예시 쿼리문은 서브쿼리 안에서 서브쿼리 밖의 Product를 가져와 서브쿼리 내의 where문의 조건으로 사용하고 있다. 즉, 위처럼 서브쿼리와 서브쿼리 밖의 내용이 서로 맞물려서 사용하는 쿼리를 상관 서브쿼리 라고 한다.

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 의 country 와 city 와 Customers 의 country , 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;

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

위의 예제에서는 Products 의 평균가 보다 낮은 물품들의 정보를 나열하기 위해 where 절 안에 서브쿼리를 사용하고
그 서브쿼리안에 서브쿼리 밖의 p1 과 맞물려서 사용했다.

SELECT
  CategoryID, CategoryName
FROM Categories C
WHERE EXISTS (
  SELECT * FROM Products P
  WHERE P.CategoryID = C.CategoryID
  AND P.Price > 80
);

이번에는 Exist 를 사용해서 where 절에 서브쿼리를 만족하는 category가 있는지 여부를 통해 데이터를 판별해서 가지고 오고 있다.

2. 쓰이는 위치에 따른 분류

2-1. where 절에 사용하는 scalar subquery

where 절에 사용되는 쿼리를 스칼라 서브쿼리라고 하며

예시로 다음과 같이 사용한다.

SELECT * FROM HR.EMPLOYEES A
   WHERE A.DEPARTMENT_ID = (
            SELECT B.DEPARTMENT_ID
            FROM HR.DEPARTMENTS B
            WHERE B.LOCATION_ID = 1800);

만약 디비에 위의 WHERE 절안의 서브쿼리를 만족하는 데이터가 한개라면 값이 정상적으로 출력이 될것이다. 위처럼 하나의 값을 리턴하는 서브쿼리를 단일행 서브쿼리 라고 부른다.

하지만 만약, 위의 where 절의 서브쿼리를 만족하는 데이터가 여러가 있다면 위의 쿼리는 정상적인 값을 출력하지 못할것이다.
데이터가 여러개 있는 경우에는 =을 사용하지 않고 IN을 사용해야한다.

서브쿼리를 사용하면 성능상 좋지 않기 때문에 join을 사용해야한다는 인식이 있는데,
SQL Optimizer 가 각 쿼리에 따른 최적화 방법을 찾아주기 때문에 숴브쿼리를 사용하던 join을 사용하던 성능상 큰 차이는 존재하지 않는다.

이를 한번 알아보기 위해 바로 위의 예제 sql 을 join으로 바꿔서 실행해 보도록 하자.

SELECT *
FROM HR.EMPLOYEE A,
            HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
     AND B.LOCATION_ID = 1700;

위의 예시 쿼리에서 EMPLOYEE, DEPARTMENTS 테이블은 공통 컬럼이 없다고 가정하여 위와같은 쿼리를 작성하였다.

디비 계획을 조회하면 위의 쿼리와 where 절의 쿼리는 성능이 똑같다.

하지만 서브쿼리를 사용하면 성능상 비효율적이 되어서 사용하면 안되는 경우도 존재하는데

SELECT  
	A.EMPLOYEE_ID,
	A.FIRST_NAME,
    A.LAST_NAME,
    A.SALARY
 FROM EMPLOYEES A
WHERE A.SALARY = (SELECT MIN(SALARY) FROM A)
   OR A.SALARY = (SELECT MIN(SALARY) FROM A);

다음과 같은 경우에는 서브쿼리를 통해 같은 테이블에 두번 접근하고 있다.
이렇게 동일한 테이블에 여러번 접근하는 서브쿼리는 성능상에 좋지 않다.
그렇다면 어떤식으로 변경해주는게 좋을까?

SELECT  
	B.EMPLOYEE_ID,
	B.FIRST_NAME,
    B.LAST_NAME,
    B.SALARY
 FROM (
 	SELECT  A.EMPLOYEE_ID,
			A.FIRST_NAME,
		    A.LAST_NAME,
		    A.SALARY,
            ROW_NUMBER() OVER(ORDER BY SALARY) MINSAL,
            ROW_NUMBER() OVER(ORDER BY SALARY DESC) MAXSAL
    FROM EMPLOYEES A
 ) B
 WHERE B.MINSAL = 1 OR B.MINSAL = 1;