SQL 심화 1 (서브쿼리, 뷰, 집합연산자)

서브쿼리란

하나의 쿼리 안에 존재하는 또 다른 쿼리를 말한다. 

서브쿼리는 위치에 따라 3가지로 나눌수 있다.

위치	이름	설명
SELECT 절	스칼라 서브쿼리(Scalar Subquery)	컬럼이 올수 있는 대부분의 위치에 사용할수 있다. 컬럼 대신 사용하므로 반드시 하나의 값만을 반환해야 한다.
FROM 절	인라인 뷰(Inline View)	테이블 명이 올수 있는 위치에 사용 가능하다.
WHERE 절, HAVING 절	중첩 서브쿼리(Nested Subquery)	WHERE 설과 HAVING 절에 사용할 수 있다. 비연관 서브쿼리와 연관 서브쿼리로 나뉜다.

 

중첩서브쿼리는 서브쿼리내에 메인쿼리의 유무에 따라서 비연관 서브쿼리와 연관 서브쿼리로 나뉜다.

이름	설명	상세
비연관 서브쿼리(Uncorrelated Subquery)	메인쿼리와 관계를 맺고 있지 않음	서브쿼리 내에 메인쿼리의 칼럼이 없음
연관 서브쿼리(Correlated Subquery)	메인쿼리와 관계를 맺고 있음	서브쿼리 내에 메인쿼리의 칼럼이 존재

 

중첩서브쿼리는 반환하는 데이터에 따라서 단일 행, 다중 행, 다중 컬럼 서브쿼리로 나눌 수 있다.

단일 행(Single Row) 서브쿼리	서브쿼리가 1건 이하인 데이터를 반환 단일 행 비교 연산자와 함께 사용 ex) =, <, >, <=, >=, <>	항상 1건 이하의 결과만 반환한다
다중 행(Multi Row) 서브쿼리	서브쿼리가 여러 건의 데이터를 반환 다중 행 비교 연산자와 함께 사용 ex) IN, ALL, ANY, SOME, EXISTS	2건 이상의 행을 반환한다
다중 컬럼(Multi Column) 서브쿼리	서브쿼리가 여러 컬럼의 데이터를 반환	서브쿼리가 가지는 칼럼의 2개이상이다

 

뷰(View)

뷰는 특정 SELECT 문에 이름을 붙여서 재사용이 가능하도록한 임의의 테이블이다. 

뷰를 생성하는 구문

CREATE VIEW 뷰이름 AS
SELECT 열1, 열2, ...
FROM 테이블이름
WHERE 조건;

CREATE VIEW employee_view AS
SELECT employee_id, first_name, department_id
FROM employees;

CREATE VIEW high_salary_employees AS
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 5000;

CREATE VIEW department_summary (dept_id, total_salary) AS
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;

 

뷰를 새로 생성할때 기존에 같은 이름을 가진 뷰가 있을때

CREATE OR REPLACE VIEW employee_view AS
SELECT employee_id, last_name, department_id
FROM employees;

 

뷰를 삭제하는방법

DROP VIEW DEPT_MEMBER;

 

뷰의 특징

보안성	보안이 필요한 컬럼을 가진 테이블일 경우 해당 컬럼을 제외한 별도의 뷰를 생성하여 제공함으로써 보안을 유지할 수 있다.
독립성	테이블 스키마가 변경되었을 경우 애플리케이션은 변경하지 않고 관련 뷰만 수정할수 있다.
편리성	복잡한 쿼리 구문을 뷰명으로 단축시킴으로써 가독성을 높이고 편리하게 사용할 수 있다.

 

집합 연산자 설명

UNION ALL	각 쿼리의 결과 집합의 합집합이다. 중복된 행도 그대로 출력된다.
UNION	각 쿼리의 결과 집합의 합집합이다. 중복된 행은 한 줄로 출력된다.
INTERSECT	각 쿼리의 결과 집합의 교집합이다. 중복된 행은 한 줄로 출력된다.
MINUS/EXCEPT	앞에있는 쿼리의 결과 집합에서 뒤에 있는 쿼리의 결과집합을 뺀 차집합이다. 중복된 행은 한줄로 출력된다.

 

SELECT 컬럼1, 컬럼2
FROM 테이블1
UNION
SELECT 컬럼1, 컬럼2
FROM 테이블2;

SELECT name FROM employees
UNION
SELECT name FROM managers;


SELECT 컬럼1, 컬럼2
FROM 테이블1
UNION ALL
SELECT 컬럼1, 컬럼2
FROM 테이블2;

SELECT name FROM employees
UNION ALL
SELECT name FROM managers;


SELECT 컬럼1, 컬럼2
FROM 테이블1
INTERSECT
SELECT 컬럼1, 컬럼2
FROM 테이블2;

SELECT id, name FROM employees
INTERSECT
SELECT id, name FROM managers;


SELECT 컬럼1, 컬럼2
FROM 테이블1
MINUS
SELECT 컬럼1, 컬럼2
FROM 테이블2;

SELECT id, name FROM employees
MINUS 
SELECT id, name FROM managers;