サブクエリとは

サブクエリのタイプ

1.スカラー量子クエリー-selectセクション

-- example #1
# 부서명 가져오기
  SELECT a.employee_id
          , a.first_name || ' ' || a.last_name
          , a.department_id
          ,(SELECT b.department_name # 컬럼 1개만 가져올 수 있음 or 문자열 연결 연산자로 결합되어 최종 반환값 1개가 되게 한다.
          FROM departments b 
          WHERE a.department_id = b.department_id) as dept_name
  FROM employees a 
  ORDER BY 1 ;

-- example #2
# 부서명, 나라이름 가져오기
SELECT a.employee_id
        , a.first_name || ' ' || a.last_name
        , a.department_id
        ,(SELECT b.department_name
         FROM departments b 
         WHERE a.department_id = b.department_id) as dept_name -- 부서명
         ,(SELECT d.country_name
    	FROM departments b
        , locations c 
        , countries d
        WHERE a.department_id = b.department_id 
        AND b.location_id = c.location_id
        AND c.country_id = d.country_id) as country_name -- 나라이름
FROM employees a 
ORDER BY 1 ;

2.オンラインビュー-Fromセクション

# 1. 부서별 평균 급여를 서브쿼리에서 구한 뒤
# 2. 사원급여와 부서 평균 급여를 같이 조회
SELECT a.department_id
	, a.employee_id
	, a.first_name || ' ' || a.last_name as emp_name
    , a.salary
    , b.avg_salary
FROM employees a 
JOIN (SELECT department_id
			, avg(salary) as avg_salary)
     FROM employees 
     GROUP BY department_id) b 
  ON a.department_id = b.department_id
ORDER BY 1;

3.ネストされたクエリー-Whereセクション

-- example #1
# 1. employees 테이블에 있는 department_id 조회
# 2. departments 테이블에서 이 서브쿼리에서 반환하는 값이 포함되는 건만 조회 

SELECT *
FROM departments
WHERE department_id in (SELECT departmnet_id FROM employees);

-- example #2
# 1. job_id, salary 두 값을 동시에 비교
# 2. job_id별 최소 급여를 받는 사원이 조회됨

SELECT employee_id 
	, first_name || ' ' || last_name as emp_name
    , job_id
    , salary
WHERE (job_id,salary) IN (SELECT job_id, min_salary FROM jobs)
ORDER BY 1;

-- example #3
# 1. employees 테이블에서 자신이 속한 부서의 평균 급여보다 많이 받는 사원 조회

SELECT employee_id
	, department_id
	, first_name || ' ' || last_name as emp_name
    , salary
FROM employees a 
WHERE salary > (SELECT AGV(salary) as avg_salary 
				FROM employees b
                WHERE a.department_id = b.department_id) -- 조인에 의해 group by 효과가 나타남

4.Withセクション

セクション

-- with 별칭 as 다음에 서브쿼리 형태
WITH -- with는 한번만 명시, 서브쿼리는 여러 개 사용 가능
temp1 as (
SELECT a.department_id, a.department_name
    , b.location_id, b.street_address, b.city, b.country_id
FROM departments a 
JOIN locations b
  on a.location_id = b.location_id
),
temp2 as (
SELECT b.department_id, b.department_name, b.street_address, b.city
	, a.country_name 
FROM countries a 
JOIN temp1 b -- 서브쿼리 내에서 다른 서브쿼리 참조 가능
  on a.country_id = b.country_id
)
-- 메인 쿼리 
SELECT a.employee_id
	, a.first_name || ' ' || a.last_name as emp_name
    , b.department_name
    , b.street_address
    , b.country_name 
FROM employees a 
JOIN temp2 b -- 메인 쿼리에서는 여러개의 서브쿼리 조인해 결과 조회 가능 
  on a.department_id = b.department_id
ORDER BY 1;