SQLD 2과목 Part2 정리 [SQL 활용]

서브 쿼리

- 하나의 SQL문 안에 포함되어 있는 또다른 SQL문을 말함

- 반드시 괄호로 묶어야 함

ex) SELECT 안에 SELECT문, INSERT, UPDATE, DELETE 안의 SELECT문

 

서브 쿼리를 사용 가능한 곳

1) SELECT절

2) FROM절

3) WHERE절

4) HAVING절

5) ORDER BY절

6) 기타 DML(INSERT, DELETE, UPDATE)절

* GROUP BY절 사용 불가

 

서브 쿼리 종류

1. 동작하는 방식에 따라

1) UN-CORRELATED(비연관) 서브쿼리

- 서브 쿼리가 메인 쿼리 칼럼을 가지고 있지 않은 형태의 서브 쿼리

- 메인 쿼리에 서브 쿼리가 실행된 결과 값을 제공하기 위한 목적으로 사용

 

2) CORRELATED(연관) 서브쿼리

- 서브 쿼리가 메인 쿼리 칼럼을 가지고 있는 형태의 서브 쿼리

- 일반적으로 메인 쿼리가 먼저 수행된 후에 서브 쿼리에서 조건이 맞는지 확인하고자 할 때 사용

 

2. 위치에 따라

1) 스칼라 서브 쿼리

- SELECT에 사용하는 서브 쿼리

- 서브 쿼리 결과를 마치 하나의 칼럼처럼 사용하기 위해 주로 사용

SELECT * | 칼럼명 | 표현식,
    (SELECT * | 칼럼명 | 표현식
    	FROM 테이블명 또는 뷰명
      WHERE 조건)
  FROM 테이블명 또는 뷰명;

 

2) 인라인뷰

- FROM절에 사용하는 서브 쿼리

- 서브 쿼리 결과를 테이블처럼 사용하기 위해 주로 사용

SELECT * | 칼럼명 | 표현식
  FROM (SELECT * | 칼럼명 | 표현식
  			FROM 테이블명 또는 뷰명)
 WHERE 조건;

 

3) WHERE절 서브 쿼리

- 가장 일반적인 서브 쿼리

- 비교 상수 자리에 값을 전달하기 위한 목적으로 주로 사용(상수항의 대체)

- 리턴 데이터의 형태에 따라 단일행 서브 쿼리, 다중행 서브 쿼리, 상호연관 서브 쿼리로 구분

SELECT * | 칼럼명 | 표현식
   FROM 테이블명 또는 뷰명
 WHERE 조건연산자 (SELECT * | 칼럼명 | 표현식
 			FROM 테이블명 또는 뷰명
                    WHERE 조건);

 

WHERE절 서브 쿼리 종류

1) 단일행 서브 쿼리

- 서브 쿼리 결과가 1개의 행이 리턴되는 형태

- 단일행 서브 쿼리 연산자 종류

 

단일행 서브 쿼리 연산자

= (같다), <> (같지 않다), > (크다), >= (크거나 같다), < (작다), <= (작거나 같다)

 

예제) EMP 테이블에서 전체 직원의 급여 평균보다 높은 급여를 받는 직원의 정보 출력

STEP1) 비교대상(전체 직원 급여 평균) 확인

SELECT AVG(SAL)
   FROM EMP;

 

STEP2) 메인 쿼리의 비교 상수 자리에 서브 쿼리 결과 전달

SELECT EMPNO, ENAME, SAL
   FROM EMP
 WHERE SAL > (SELECT AVG(SAL)
                FROM EMP);

 

2) 다중행 서브 쿼리

- 서브 쿼리 결과로 여러 행이 리턴되는 형태

- '=', '>', '<'와 같은 비교 연산자 사용 불가(여러 값이랑 비교할 수 없는 연산자들)

- 서브 쿼리 결과를 하나로 요약하거나 다중행 서브 쿼리 연산자를 사용

 

다중행 서브 쿼리 연산자

IN (같은 값을 찾음)

> ANY (최소값을 반환)

< ANY (최대값을 반환)

< ALL (최소값을 반환)

> ALL (최대값을 반환)

 

예제) ALL과 ANY 비교

> ALL(2000, 3000) : 최대값(3000)보다 큰 행들 반환

< ALL(2000, 3000) : 최소값(2000)보다 작은 행들 반환

> ANY(2000, 3000) : 최소값(2000)보다 큰 행들 반환

< ANY(2000, 3000) : 최대값(3000)보다 작은 행들 반환

 

예제) 다중행 서브 쿼리 연산자 오류 -> 서브 쿼리 결과가 여러 개일 경우, 연산자와 대소 비교 불가

SELECT EMPNO, ENAME, SAL
  FROM EMP
 WHERE SAL > (SELECT SAL
                FROM EMP
               WHERE DEPTNO = 10);

 

해결1 : 서브 쿼리 결과를 하나의 행의 결과가 되도록 변경

SELECT EMPNO, ENAME, SAL
  FROM EMP
 WHERE SAL > (SELECT MIN(SAL)
                FROM EMP
               WHERE DEPTNO = 10);

 

해결2 : 다중행 서브 쿼리 연산자로 변경

SELECT EMPNO, ENAME, SAL
  FROM EMP
 WHERE SAL > ANY(SELECT SAL
                   FROM EMP
                  WHERE DEPTNO = 10);

 

3) 다중칼럼 서브 쿼리

- 서브 쿼리 결과가 이미 여러 갘ㄹ럼이 리턴되는 형태

- 메인 쿼리와의 비교 칼럼이 2개 이상

- 대소 비교 전달 불가 (두 값을 동시에 묶어서 대소비교 할 수 없음)

 

예제) EMP 테이블에서 부서별 최대 급여자 확인

STEP1) 부서별 최대 급여 확인

SELECT DEPTNO, MAX(SAL)
   FROM EMP
  GROUP BY DEPTNO;

 

STEP2) 메인 쿼리에 비교 대상으로 서브 쿼리 결과 전달

SELECT EMPNO, ENAME, SAL, DEPTNO
  FROM EMP
 WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)
                            FROM EMP
                          GROUP BY DEPTNO);

 

* 부서별 최대 급여가 여러 값이 나오므로, 비교 시에는 다중행 연산자인 IN을 사용 (사용 시 에러 발생)

 

4) 상호연관 서브 쿼리

- 메인 쿼리와 서브 쿼리의 비교를 수행하는 형태

- 비교할 집단이나 조건은 서브 쿼리에 명시

- 메인 쿼리 절에는 서브 쿼리 칼럼이 정의되지 않았기 때문에 에러 발생

 

예제) EMP 테이블에서 부서별로 해당 부서의 평균 급여보다 높은 급여를 받는 사원 정보

 

이렇게 하면 에러 발생. 다중칼럼 서브쿼리는 동시에 두 칼럼에 대한 대소비교 불가

SELECT EMPNO, ENAME, SAL, DEPTNO
  FROM EMP
 WHERE (DEPTNO, SAL) > (SELECT DEPTNO, AVG(SAL)
                          FROM EMP
                         GROUP BY DEPTNO);

 

해결 방법 : 대소 비교할 칼럼을 메인 쿼리에, 일치조건을 서브 쿼리에 전달

SELECT EMPNO, ENAME, SAL, DEPTNO
  FROM EMP E1
 WHERE SAL > (SELECT AVG(SAL)
                FROM EMP E2
               WHERE E1.DEPTNO = E2.DEPTNO
               GROUP BY DEPTNO);

 

* 메인 쿼리와 결과적으로 비교해야 할 칼럼은 SAL과 DEPTNO인데, 그 중 SAL에 대한 대소비교 전에 먼저 

비교할 부서(DEPTNO) 정보가 확정 되어야 함