서브쿼리

서브쿼리는 데이터를 필터링할 때 사용하며 SELECT, FROM, JOIN, WHERE, HAVING 등 다양한 SQL 구문의 안에 등장할 수 있다.

EXISTS

EXISTS는 서브쿼리가 최소한 한 개의 행을 반환하면 true로 평가되어, 주로 WHERE 절에서 사용되며 결과 집합의 필터링에 활용. 반환값이 select 1, select id 등 무엇이든 상관없다.

EXISTS 는 SEMI-JOIN 방식으로 동작한다.
JOIN과 SEMI-JOIN 의 차이: 조건을 만족하는 RECORD를 찾으면 실행을 중단할 수 있음.

IN

외부 쿼리의 값(예: ID)이 서브쿼리가 반환한 값 집합에 포함되어 있는지 비교한다. 외부 쿼리 값이 서브쿼리 결과에 있다면 TRUE를 반환해서 결과 집합에 포함시킨다.

서브쿼리는 가상 테이블처럼 작동한다.

ANY (SOME)

any는 in과 유사하게 서브쿼리로 생성된 결과 집합의 값과 비교한다. 왼쪽의 값이 서브쿼리 결과 중 하나와 일치하면 true를 반환하며, 그렇지 않을 경우 false를 반환한다.

쿼리 예시

SELECT id, first_name, last_name, admission_score
FROM student
WHERE admission_score > ANY (
    SELECT student_grade.grade
    FROM student_grade
    WHERE
        student_grade.student_id = student.id
)
ORDER BY id

ALL

서브쿼리의 모든 결과 값과 비교하여, 왼쪽 값이 모든 값보다 크거나 작으면 true, 그렇지 않으면 false를 반환한다.

쿼리 예시

SELECT id, first_name, last_name, admission_score
    FROM student
    WHERE admission_score < ALL (
        SELECT student_grade.grade
        FROM student_grade
        WHERE
            student_grade.student_id = student.id
    )
    ORDER BY id

subquery로 column을 표현하는 경우

subquery를 column을 표현하는데 사용하기 위해서는 반드시 단일 값을 반환해야 한다는 점에 주의해야한다.

문제 상황 아래 쿼리는 학생 정보와, 학생의 평균 성적을 구한다고 해보자.

초기 솔루션

이런 형태로 쿼리를 작성하게 되는 경우가 흔하게 발생된다.

SELECT
    s.id, s.first_name, s.last_name,
    (
        SELECT AVG(sg.grade)
        FROM student_grade sg
        WHERE sg.student_id = s.id
    ) AS avg_grade
FROM student s
ORDER BY s.id
LIMIT 100

이 코드는 실행계획을 확인하니 각 row 마다 subquery를 실행하며, 따라서 100번 subquery가 실행되는 것으로 확인되었다.

개선 솔루션

SELECT
    s.id, s.first_name, s.last_name,
    sg.avg_grade
FROM student s
JOIN (
    SELECT
        AVG(grade) AS avg_grade,
        student_id
    FROM student_grade
    GROUP BY student_id
    ORDER BY student_id
    LIMIT 100
) AS sg ON s.id = sg.student_id
ORDER BY s.id

서브쿼리 수행의 반복 없이 한 번의 수행으로 처리할 수 있게 되었다. (약 2배 개선되었음.)

정리

어떤 방식이 더 빠른지는 사용 환경, 데이터베이스, 버전 등에 따라 달라질 수 있다. 따라서 실행 계획을 반드시 확인해야 하겠다.

또한 subquery 결과에 null 이 포함되어 있을 경우, 결과에 영향을 미칠수 있으므로 주의가 필요하다. null 값의 비교는 is null 또는 is not null 로만 해야한다. 그렇지 않을 경우 true, false 가 아닌 null로 평가되어 결과에 영향을 미칠 수 있다.