데이터베이스 4일차 - 외부·교차 조인, 서브쿼리, 집합연산자

외부 조인(Outer Join)

: 조인 조건에 일치하지 않는 데이터까지 모두 출력해주는 조인

--LEFT
SELECT e.last_name 직원, e.department_id,d.department_name
FROM employees e
LEFT OUTER JOIN departments d
    ON e.department_id = d.department_id;
--왼쪽에 있는 모든 데이터를 출력하므로 부서가 없는 직원도 출력

--FULL
SELECT e.last_name 직원, e.department_id,d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;
--왼쪽, 오른쪽에서 출력된 부서가 없는 직원, 직원이 없는 부서 모두 출력

교차조인(cross join)

: 한 쪽 테이블의 모든 행과 다른 테이블의 모든 행을 연결시킨다.

 따라서 교차조인의 결과는 두 테이블의 행의 개수를 곱한 개수가 됨.(Cartesian Product)

 

서브쿼리(Sub Query)

: 하나의 SQL문에 포함된 다른 SQL문으로 메인 쿼리 보다 먼저 실행되며, 여러 번 SELECT문을 실행하지 않고 하나의 중첩된 SELECT 문으로 쉽게 얻을 수 있다.

 단일 행 서브 쿼리

SELECT employee_id, last_name,salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE LAST_NAME = 'Abel');
--서브쿼리 결과만 보고싶을 땐 서브쿼리만 블록지정하여 실행

employee 테이블에서 이름이 Abel인 직원의 월급보다 월급을 많이 받는 사람 출력

 

  그룹함수를 서브쿼리로 이용

-- 그룹함수를 서브쿼리로 이용
-- 그룹함수는 가장 높은 급여를 받는 사람의 이름을 알 수 없음.
SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

원래는 where절에 그룹함수를 사용할 수 없지만 서브쿼리를 이용하여 사용

*그룹의 조건절인 HAVING절에도 사용가능

 

단일 행 서브쿼리 예제 ) jobs 테이블에서 job_title이 'Stock Manager'의 job_id를 가진 직원들 출력

--예제3****
SELECT *
FROM employees e
JOIN jobs j
    ON e.job_id = j.job_id
WHERE e.job_id = (SELECT job_id FROM jobs WHERE job_title = 'Stock Manager');
-- 서브쿼리에 j.job_id 형식으로 하니까 오류발생

 

 

다중 행 서브쿼리

예제1) 부서번호가 20번인 직원들의 매니저와 매니저가 같은 직원을 출력하라(20번 부서 직원 제외)

--예제 1)
SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE manager_id IN(SELECT manager_id FROM employees WHERE department_id = 20)
AND department_id != 20;

 

예제2) job_id가 'ST_MAN'인 직원들 중 어느 한 직원 보다 급여가 작으면 그 직원을 출력하라.

--예제 2)
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY(SELECT salary FROM employees WHERE job_id = 'ST_MAN');

 

예제3) 직책이 'IT_PROG'인 직원들 보다 급여가 작은 직원들을 출력하시오.

--예제 3)
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL(SELECT salary FROM employees WHERE job_id = 'IT_PROG');

 

다중 열 서브 쿼리

: 여러 개의 컬럼을 검색하는 서브 쿼리.

서브 쿼리의 SELECT문에 여러 개의 컬럼을 검색하며, 1:1 대응돼야 한다.

 

예제1) employees 테이블에서 job_id 별로 가장 낮은 salary를 찾고, job_id 별 salary에 해당하는 직원의 first_name, job_id, salary, department_id를 출력하라.(salary 내림차순)

--예제
SELECT first_name, job_id, salary, department_id
FROM employees
WHERE (JOB_ID, SALARY) IN (SELECT JOB_ID, MIN(SALARY)
                            FROM employees
                            GROUP BY job_id)
ORDER BY salary DESC;

집합연산자 (UNION, UNION ALL, INTERSECT, MINUS)

예제) employees 테이블의 부서번호와 departments 테이블의 부서번호 집합을 UNION 연산자를 이용해 합쳐라.

--예제1)
SELECT department_id
FROM employees
UNION
SELECT department_id
FROM departments;