SQL - 단일행/복수행 서브쿼리

-- < 단일행 서브쿼리 > : 서브쿼리가 실행되어 반드시 한 개의 행을 반환하는 서브쿼리 --

1. 비교 연산자


-- < 복수행 서브쿼리 > : 서브쿼리가 실행되어 반환되는 결과가 하나 이상의 행일 때 사용하는 서브쿼리 --
-- 1. IN 연산자: 서브쿼리 반환값이 복수이고 메인 쿼리와 동등 연산자(=) 방식으로 비교할 때                   

SELECT last_name, salary
FROM employees
WHERE salary IN ( SELECT salary
                  FROM employees
                  WHERE last_name IN ('Whalen','Fay') );

                  
                  
 
 -- 2. 다중 연산자 > ALL : 최대값보다 큰 값을 조회 ( 최대 월급보다 많은 )

SELECT last_name, department_id, salary
FROM employees
WHERE salary > ALL (SELECT salary
               FROM employees
               WHERE job_id = 'IT_PROG');

SELECT last_name, department_id, salary
FROM employees
WHERE salary > (SELECT max(salary)
               FROM employees
               WHERE job_id = 'IT_PROG');

               

            
-- 2. 다중 연산자 < all : 최소값보다 작은 값을 조회 ( 최소 월급보다 작은 )                 

SELECT last_name, department_id, salary
FROM employees
WHERE salary < ALL (SELECT salary
                     FROM employees
                      WHERE job_id = 'IT_PROG');

 

                      
 
-- 3. ANY 연산자 : 서브쿼리에서 반환되는 행들 전체에 대해 조건이 하나 이상만 만족하면 됨
-- > ANY (서브쿼리) : (서브쿼리에서 반환된) 최소값보다 큰 데이터를 조회

SELECT last_name, department_id, salary
FROM employees
WHERE salary > ANY (SELECT salary
                     FROM employees
                     WHERE job_id = 'IT_PROG');

SELECT last_name, department_id, salary
FROM employees
WHERE salary > (SELECT max (salary)
                     FROM employees
                     WHERE job_id = 'IT_PROG');

 

                  
-- < ANY (서브쿼리) : (서브쿼리에서 반환된) 최대값보다 작은 데이터를 조회   

SELECT last_name, department_id, salary
FROM employees
WHERE salary < ANY (SELECT salary
                     FROM employees
                     WHERE job_id = 'IT_PROG');

                     

 

                     
 -- 4. EXISTS 연산자 : 서브쿼리에서 실행된 결과가 하나라도 존재 하는지 여부를 확인할 때 사용
 -- 서브쿼리에서 검색된 결과가 하나라도 있으면 메인 쿼리에 전달된 값이 true이기 때문에 메인쿼리가 실행되어
 -- 검색 결과가 출력된다.
 -- 사원들 중에서 커미션을 받는 사원이 한 명이라도 있으면 모든 사원 정보를 출력

SELECT last_name, department_id, salary
FROM employees
WHERE EXISTS (SELECT employee_id
              FROM employees
              WHERE commission_pct IS NOT NULL);