[SQL] 서브쿼리

 

기본 개념

개념

다른 SQL 문 안에 포함된 SQL문

 

한 번의 조회만으로 필요한 조건값을 알 수 없을 때, 

안쪽 SQL로 값을 구한 뒤 그 결과를 바깥쪽 SQL에서 사용

 

바깥쪽 SQL → 최종 데이터를 조회 => 메인쿼리

안쪽 SQL → 바깥쪽 SQL에 필요한 값이나 데이터를 조회 => 서브쿼리

 

사용 이유

다른 조회를 통해 조건값을 구해야 하는 경우

 

문법

SELECT 조회할_컬럼
FROM 테이블
WHERE 컬럼 연산자 (
    SELECT 조회할_컬럼
    FROM 다른_테이블
    WHERE 조건
);

 

서브쿼리 결과의 형태

서브쿼리 결과	주로 사용하는 형태	예시
값 하나	=, >, < 등의 비교 연산자	SELECT id, name, price FROM products WHERE price > (     SELECT AVG(price)     FROM products );
여러 값	IN, NOT IN	SELECT id, name FROM products WHERE category_id IN (     SELECT id     FROM categories     WHERE status = 'active' );
여러 행과 컬럼	FROM에서 임시 테이블처럼 사용 (테이블 형태의 중간 조회 결과)	SELECT     order_summary.user_id,     order_summary.order_count,     order_summary.total_amount FROM (     SELECT         user_id,         COUNT(*) AS order_count,         SUM(amount) AS total_amount     FROM orders     GROUP BY user_id ) AS order_summary WHERE order_summary.total_amount >= 50000;

 

사용하는 위치

작성 위치	역할	일반적인 반환 형태	문법
WHERE	조회 조건 제공 (메인쿼리의 조건값)	값 하나 또는 여러 값	SELECT 조회할_컬럼 FROM 테이블 WHERE 컬럼 연산자 (     SELECT 조회할_컬럼     FROM 다른_테이블     WHERE 조건 );
SELECT	조회 결과에 표시할 값으로 사용	값 하나	SELECT     컬럼,     (         SELECT 값         FROM 다른_테이블         WHERE 조건     ) AS 별칭 FROM 테이블;
FROM	임시 테이블 생성	여러 행과 여러 컬럼	SELECT     sub.컬럼,     sub.계산_결과 FROM (     SELECT         컬럼,         집계함수(대상) AS 계산_결과     FROM 테이블     GROUP BY 컬럼 ) AS sub;

 

-- WHERE에서 사용 --

-- 전체 상품의 평균 가격보다 비싼 상품을 조회
SELECT
    id,
    name,
    price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

-- 활성 상태인 카테고리에 속한 상품을 조회
SELECT
    id,
    name,
    category_id
FROM products
WHERE category_id IN (
    SELECT id
    FROM categories
    WHERE status = 'active'
);


-- SELECT에서 사용 --

-- 사용자 정보와 전체 주문 수를 함께 조회
SELECT
    u.id,
    u.name,
    (
        SELECT COUNT(*)
        FROM orders
    ) AS total_order_count
FROM users AS u;

-- FROM 에서 사용 --

-- 사용자별 주문 수와 총주문 금액을 먼저 계산한 뒤, 총주문 금액이 50000 이상인 사용자만 조회
SELECT
    order_summary.user_id,
    order_summary.order_count,
    order_summary.total_amount
FROM (
    SELECT
        user_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
) AS order_summary
WHERE order_summary.total_amount >= 50000;

 

 

---

 

상관 서브쿼리

개념

서브쿼리가 메인 쿼리의 현재 행을 참조하는 서브쿼리

 

일반 서브쿼리는 메인 쿼리와 독립적으로 실행할 수 있지만, 

상관 서브쿼리는 메인 쿼리의 값을 사용하므로 단독으로 실행하기 어렵다. 

 

문법

SELECT
    외부_별칭.컬럼,
    (
        SELECT 집계함수(내부_별칭.컬럼)
        FROM 내부_테이블 AS 내부_별칭
        WHERE 내부_별칭.연결_컬럼 = 외부_별칭.연결_컬럼 -- 서브쿼리 내부에서 메인 쿼리의 별칭 참조
    ) AS 결과_별칭
FROM 외부_테이블 AS 외부_별칭;

 

EX]

/**
users)

id	name
1	사용자 A
2	사용자 B
3	사용자 C
orders)

id	user_id
101	1
102	1
103	2
*/


SELECT
    u.id,
    u.name,
    (
        SELECT COUNT(*)
        FROM orders AS o 
        WHERE o.user_id = u.id -- 서브쿼리 안에서 메인 쿼리의 u.id를 참조
    ) AS order_count
FROM users AS u;

/** 결과
=> 사용자 행마다 다른 조건으로 주문 수를 계산 
| id | name  | order_count |
| -: | ----- | ----------: |
|  1 | 사용자 A |           2 |
|  2 | 사용자 B |           1 |
|  3 | 사용자 C |           0 |
*/

 

 

주의

메인 쿼리의 행마다 관련 값을 확인하는 형태가 될 수 있음.

데이터가 많으면 일반 서브쿼리나 JOIN보다 처리 부담이 커질 수 있음. 

 

관련 컬럼에는 적절한 인덱스가 있는 것이 좋다. 

 

---

 

EXISTS와 NOT EXISTS

개념

서브쿼리의 결과가 존재하는지 확인할 때 사용

구문	의미	문법
EXISTS	관련 데이터가 존재하는 행 조회	SELECT 외부_별칭.컬럼 FROM 외부_테이블 AS 외부_별칭 WHERE EXISTS (     SELECT 1     FROM 내부_테이블 AS 내부_별칭     WHERE 내부_별칭.연결_컬럼 = 외부_별칭.연결_컬럼 ); --  서브쿼리에서 메인 쿼리의 값을 참조하므로 일반적으로 상관 서브쿼리 형태로 사용함.
NOT EXISTS	관련 데이터가 존재하지 않는 행 조회	SELECT 외부_별칭.컬럼 FROM 외부_테이블 AS 외부_별칭 WHERE NOT EXISTS (     SELECT 1     FROM 내부_테이블 AS 내부_별칭     WHERE 내부_별칭.연결_컬럼 = 외부_별칭.연결_컬럼 );

 

SELECT 1을 사용하는 이유

서브쿼리가 반환하는 실제 값이 아니라 행이 존재하는지만 확인함.

 

EX]

/**
users)
| id | name  |
| -: | ----- |
|  1 | 사용자 A |
|  2 | 사용자 B |
|  3 | 사용자 C |

orders)
|  id | user_id |
| --: | ------: |
| 101 |       1 |
| 102 |       1 |
| 103 |       2 |
*/

-- 주문한 적이 있는 사용자 조회 
SELECT
    u.id,
    u.name
FROM users AS u
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.user_id = u.id
);

/** 결과
| id | name  |
| -: | ----- |
|  1 | 사용자 A |
|  2 | 사용자 B |
*/

-- 주문한 적이 없는 사용자 조회
SELECT
    u.id,
    u.name
FROM users AS u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.user_id = u.id
    	-- o.user_id -> 주문에 저장된 사용자 ID
        -- u.id -> 현재 확인하고 있는 사용자의 ID
);

/** 결과
설명)
- 사용자 A의 id는 1 -> user_id가 1인 주문 101, 102가 존재 -> EXISTS가 참이 됨, 결과 포함
- 사용자 B의 id는 2 -> user_id가 2인 주문 103이 존재 -> 동일 
- 사용자 C의 id는 3 -> user_Id가 3인 주문은 존재하지 않음 -> 거짓이 됨, 결과 제외 

users의 사용자 한 명씩 확인하면서, 해당 사용자의 주문이 존재하는지 검사함.

결과)
| id | name  |
| -: | ----- |
|  3 | 사용자 C |

*/

 

---

 

JOIN과 서브쿼리의 선택

 

JOIN과 서브쿼리는 서로 관련된 데이터를 이용한다는 공통점이 있지만, 

사용하는 목적이 다름

JOIN → 여러 테이블의 컬럼을 하나의 결과로 연결

서브쿼리 → 다른 조회 결과를 조건값이나 중간 결과로 사용 

 

필요한 결과	적합한 방식
여러 테이블의 컬럼을 함께 조회	JOIN
다른 조회에서 계산한 값과 비교	서브쿼리
특정 값 목록에 포함되는지 확인	IN 서브쿼리
관련 데이터의 존재 여부만 확인	EXISTS, NOT EXISTS
집계한 결과를 다시 조회	FROM 서브쿼리
같은 기준으로 여러 테이블을 연결	JOIN

 

-- JOIN을 사용하는 경우 --

-- 사용자 정보와 주문 정보를 함께 조회
-- => 사용자 컬럼과 주문 컬럼이 모두 필요하므로 JOIN이 적합 
SELECT
    u.id,
    u.name,
    o.id AS order_id,
    o.total_price
FROM users AS u
JOIN orders AS o
    ON u.id = o.user_id;
    
-- 서브쿼리를 사용하는 경우 --

-- 전체 상품의 평균 가격보다 비싼 상품을 조회
-- => 다른 테이블의 컬럼을 결과에 붙이는 것이 아니라, 평균 가격이라는 비교값 하나가 필요함
SELECT
    id,
    name,
    price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

-- 같은 결과를 만들 수 있는 경우 
-- => 주문한 적이 있는 사용자를 조회하는 경우에는 둘 다 사용 가능
SELECT DISTINCT -- 사용자 한 명에게 주문이 여러 개 있으면 사용자 행도 여러 번 만들어질 수 있으므로
    u.id,
    u.name
FROM users AS u
JOIN orders AS o
    ON u.id = o.user_id;
    
SELECT DISTINCT
    u.id,
    u.name
FROM users AS u
JOIN orders AS o
    ON u.id = o.user_id;