데이터베이스 (서브쿼리)

2023.07.17 수업(script 22 - 서브쿼리)

 

◎ 서브쿼리

- 메인 쿼리 내부에 작성하는 쿼리

- 질의 쿼리 보조 또는 하위 쿼리로 추가하여 실행되는 쿼리
- SELECT문장 안에 또 다른 보조 SELECT를 가진 경우
- 서브쿼리의 SELECT문자열은 주로 '()'로 둘러쌓여있음
- 실행결과를 반환할 수도 있고, FROM절에  쓰여 테이블 대신으로 사용할 수 있다
- WHERE절이 조건으로도 사용할 수 있다

- 메인쿼리와 서브쿼리가 연관성이 있는 경우(주로 JOIN)와 연관성이 없는 경우로 나뉨

 

※ 크게 3가지 서브 쿼리로 분류
1. 스칼라 서브쿼리 
- select절에서 사용. 단일컬럼, 단일 행을 반환(1개의 값)
2. 인라인 뷰
- from절에서 사용. 임시 뷰, 임시 테이블
3. 중첩 서브쿼리
- where절, having절에서 사용. 다중 컬럼, 다중 행을 반환

- 일반 서브쿼리라고도 함. 가장 많이 사용됨

 

* 단일행 비교연산자 ( >, >=, <, <=, =, ... )
* 다중행 비교연산자 ( IN, ANY, SOME, ALL, EXISTS )

---

▷ 1. 스칼라 서브쿼리 

- SELECT 문에 나타나는 서브쿼리
- 서브쿼리의 결과가 메인쿼리의 컬럼이나 표현식으로 사용되는 경우
- 서브쿼리 SELECT절에서 단 하나의 컬럼이나 표현식만 가지는 경우 컬럼이나 표현식은 결합연산자( + 또는 ||)로 연결해 하나의 컬럼으로 사용 가능
- 결과 값이 하나의 값을 반환해야 한다(한 행, 한 컬럼만을 반환하는 서브쿼리)
- 스칼라쿼리 안의 컬럼수가 둘 이상의 독립적인 컬럼이거나 레코드수가 2개 이상이라면 스칼라쿼리가 아니다(오류)

 

[구문]

SELECT 
      컬럼리스트
      (SELECT
         서브쿼리 컬럼리스트
         FROM 
      	    서브쿼리 테이블
         WHERE 서브쿼리 조건절
         ) as 별칭
   FROM 
      테이블;

ex) 사원테이블 전체를, 부서테이블의 NAME컬럼(where절을 만족하는)을 가져오기

'왕건' 사원의 부서컬럼처럼 데이터가 없는 경우 NULL을 반환

사원정보 테이블에 부서코드를 알고 있으므로 부서이름 추가하기

SELECT 
	te.*,
	(SELECT td.DEPT_NAME 
		FROM TBL_DEPT td
		WHERE te.DEPT_ID = td.DEPT_ID) DEPT_NAME
	FROM 
		TBL_EMPLOYEE te;

* LEFT JOIN과 유사

-- 1. 오라클
SELECT 
	te.*, td.*
	FROM
		TBL_EMPLOYEE te,
		TBL_DEPT td
	WHERE 
		te.DEPT_ID = td.DEPT_ID (+)
	ORDER BY 
		te.EMP_ID;
-- 2. ANSI
SELECT 
	te.*, td.*
	FROM
		TBL_EMPLOYEE te
			LEFT JOIN
		TBL_DEPT td
		ON 
			te.DEPT_ID = td.DEPT_ID 
	ORDER BY 
		te.EMP_ID;

---

▷ 2. 인라인 뷰
- FROM 문에 나타나는 서브쿼리
- SELECT ~ FROM 구문에서 테이블 대신으로 사용할 수 있는 서브쿼리
- MAIN쿼리의 FROM절에서 사용하는 서브쿼리
- FROM절에 테이블 대신 쿼리결과가 대신 실행한다 
- 스칼라쿼리와 다르게 여러컬럼 여러 레코드를 반환할 수 있다

- 하나의 테이블처럼 사용된다

- 열 이름과 테이블명을 필수로 명시해주어야함(서브쿼리 마지막에 AS 별칭 사용)
[구문]

SELECT 
      컬럼리스트
   FROM 
      테이블 또는
      (SELECT
         서브쿼리 컬럼리스트
         FROM
            서브쿼리 테이블
         WHERE 
            서브쿼리 조건절) as 별칭;

ex) 현재부서가 활성화(부서테이블의 use_Y = 'Y'인 경우)결과를 출력

사원-주소정보를 조인한 테이블과 부서테이블을 조인하여 부서-사원-주소 테이블

* || : 문자열 결합연산자

SELECT
	td.DEPT_ID, td.DEPT_NAME,
	ea.emp_id, ea.emp_name, ea.address
	FROM 
		TBL_DEPT td,
		-- 사원-주소 정보테이블
		-- 인라인 - 뷰
		(SELECT 
			te.EMP_ID, te.EMP_NAME, te.dept_id,
			ta.CITY || ta.GU || ta.ADDRESS_NAME address
			FROM 
				TBL_EMPLOYEE te,
				TBL_ADDRESS ta
			WHERE 
				te.ADDRESS_ID = ta.ADDRESS_ID) ea
	WHERE 
		td.USE_YN = 'Y' AND
		td.DEPT_ID = ea.DEPT_ID
	ORDER BY 
		1, 3;

---

▷ 3. 중첩서브쿼리 
- 데이터를 분석하다보면 조건으로 검색을 많이 하는데 WHERE조건절의 내용이 고정된 경우는 별로 없고 검색 상황에 따라 그 조건내용은 변한다.
이때 조건절에 변경된 내용으로 조건데이터를 전달하고 싶을 때 사용한다
- 즉 메인쿼리테이블의 특정 컬럼 값과 비교할 값을 반환하는 용도로 사용한다

- 서브쿼리를 끝마친 값들을 메인쿼리의 조건절을 통해 비교

- 한 개의 값만 필요한 경우 단일행 서브쿼리가 되고 IN 등 여러 개의 값이 필요한 경우 다중행 서브쿼리가 된다

 

1-1) TBL_EMPLOYEE테이블에서 세종대왕을 찾아 부서명을 가져오기

-- 1) 세종대왕 찾기(검색쿼리)
SELECT * FROM TBL_EMPLOYEE WHERE EMP_NAME = '세종대왕';
-- 2) 세종대왕이 속한 부서아이디 확인
SELECT dept_id FROM TBL_EMPLOYEE WHERE EMP_NAME = '세종대왕'; -- 4

-- 3) 부서테이블에서 2번에서 확인한 부서아이디를 비교하면 부서명 확인 가능
SELECT DEPT_NAME
	FROM TBL_DEPT 
	WHERE DEPT_ID = 4; -- 마케팅팀

1-2) 위의 과정을 중첩쿼리를 통하여 한번에 해결 : '신사임당'의 소속부서 검색

SELECT td.dept_name
	FROM tbl_dept td
	WHERE td.DEPT_ID  = ( 
	-- 조건절이 동적으로 값이 생성되므로 조건을 검색하는 서브쿼리 작성
		SELECT 
			te.DEPT_ID
			FROM TBL_EMPLOYEE te
			WHERE te.EMP_NAME = '신사임당'			
	); -- 회계팀

 

2) 조건절에 비교값이 여러개의 값을 리턴하는 경우
- 나이가 40대인 사원들의 부서정보를 출력하기
- 메인쿼리 : 사원들의 부서정보 출력
- 서브쿼리 : 나이가 40대인 사원
- 메인쿼리와 서브쿼리의 연결고리(조건컬럼)

SELECT 
	  td.DEPT_ID,
	  td.DEPT_NAME,
	  td.USE_YN,
	  td.DEPT_DESC
	FROM 
		TBL_DEPT td
	WHERE 
		td.DEPT_ID IN ( 
			SELECT 
				te.DEPT_ID
			FROM TBL_EMPLOYEE te 
			WHERE te.AGE BETWEEN 40 AND 49
		);

---

▶ 서브쿼리를 활용한 조인

 

▷ 동등조인(EQUI-JOIN)
- WHERE 절에서 = (등호 연산자) 사용하여 2개 이상의 테이블이나 뷰를 연결함
두 컬럼 값이 같은 행을 추출하는 것.
EX) 사원과 부서 테이블에서 공통적으로 존재하는 부서번호를 사용하여 조인.

 

1-1) WHERE조건절의 비교컬럼이 여러개이고 대응하는 조건결과(서브쿼리)의 결과컬럼이 여러개인 경우
- 주소 '서대문구', '중구'에 거주하는 사원들과 성별 & 나이가 같은 사원 출력하기

SELECT te.*
	FROM TBL_EMPLOYEE te 
	WHERE (te.GENDER, te.AGE)
		IN 
	(
	-- 1) 주소테이블에서 주소아이디와
	-- 2) 사원테이블의 주소아이디가 같은 동등조인(inner, equal)
	-- 3) 주소테이블에서 '서대문구'와 '중구' 찾기(IN범위연산 사용)
	SELECT 
		  te1.GENDER, te1.AGE
		FROM 
			TBL_EMPLOYEE te1,
			TBL_ADDRESS ta
		WHERE 
			te1.ADDRESS_ID = ta.ADDRESS_ID AND 
			ta.GU IN ('서대문구', '중구')
			-- ta.ADDRESS_NAME = '서대문구' OR ta.ADDRESS_NAME = '중구'
	);

1-2) 확인해보기 : 서대문구, 중구에 거주하는 직원의 이름 검색하기(left join 활용)

SELECT te.EMP_NAME
	FROM 
		TBL_EMPLOYEE te 
			LEFT join 
		TBL_ADDRESS ta
		ON te.ADDRESS_ID = ta.ADDRESS_ID
	WHERE ta.GU in('서대문구', '중구');

-> 홍길동과 성별(남)과 나이(45세)가 같은 세종대왕이 포함되어 출력이 됨

---

▷ 세미조인(SEMI-JOIN)
- 서브쿼리를 사용해 서브쿼리에 존재하는 데이터만 메인 쿼리에서 추출하는 조인
- 동등연산 범위연산에서 컬럼식을 간소화 한 것
IN, EXISTS 연산자를 사용
​

1) 나이가 40대인 사원의 부서아이디와 부서테이블의 부서아이디가 같은 사원 구하기

SELECT  td.* -- 부서정보
	FROM TBL_DEPT td 
	WHERE EXISTS ( -- 1이 존재하면 참이다
	-- 사원테이블의 부서아이디를 얻어 부서테이블과 조인하여 부서정보를 얻는다
	-- 그리고 나이가 40대인 사원의 부서아이디와 부서테이블의 부서아이디가 같은 사원 구하기(inner join)
		SELECT 
			 1 -- te.DEPT_ID  -- 아래 조건을 만족하면 1을 리턴
			FROM TBL_EMPLOYEE te
			WHERE te.DEPT_ID = td.DEPT_ID AND 
				te.AGE BETWEEN 40 AND 49	
	);

---

▷ 안티조인(ANTI-JOIN)
- 서브쿼리의 B테이블에는 없는 메인 쿼리의 A 테이블의 데이터만 추출하는 조인
- 세미조인의 반대 값
- NOT IN, NOT EXISTS 연산자를 사용

- 동등연산 또는 범위연산의 결과를 뒤집는 것(IN 연산자를 NOT IN으로 대체)

 

1) 40대 직원이 속한 부서를 뺀 나머지 부서정보 출력

SELECT 
	  td.DEPT_ID,
	  td.DEPT_NAME,
	  td.USE_YN,
	  td.DEPT_DESC
	FROM 
		TBL_DEPT td
	WHERE 
		td.DEPT_ID NOT IN ( 
			SELECT 
				te.DEPT_ID
			FROM TBL_EMPLOYEE te 
			WHERE te.AGE BETWEEN 40 AND 49
		);

 

(참고) JOIN

2023.08.01 - [백엔드/데이터베이스] - 데이터베이스(INNER JOIN)

데이터베이스(INNER JOIN)

2023.08.02 - [백엔드/데이터베이스] - 데이터베이스(OUTER JOIN)

데이터베이스(OUTER JOIN)