10. SubQuery

10.1. 서브쿼리

10.1.1. 서브쿼리 개요

10.1.2. 사용 목적

10.1.3. 서브쿼리의 효율성

10.1.4. WITH

1. 가독성: 복잡한 쿼리의 구조를 명확하게 표현할 수 있으며, 각 부분을 분리하여 읽기 쉽게 만듭니다.

2. 재사용성: 동일한 서브쿼리를 여러 번 재사용할 필요 없이 한 번만 정의하면 됩니다.

3. 로직의 순차적 표현: WITH절을 연쇄적으로 정의하면, 쿼리의 실행 로직을 단계별로 표현할 수 있습니다. 이는 특히 데이터 전처리 단계를 순차적으로 표현할 때 유용합니다.

4. 효율성: 데이터베이스에 따라, WITH절의 결과를 캐싱하여 주 쿼리에서의 반복된 참조에 대한 연산을 줄일 수 있습니다.

WITH new_table1 AS ( 
	SELECT 
				column1
	FROM Table1
), new_table2 AS (
	SELECT 
				column1
	FROM Table2
)

SELECT *
FROM new_table1;

SELECT 
			column1
FROM (
			SELECT *
			FROM Table1
			);

WITH employee_data AS (
  SELECT 
				employee_id 
  FROM `HR.employees`
)

SELECT * 
FROM employee_data;

WITH employee_job_counts AS (
  SELECT 
				job_id
			, COUNT(employee_id) AS job_count
  FROM `HR.employees`
  GROUP BY job_id
)
SELECT * 
FROM employee_job_counts
ORDER BY job_count DESC;

WITH employee_counts AS (
  SELECT 
		    LEFT(job_id,2) AS job_code
		  , COUNT(LEFT(job_id,2)) AS employee_count
  FROM `HR.employees`
  GROUP BY job_code
  HAVING COUNT(job_code)>=20
)
SELECT * 
FROM employee_counts;

WITH dep_loc_join AS (
  SELECT 
				d.department_id
			, d.department_name 
			,	l.country_id
  FROM `HR.departments` AS d
  LEFT JOIN `HR.locations` AS l 
  ON d.location_id = l.location_id
), dep_loc_emp_join AS (
  SELECT 
				e.employee_id
			,	e.department_id 
			,	dl.department_name 
			,	dl.country_id
  FROM `HR.employees` AS e
  LEFT JOIN dep_loc_join AS dl
  ON e.department_id = dl.department_id
)
SELECT 
			country_id
		,	department_name
		,	COUNT(employee_id) AS employee_count
FROM dep_loc_emp_join
GROUP BY country_id, department_name;

WITH dep_loc_join AS (
  SELECT 
				d.department_id
			,	d.department_name
			,	l.country_id
  FROM `HR.departments` AS d
  LEFT JOIN `HR.locations` AS l 
  ON d.location_id = l.location_id
)

SELECT *
FROM dep_loc_join;

WITH dep_loc_join AS (
  SELECT 
				d.department_id
			,	d.department_name
			,	l.country_id
  FROM `HR.departments` AS d
  LEFT JOIN `HR.locations` AS l 
  ON d.location_id = l.location_id
), dep_loc_emp_join AS (
  SELECT 
				e.employee_id
			,	e.department_id 
			,	dl.department_name 
			,	dl.country_id
  FROM `HR.employees` AS e
  LEFT JOIN dep_loc_join AS dl
  ON e.department_id = dl.department_id
)

SELECT *
FROM dep_loc_emp_join;

WITH dep_loc_join AS (
  SELECT 
				d.department_id 
			,	d.department_name 
			,	l.country_id
  FROM `HR.departments` AS d
  LEFT JOIN `HR.locations` AS l 
  ON d.location_id = l.location_id
), dep_loc_emp_join AS (
  SELECT 
				e.employee_id
			,	e.department_id 
			,	dl.department_name 
			,	dl.country_id
  FROM `HR.employees` AS e
  LEFT JOIN dep_loc_join AS dl
  ON e.department_id = dl.department_id
)
SELECT 
			country_id
		,	department_name 
		,	COUNT(employee_id) AS employee_count
FROM dep_loc_emp_join
GROUP BY country_id, department_name;

• WITH 문을 사용하는 경우
• “department_id” 별 “salary”의 편차의 합을 구하는 예제입니다.

WITH employee_salary_dev AS (
	 SELECT
				 employee_id
			 , salary - AVG(salary) OVER() AS deviation
	 FROM `HR.employees`
)
SELECT
		  ROUND(SUM(deviation), 2) AS deviations_sum
FROM  employee_salary_dev;



• “department_id” 별 “salary”의 분산을 구하는 예제입니다.

WITH employee_salary_dev AS (
	 SELECT 
				 department_id 
       , salary - AVG(salary) OVER(PARTITION BY department_id) AS deviation
       , COUNT(salary) OVER(PARTITION BY department_id) AS n
   FROM `HR.employees`
)
SELECT 
			 department_id
     , ROUND(SUM(CASE 
                     WHEN n = 1 THEN 0 
                     ELSE (deviation * deviation) / (n - 1) 
                 END), 2) AS variance
FROM  employee_salary_dev
GROUP BY department_id
ORDER BY department_id;

• WITH문을 사용하지 않는 경우
• “department_id” 별 “salary”의 편차의 합을 구하는 예제입니다.

SELECT 
			ROUND(SUM(dev), 2) AS deviations_sum
FROM (
      SELECT
            salary - AVG(salary) OVER() AS dev
      FROM `HR.employees`
			);



• “department_id” 별 “salary”의 분산을 구하는 예제입니다.

SELECT 
			department_id
    , ROUND(SUM(CASE 
                     WHEN n = 1 THEN 0 
                     ELSE (deviation * deviation) / (n - 1) 
                 END), 2) AS variance
FROM (
      SELECT 
						department_id
          , salary - AVG(salary) OVER(PARTITION BY department_id) AS deviation
          , COUNT(salary) OVER(PARTITION BY department_id) AS n
      FROM `HR.employees`
			)
GROUP BY department_id
ORDER BY department_id;

10.2. 서브쿼리의 종류

1. 스칼라 서브쿼리 (Scalar Subquery):
결과로 단일 값을 반환합니다. 일반적으로 SELECT 문의 열 값 또는 WHERE 절의 조건 값으로 사용되며 각 행에 대한 값은 계산하는데 사용할 수 있습니다.

SELECT 
			column1
		, (
			 SELECT 
						 sub_column 
			 FROM sub_table
			 WHERE condition
			 ) AS alias
FROM Table1;

1. 행 서브쿼리 (Row Subquery):
결과로 하나의 행을 반환합니다. 여러 열의 값을 포함하는 단일 행을 반환하며 다중 컬럼의 비교를 위해 WHERE절에서 사용할 수 있습니다.

SELECT 
			column1
		, column2
FROM Table1
WHERE (column1, column2) = (
														SELECT 
																	sub_column1
															  , sub_column2 
											      FROM sub_table
											      WHERE condition
														);

1. 열 서브쿼리 (Column Subquery):
결과로 여러 값을 포함하는 단일 열을 반환합니다. IN, NOT IN등의 연산자와 함께 WHERE절에서 사용될 수 있습니다.

SELECT 
			column1
		, column2
FROM Table1
WHERE column1 IN (
								  SELECT sub_column 
					        FROM sub_table
					        WHERE condition
								  );

1. 테이블 서브쿼리 (Table Subquery):
결과로 여러 행과 열을 반환합니다. 주로 FROM 절에서 사용되며, 임시 테이블처럼 작동하여 JOIN연산이나 인라인 뷰로 사용할 때 유용합니다.

SELECT 
			column1
		, column2
FROM (
			SELECT 
						sub_column1
					, sub_column2
      FROM sub_table
      WHERE condition
			) AS alias;

10.2.1. 스칼라

SELECT
	    column1
	  , column2
    , (
			 SELECT 
						 single_value 
			 FROM sub_table 
			 WHERE conditions
		   ) AS scalar_sub_column
FROM main_table
WHERE main_table_conditions;

• main_table : 메인쿼리에서 사용하는 메인테이블입니다.

• sub_table : 스칼라 서브쿼리에서 사용하는 서브쿼리 테이블입니다.

• column1, column2 : 메인쿼리에서 선택한 열입니다.

• conditions : 메인쿼리 및 스칼라 서브쿼리의 조건을 지정하는 부분입니다.

• scalar_sub_column : 스칼라 서브쿼리가 반환한 단일 값을 저장하는 열입니다.

SELECT
		  e.employee_id
		, e.department_id
	  , e.first_name || ' ' || e.last_name AS full_name
		, e.salary
		, (
		    SELECT 
							MAX(salary)
		    FROM `HR.employees` as ms
		    WHERE department_id = e.department_id
		    ORDER BY e.salary DESC
		  ) AS max_salary_for_department
FROM `HR.employees` AS e
ORDER BY department_id;

WITH MaxSalaries AS (
  SELECT
				department_id
			, MAX(salary) AS max_salary
  FROM `HR.employees`
  GROUP BY department_id
)
SELECT
		  e.employee_id
		, e.department_id
		, e.first_name || ' ' || e.last_name AS full_name
		, e.salary
		, ms.max_salary AS max_salary_for_department
FROM `HR.employees` AS e
LEFT JOIN MaxSalaries AS ms
ON e.department_id = ms.department_id
ORDER BY e.department_id;

SELECT
	    employee_id
	  , first_name || ' ' || last_name AS full_name
	  , (
       SELECT job_title
       FROM `HR.jobs` AS j
       WHERE j.job_id = e.job_id
		   ) AS job_title
FROM `HR.employees` AS e
WHERE 1=1
		,	employee_id = 102;

WITH EmployeeJob AS (
  SELECT
		    e.employee_id
		  , e.first_name || ' ' || e.last_name AS full_name
		  , j.job_title
  FROM `HR.employees` AS e
  JOIN `HR.jobs` AS j
  ON e.job_id = j.job_id
)
SELECT
			employee_id
		, full_name
		, job_title
FROM EmployeeJob
WHERE 1=1
		, employee_id = 102;

10.2.2 인라인 뷰

1. 임시 테이블처럼 작동: 인라인 뷰는 쿼리 실행 시 임시적으로 생성되며, 쿼리 실행이 완료되면 사라집니다.

2. 복잡한 쿼리의 단순화: 인라인 뷰를 사용하면 복잡한 조인 조건이나 필터링 로직을 서브쿼리로 분리하여 주 쿼리를 간결하게 만들 수 있습니다.

3. 다중 조인의 중간 단계: 여러 테이블을 조인할 때, 인라인 뷰를 사용하여 중간 결과 집합을 생성한 결과를 대상으로 추가 작업을 수행할 수 있습니다.

4. 중간 결과 생성: 복잡한 쿼리를 분리하여 중간 단계의 결과를 생성하고, 이를 기반으로 최종 결과를 얻을 수 있습니다.

SELECT
	    column1
	  , column2
FROM (
	    SELECT
		        sub_column1
		      , sub_column2
	    FROM sub_table
	    WHERE conditions
			) AS inlineview_sub_column
WHERE main_table_conditions;

• inlineview_sub_column: 인라인 뷰의 별칭으로, 인라인 뷰의 결과를 참조할 때 사용됩니다.

• column1, column2: 메인쿼리에서 선택하려는 메인 테이블의 열입니다.

• sub_column1, sub_column2: 인라인 뷰 내에서 선택하려는 “Table"의 열입니다.

• sub_table: 인라인 뷰 내부의 서브쿼리에서 참조하는 테이블입니다.

• conditions: 인라인 뷰 내의 서브쿼리에서 데이터를 필터링하기 위해 사용되는 조건들입니다.

• main_table_conditions: 인라인 뷰 외부의 메인쿼리에서 데이터를 필터링하기 위해 사용되는 조건들입니다.

SELECT 
			e.first_name
		, e.salary 
FROM `HR.employees` AS e 
     (
			SELECT AVG(salary) AS avg_salary 
			FROM `HR.employees`
			) AS avgs 
WHERE e.salary > avgs.avg_salary;

WITH AverageSalary AS (
	SELECT AVG(salary) AS avg_salary 
  FROM `HR.employees`
)

SELECT 
      e.first_name
    , e.salary 
FROM `HR.employees` AS e
JOIN AverageSalary avgs 
ON e.salary > avgs.avg_salary;

 

SELECT 
			e.first_name
		, e.department_id
	  , e.salary 
FROM `HR.employees` AS e 
JOIN (
			SELECT 
						department_id
					, MAX(salary) AS max_salary 
		  FROM `HR.employees`
			GROUP BY department_id
			) AS d 
ON e.department_id = d.department_id AND e.salary = d.max_salary;

WITH DepartmentMaxSalaries AS (
	SELECT
				department_id
		  , MAX(salary) AS max_salary
	FROM `HR.employees`
	GROUP BY department_id
)

SELECT 
	    e.first_name
	  , e.department_id
	  , e.salary 
FROM `HR.employees` AS e 
JOIN DepartmentMaxSalaries dms 
ON e.department_id = dms.department_id 
AND e.salary = dms.max_salary;

SELECT 
			e.first_name
		, e.job_id
		, e.salary
		, iv.avg_salary 
FROM `HR.employees` AS e 
JOIN (
			SELECT 
						job_id
					, AVG(salary) AS avg_salary 
			FROM `HR.employees` 
			GROUP BY job_id
			) AS iv 
ON e.job_id = iv.job_id 
ORDER BY ABS(e.salary - iv.avg_salary) DESC;

WITH JobAverageSalaries AS (
	SELECT
				job_id
			,	AVG(salary) AS avg_salary 
  FROM `HR.employees` 
  GROUP BY job_id
)

SELECT 
			e.first_name
		, e.job_id
		, e.salary
		, jas.avg_salary 
FROM `HR.employees` AS e 
JOIN JobAverageSalaries AS jas 
ON e.job_id = jas.job_id 
ORDER BY ABS(e.salary - jas.avg_salary) DESC;

10.2.3. 중첩

SELECT 
			main_column1
FROM main_table
WHERE main_column2 operator (
														 SELECT sub_column 
														 FROM sub_table 
														 WHERE sub_condition
														 );

• main_column1: 외부 쿼리에서 선택할 주요 열을 지정합니다.

• main_table: 외부 쿼리의 주 테이블을 지정합니다.

• main_column2: 외부 쿼리에서 조건으로 사용할 주요 열을 지정합니다.

• operator: 비교 연산자(예: =, >, <, IN 등)를 지정합니다.

• sub_column: 서브쿼리에서 선택할 열을 지정합니다.

• sub_table: 서브쿼리의 테이블을 지정합니다.

• sub_condition: 서브쿼리에서 사용되는 조건을 지정합니다.

SELECT 
			employee_id 
		,	first_name 
		,	last_name 
		,	hire_date 
		,	salary
FROM `HR.employees`
WHERE salary > (
                SELECT AVG(salary)
                FROM `HR.employees`
                WHERE hire_date > '2005-01-01'
                );

SELECT 
			department_id
		,	employee_id
FROM `HR.employees`
WHERE department_id NOT IN (
                            SELECT department_id
                            FROM `HR.departments`
                            WHERE manager_id IS NULL
                            );

SELECT 
			employee_id
		, salary
FROM `HR.employees`
WHERE salary > (
							  SELECT percentile_cont(salary, 0.75) OVER()
							  FROM `HR.employees`
							  LIMIT 1
								)
ORDER BY salary DESC;

10.2.4. IN

value [ NOT ] IN ( subquery )

SELECT
		  employee_id
		, first_name || ' ' || last_name AS full_name
FROM `HR.employees`
WHERE 1=1
	  , job_id IN ( 
								 SELECT job_id
							   FROM `HR.jobs`
							   WHERE 1=1
										 , job_title IN ('Sales Representative', 'Marketing Manager')
								 );

WITH SelectedJobs AS (
	SELECT job_id
	FROM `HR.jobs`
	WHERE job_title IN ('Sales Representative', 'Marketing Manager')
)

SELECT
		  e.employee_id
		, e.first_name || ' ' || e.last_name AS full_name
FROM `HR.employees` AS e
WHERE 1=1
		, e.job_id IN (
									 SELECT job_id 
									 FROM SelectedJobs
									 );

SELECT
		  employee_id,
		, first_name || ' ' || last_name AS full_name
		, department_id
FROM `HR.employees`
WHERE department_id NOT IN (
												    SELECT department_id
												    FROM `HR.departments`
												    WHERE 1=1
																,	department_name IN ('Sales', 'Marketing')
													  );

WITH ExcludedDepartments AS (
	SELECT department_id
	FROM `HR.departments`
	WHERE department_name IN ('Sales', 'Marketing')
)

SELECT
		  e.employee_id
		, e.first_name || ' ' || e.last_name AS full_name
		, e.department_id
FROM `HR.employees` AS e
WHERE 1=1
		, e.department_id NOT IN (
															SELECT department_id
															FROM ExcludedDepartments
															);

10.2.5. EXISTS

SELECT EXISTS (
							 SELECT 'found'
							 FROM sub_table
							 WHERE condition
							 ) AS exists_result;

SELECT
		  column1
		, column2
FROM main_table
WHERE EXISTS (
							SELECT 'found'
							FROM sub_table
							WHERE condition
							);

• column1, column2: 반환할 열을 지정합니다.

• main_table: 데이터를 조회할 기본 테이블을 지정합니다.

• sub_table: 서브쿼리에서 사용할 두 번째 테이블을 지정합니다.

• found: 서브쿼리에서 사용된 값으로, 실제로는 항상 참(TRUE)으로 평가되는 임의의 값입니다. 이 값은 EXISTS 서브쿼리에서 조건이 참인지 여부를 확인하는 데 사용됩니다.

• condition: 서브쿼리에서 사용할 조건을 지정합니다.

SELECT
		  employee_id
	  , job_id
    , department_id
FROM `HR.employees`
WHERE EXISTS (
							SELECT 1
							FROM `HR.departments`
							WHERE department_id = 50
							)

10.2.6. 상관

SELECT
			m.column1
	  , m.column2
		 (
			SELECT 
						s.value
	    FROM sub_table s
	    WHERE s.id = m.id
			) AS subquery_result
FROM main_table AS m;

SELECT 
			m.column1
		,	m.column2
FROM main_table m
WHERE operator (
								SELECT 
											s.value 
								FROM sub_table s 
								WHERE m.id = s.id
								);

• main_table (m): 데이터를 조회할 기본 테이블을 지정합니다.

• sub_table (s): 서브쿼리에서 사용할 두 번째 테이블을 지정합니다.

• m.id: 메인 테이블 “main_table”의 id 열을 나타냅니다.

• s.id: 서브 테이블 “sub_table”의 id 열을 나타냅니다. 이 열은 외부 쿼리의 메인 테이블과 서브쿼리의 서브 테이블을 연결하는 데 사용됩니다.

• operator: 비교 연산자(예: =, >, <, IN 등)를 지정합니다.

SELECT 
			e.first_name
    , e.last_name
		, e.employee_id
FROM `HR.employees` AS e
WHERE EXISTS (
              SELECT 
										employee_id
              FROM `HR.job_history` AS j
              WHERE j.employee_id = e.employee_id
              );

10.2.7. ARRAY

ARRAY ( subquery )

SELECT
      ARRAY(
						SELECT 
                  first_name 
            FROM `HR.employees` 
            WHERE job_id='SA_MAN'
						) AS SA_MAN;

WITH SaManEmployees AS (
	SELECT 
			  ARRAY_AGG(first_name) AS SA_MAN
	FROM `HR.employees`
	WHERE job_id = 'SA_MAN'
)

SELECT SA_MAN
FROM SaManEmployees;

SELECT 
			department_id
		,	ARRAY_AGG(first_name) AS employees_name
FROM `HR.employees`
GROUP BY department_id;

WITH DepartmentEmployees AS (
	SELECT 
				department_id
			, ARRAY_AGG(first_name) AS employees_name
  FROM `HR.employees`
  GROUP BY department_id
)

SELECT 
			department_id
		, employees_name
FROM DepartmentEmployee;