DB/SQL

[SQL] 서브쿼리(Subquery) & 뷰(VIEW)정리

h_sseul 2023. 4. 4. 22:53

서브쿼리(subquery)

: 다른 쿼리 내부에 포함되어 있는 SELECT 문

  • 서브쿼리(= 자식쿼리, 내부쿼리) : 메인쿼리 컬럼 사용 가능
  • 메인쿼리(=부모쿼리, 외부쿼리) : 서브쿼리 컬럼 사용 불가

* 서브쿼리에서는 order by절 사용 불가능

 

서브쿼리 종류

1. 스칼라 서브쿼리(Scalar Subquery)

- SELECT 절에 사용되는 서브쿼리

- 다른 테이블에서 어떠한 값을 가져올 때 사용됨

- 하나의 레코드만(단일 행, 단일 컬럼) 리턴이 가능하며, 두 개 이상의 레코드는 리턴 불가능

- 일치하는 데이터가 없어도 오류가 나지 않고 NULL 값을 리턴한다.

- 데이터 건수가 적은 경우, JOIN보다 유리하다.

 

 

2. 인라인 뷰(Inline View)

- FROM 절에 사용되는 서브쿼리 + JOIN과 함께 사용

- 인라인 뷰는 하나의 임시 테이블이라고 생각하기 (뷰 형태)

CREATE VIEW 명령어로 만들지 않고 SQL문의 해당 라인(FROM 절)에 직접 기술한다고 해서 인라인 뷰(InLine View)라고 하는 것. SQL문 라인에 직접 기술하니 필요한 시점에만 사용되는 특징

- 서브쿼리가 FROM 절에 사용될 경우(인라인 뷰), 무조건 Alias을 지정해주어야 한다.

- 메인쿼리에서는 인라인 뷰에서 select 한 컬럼만 사용 가능하다.

 

select e.*, d.dept_name
from employee e,
	(select dept_id, dept_name
	from departments) d # 인라인 뷰
where e.dept_id = d.dept_id;

 

사용 이유

- 서브쿼리와 테이블 조인이 이루어질 경우 검색하는 필드명은 '테이블.필드명' 과 같이 길어지게 된다. 서브쿼리를 통해 메인쿼리로 올라갈수록 쿼리의 길이가 점점 더 길어질 뿐만 아니라 쿼리의 가독성이 떨어진다. 이때, FROM절에서 사용하는 인라인 뷰에 별칭(Alias)를 줘서 간단하게 만들 수 있다. 

- 전체 테이블을 비교하는 것보다 테이블의 일부 데이터만 불러와 그 중에서 조건을 따지는 것이 비교하는 횟수가 적다.

 

 

3. 일반 서브쿼리

- WHERE 절에서 사용되는 쿼리

3-1. 서브쿼리의 결과가 단일 행이면 단일 행 서브쿼리(Single Row Subquery)

3-2. 서브쿼리의 결과가 다중 행이면 다중 행 서브쿼리(Multi Row Subquery)

3-3. 서브쿼리의 결과가 다중 컬럼이면 다중 컬럼 서브쿼리(Multi Column Subquery)

 * 단일 행 비교 연산자 : =, <, >, <=, >=, <>

 * 다중 행 비교 연산자 : IN, ANY, EXISTS, ALL

 

 

서브쿼리 사용 가능한 곳

  • SELECT
  • FROM
  • WHERE
  • HAVING
  • ORDER BY
  • INSERT문의 VALUES 절
  • UPDATE문의 SET 절

 

 

뷰(VIEW)

사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블이나 다른 뷰를 이용해 생성되는 가상 테이블

 - 기본 테이블 : 디스크에 공간이 할당되어 데이터를 저장함. 즉, 실제 데이터가 저장된다.

 - 뷰 : 데이터 딕셔너리(Data Dictionary) 테이블에 뷰에 대한 정의(SQL문)만 저장되어 디스크 저장 공간 할당이 이루어지지 않음. 즉, 저장장치 내에 물리적으로 데이터가 존재하지 않지만 사용자에게 있는 것처럼 간주된다.

 

특징

- 필요한 데이터만 뷰로 정의해 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해진다.

- 뷰를 통해서만 데이터에 접근하게 하면 뷰에 나타나지 않은 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있다.

- 데이터베이스는 유저가 쿼리할 때마다 뷰를 매번 recreate 하기 때문에 뷰는 항상 최신의 데이터를 보여준다.

 

사용 이유

뷰는 접근 단순화 및 보안을 높이는 데 필요하기 때문이다.

- 여러 테이블을 조인해 길고 복잡한 쿼리문을 통해 데이터를 조회하는 경우, 해당 쿼리문을 매번 작성하는 일은 번거로운 일. 뷰 테이블을 이용하면 해당 쿼리문을 뷰 테이블로 만들어 놓고 뷰를 하나의 테이블인 것처럼 조회할 수 있어 편리하다.

- 동일한 테이블에 접근하는 여러 다른 사용자들이 있다면, 사용자들마다 서로 다른 뷰에 접근하도록 여러 뷰를 정의할 수 있다. 이를 통해 특정 사용자만 특정 뷰에 접근할 수 있도록 접근을 제한함으로써 보안을 높이는 것이 가능하다.

 

제약사항

단순 뷰는 INSERT, UPDATE, DELETE를 자유롭게 사용할 수 있다. 하지만 함수, UNION, GROUP BY 등을 사용한 복잡한 뷰인 경우 해당 명령문들을 사용하는 것이 불가능하다.

 

뷰 문법

 

CREATE VIEW 뷰 이름 AS 
SELECT COL1, COL2, ...
FROM 테이블명 -- 여러 개의 테이블을 별칭과 함께 작성해 사용 가능
WHERE 조건;

-- WITH절과 동일하게 위에서 한 번 생성해두면 아래에서 조회 및 가능
SELECT * 
FROM 뷰 이름;