🌹 서론
기존에 서브 쿼리가 아닌 JOIN을 자주 사용하였었는데, 이는 JOIN에 대한 익숙함 때문이었고 서브 쿼리에는 어떠한 특징이 있는지에 대해서 무지한 상태였다.
그래서 이번 기회에 Real MySQL 책을 읽으며 서브 쿼리의 특징 및 장, 단점을 이야기 해보고자 한다.
🌷 본론
서브쿼리란?
서브 쿼리(Subquery)는 하나의 SQL 쿼리 안에 포함된 또 다른 쿼리를 의미한다.
쉽게 말해, 메인 쿼리의 조건이나 데이터를 정의하는 데 사용되는 작은 쿼리이다.
이러한 서브 쿼리를 통해서 단위 처리별로 쿼리를 독립적으로 작성할 수 있고, JOIN과 같이 여러 테이블을 섞어 두는 형태가 아니어서 쿼리의 가독성도 높아지고, 복잡한 쿼리도 손쉽게 작성할 수 있다.
이러한 서브 쿼리를 예시와 함께 더 자세히 알아보자.
아래와 같이 두 테이블이 있다.
EMPLOYEES 는 직원 정보를 담고 있는 테이블이고 아래와 같은 컬럼들이 존재한다.
employee_id(pk),name,department_id(fk),salary
DEPARTMENTS 는 부서 정보를 담고 있는 테이블이고 아래와 같은 컬럼들이 존재한다.
department_id(pk),department_name
서브 쿼리는 쿼리의 여러 위치에서 사용될 수 있는데, 대표적으로 SELECT 절, FROM 절, WHERE 절에 사용될 수 있고 사용되는 위치에 따라 쿼리의 성능 영향도와 MySQL 서버의 최적화 방법은 완전히 달라진다.
이 중 이번 글에서는 SELECT 절에 사용되는 서브 쿼리의 특징을 알아보자.
SELECT 절에 사용된 서브 쿼리
SELECT name, (SELECT MAX(salary) FROM employees) AS highest_salary
FROM employees;SELECT 절에 사용된 서브 쿼리는 내부적으로 임시 테이블을 만들거나 쿼리를 비효율적으로 실행하게 만들지 않기에 서브 쿼리가 적절히 인덱스를 사용할 수 있다면 크게 주의 할 사항은 없다.
그러나 SELECT 절에 서브 쿼리를 사용할 때 주의 해야 할 점이 있다.
바로 SELECT 절의 서브 쿼리에는
로우 서브쿼리를 사용할 수 없고, 오로지스칼라 서브쿼리만 사용할 수 있다.
로우 서브 쿼리와 스칼라 서브쿼리는 만들어내는 결과에 따라 구분 되어진다.
먼저 스칼라 서브 쿼리는 레코드의 칼럼이 각각 하나인 결과를 만들어내는 서브 쿼리이다.
반면 로우 서브 쿼리는 스칼라 서브 쿼리보다 레코드나 칼럼 수가 많은 결과를 만들어내는 서브 쿼리를 칭한다.
이렇듯 일반적으로 SELECT 절에 서브 쿼리를 사용하면 그 서브 쿼리는 NULL이든 아니든 항상 칼럼과 레코드가 하나인 결과를 반환 해야 한다.
SELECT name, (SELECT salary FROM employees) AS highest_salary
FROM employees;위의 경우에는 2건 이상의 레코드를 반환하게 되면 에러가 발생한다.
SELECT name, (SELECT salary, department_id FROM employees) AS highest_salary
FROM employees;위의 경우에는 서브 쿼리가 2개 이상의 컬럼을 가져오기 때문에 에러가 발생한다.
서론에서도 이야기 되었듯 JOIN으로 처리 해도 되는 쿼리(여러 테이블의 데이터가 필요한 경우)를 SELECT 절의 서브 쿼리를 사용해서 작성할 때도 있다.
그러나 이는 서브 쿼리로 실행될 때보다 조인으로 처리할 때가 조금 더 빠르기에 가능하다면 조인으로 쿼리를 작성하는 것이 좋다.
추가적으로 SELECT 절에 서브 쿼리를 사용할 때 아래와 같이 동일한 서브 쿼리가 여러번 사용되는 경우가 있다.
아래 쿼리는 EMPLOYEES와 DEPARTMENTS 테이블을 사용하여 각 직원의 이름, 그 직원이 속한 부서의 이름, 그리고 해당 부서에서 가장 높은 급여와 평균 급여를 모두 반환하려고 하는 예시이다.
SELECT
e.name,
(SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id) AS department_name,
(SELECT MAX(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id) AS highest_salary,
(SELECT AVG(e3.salary) FROM employees e3 WHERE e3.department_id = e.department_id) AS average_salary
FROM employees e;이 경우 위에서 이야기 했던 서브 쿼리의 LIMIT 1 조건 때문에 매번 반복되는 서브 쿼리를 볼 수 있다.
만약 department_name, highest_salary, average_salary를 한번의 연산으로 가져오게 된다고 하면 이러한 문제를 해결할 수 있을 것이다.
이를 레터럴 조인으로 해결 할 수 있는데, 레터럴 조인을 통해 동일한 서브 쿼리를 반복 실행 하지 않고 한번 실행 후 여러 번 사용할 수 있다.
레터럴 조인은 서브 쿼리와 마찬가지로 하위 쿼리가 메인 쿼리의 특정 행을 참조할 뿐만 아니라, 서브 쿼리와는 달리 한번 계산된 값을 재활용하여 중복 실행을 방지하고 성능을 최적화할 수 있다.
SELECT
e.name,
d.department_name,
stats.highest_salary,
stats.average_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN LATERAL (
SELECT
MAX(e2.salary) AS highest_salary,
AVG(e2.salary) AS average_salary
FROM employees e2
WHERE e2.department_id = e.department_id
) AS stats;
위 레터럴 조인에서는 기존 서브 쿼리에서 3번의 연산을 거쳤던 작업을 하나의 연산으로 처리하는 모습이다.
