서브쿼리와 상관 서브 쿼리

1. 서브쿼리란?

SQL 문장 안에 포함된 다른 SQL 문장을 의미한다. 일반적으로, 서브쿼리는 다른 SQL 문장의 조건절 안에 포함시켜, 조건절에 해당하는 값을 서브쿼리로 가져와서 사용한다.

 

1) SELECT문 안에서 사용하는 서브쿼리

(1) 서브쿼리 사용하여 특정 값과 비교하기

[ 기본 형태 ]
SELECT column1, column2
FROM table
WHERE column1 = (SELECT column1 FROM table2 WHERE column2 = 'value');

# 예시 코드
SELECT order_id, price
FROM productorder
WHERE price >= (
	SELECT AVG(price)
	FROM productorder);
# 결과: price의 평균값 이상인 레코드를 productorder 테이블에서 가져옴!

(2) 서브쿼리 사용하여 집계 함수 계산하기

[ 기본 형태 ]
SELECT column1, (SELECT AVG(column2) 
FROM table2 WHERE column1 = t1.column1) AS avg
FROM table1;

# 예시 코드
SELECT order_id, price, (SELECT COUNT(*) FROM productorder) AS order_count
FROM productorder
ORDER BY price
LIMIT 3;
# 결과: productorder 테이블에서 price 순으로
# 3건의 order_id와 price, productorder 테이블의 전체 레코드 수를 가져온다.

(3) 서브쿼리 사용하여 여러 값 가져오기: 복수행 서브 쿼리

[ IN연산자 이용한 기본 형태 ]
SELECT column1, column2
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE column2 > 10);

해석: table2에서 column2가 10보다 큰 경우의 column1 값을 가져와서 table1의 column1 값과 비교한다.
이때, 서브쿼리의 결과는 여러 개일 수 있기 때문에 IN 연산자를 사용한다.

[ ANY 연산자, ALL 연산자 ]
column1 < ANY ( 10, 20, 30 )
column2 >= ALL( 10, 20, 30 )

# 예시 코드
SELECT customer_id, customer_name
FROM customer
WHERE cutomer_id IN(
	SELECT DISTINCT customer_id
	FROM productorder
	WHERE price >= 700);
# 해석1: productorder 테이블 안에 있는 price 값이 700 이상인 customer_id를 서브쿼리에서 가져온다.
# 해석2: 중복 레코드가 있을 수 있기 때문에 DISTINCT 사용
# 해석3: 서브 쿼리의 결과와 IN 연산자를 사용하여 customer 테이블에서 고객 정보를 가져온다.

TIP> 복수행 서브 쿼리 사용 시 주의사항

IN 연산자나, ANY 연산자에서 결과 안에 NULL이 들어 있을 경우, 연산 결과가 NULL이 된다.
때문에 서브 쿼리의 결과에서 NULL을 제외하여 데이터 무결성을 유지해야 한다.

[ 서브 쿼리 결과에서 NULL 제외 방법 ]
SELECT * FROM table1
WHERE column1 IN (
SELECT column2 FROM table2 WHERE column2 IS NOT NULL);

(4) 조건에 맞는 대상자 선정 후 요약이 필요한 경우

[ 기본 형태 ]
SELECT column1, column2
FROM (SELECT * FROM talbe1 WHERE conditions) (AS) Tb1
WHERE conditions;

해석: 조건에 맞는 대상자를 먼저 FROM 이하 SELECT문을 이용하여 선정한 후, 데이터를 요약함.
주의사항: 별칭 생략 시 오류 발생함

2) INSERT문 안에서 사용하는 서브쿼리

서브쿼리(SELECT 문장) 결과를 INSERT INTO로 새로운 테이블에 추가할 수 있음.

[ 기본 형태 ]
INSERT INTO table1 (column1, column2, column3)
SELECT column1, column2, column3
FROM table2 
WHERE condition;

3) UPDATE문 안에서 사용하는 서브쿼리

서브쿼리(SELECT 문장) 결과를 UPDATE 문자에서 업데이트할 데이터로 사용할 수 있음.

[ 기본 형태 ]
UPDATE table1
SET column1 = (SELECT column2 FROM table2 WHERE condition)
WHERE condition2;

2. 상관 서브 쿼리란?

상관 서브쿼리는, 바깥쪽 쿼리의 행(ROW)들을 참조하고 안쪽 쿼리의 결과를 바탕으로 바깥쪽 쿼리의 결과를 계산하는 서브쿼리를 뜻한다.

상관 서브쿼리는 서브쿼리 안에 있는 SELECT 문이 바깥쪽 쿼리의 컬럼 또는 조건절에서 참조하는 컬럼과 관련되어 있다. 이때, 안쪽 쿼리의 SELECT 문이 바깥쪽 쿼리에서 실행될 때마다 다른 결과를 반환할 수 있다.

 

3. 상관 서브 쿼리 유형

1) 스칼라 서브쿼리

하나의 값만 반환하는 서브쿼리로, 비교연산자나 WHERE절에 사용된다.

**[ 예시 코드 ]**
SELECT employee_name, salary  -- 조회 컬럼 선택: 직원명, 급여
FROM employees as e -- 테이블 선택: employees (별칭 e)
WHERE salary > (SELECT AVG(salary) -- 조회 컬럼 선택: 평균값(급여)
								FROM employees -- 테이블 선택: employees
								WHERE department = e.department); -- 조건 설정1: 부서 동일
# 조건 설정2: [메인쿼리] salary > 서브쿼리(결과: 동일한 부서 직원의 평균급여)

전체 해석: employees 테이블에서 employee_name과 salary를 가져오는데, 조건은 각 부서(department)에서 현재 직원(e)의 부서와 같은 부서에 근무하는 직원들의 평균 급여보다 높은 급여를 받는 직원들만 선택함

기능별 해석1: 서브쿼리 (SELECT AVG(salary) FROM employees WHERE department = e.department) 는 각 부서에서 근무하는 직원들의 평균 급여를 계산! 이 서브쿼리는 메인 쿼리의 WHERE 조건에서 salary > (서브쿼리) 로 사용됨!

기능별 해석2: 메인 쿼리는 서브쿼리의 결과값을 이용하여 현재 직원이 근무하는 부서와 같은 부서에 근무하는 직원들의 평균 급여보다 높은 급여를 받는 직원을 선택!

2) 인라인 뷰 서브쿼리

가상 테이블의 형태로, 서브쿼리에서 반환된 결과를 바깥쪽 쿼리에서 사용할 수 있도록 만들어준다. FROM 절에 사용된다.

**[ 예시 코드 ]**
SELECT e.employee_name, d.department_name, e.salary  -- 조회 컬럼 선택: 직원명, 부서명, 급여
FROM employees as e, -- 테이블 선택: employees (별칭 e) 
     (SELECT department_id, department_name -- 조회 컬럼 선택: 부서아이디, 부서명 
			FROM departments) as d  -- 테이블 선택: departments
WHERE e.department_id = d.department_id; 
# 조건: employees 테이블의 id와 department 테이블의 id 동일

전체 해석: employees와 departments 테이블을 이용하여 employee_name, department_name, salary 정보를 가져옴

기능별 해석1: employees와 departments 테이블은 department_id로 연결되어 있다.

기능별 해석2: 이 쿼리에서는 subquery를 사용하여 departments 테이블에서 department_id와 department_name을 가져와 d라는 이름으로 임시 테이블로 만들고, employees와 d를 조인하여 department_id가 같은 행들을 연결한다. 그리고 이렇게 연결된 행에서 employee_name, department_name, salary 정보를 가져와서 반환한다.