SQL 서브쿼리 및 집합연산자

⏰ 2024.10.25 (D+12)

1. 서브쿼리(SUBQUERY)

🔖 중요]
- 서브 쿼리는 다른 하나의 SQL 문장 안에 기술된 SELECT문장을 말한다.
- 서브 쿼리는 괄호로 묶어서 사용
- 서브 쿼리만을 단독 실행시 실행하여 결과 추출 가능 시 사용
- 두 종류의 연산자가 서브 쿼리에 사용
- =,>,>=,<,<=,<>,!=(단일행 연산자) 혹은 IN, NOT IN(복수행 연산자)등 서브 쿼리는 연산자의 오른쪽에 기술하여 작성
- 단일행 서브 쿼리에는 단일행 연산자를 다중행 서브 쿼리에는 복수행 연산자를 사용
- 서브 쿼리는 SELECT절,FROM절 WHERE절 등에서 사용 가능

단일행 사용 예시 📖]
SELECT
*
FROM EMP
WHERE EMPNO=7499;

복수행 사용 예시 📖]
SELECT
*
FROM EMP;

단일행 연산자 사용 예시 📖] 평균 급여보다 많이 받는 사람을 구해라
SELECT
*
FROM EMP
WHERE SAL >=2077.08333;

단일행 연산자 사용 예시 📖] 평균 급여보다 적게 받는 사람을 구해라
SELECT
*
FROM EMP
WHERE SAL < 2077.08333;

서브쿼리 사용 예시 📖] 평균 급여보다 많이 받는 사람을 구해라
SELECT
*
FROM EMP
WHERE SAL < (SELECT AVG(SAL) FROM EMP);

서브쿼리 사용 예시 📖] 다중행 연산자를 통한 직무별 가장 많은 급여를 받는 사람을 구해라.
SELECT
EMPNO 사원번호,
ENAME 이름,
JOB 직업,
SAL 연봉
FROM EMP
WHERE (JOB,SAL) IN (SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB);

추가 사용 예시 📖] 다중행 연산자를 통한 직무별 가장 많은 급여를 받는 사람을 구해라.
SELECT
EMPNO 사원번호,
ENAME 이름,
JOB 직업,
SAL 연봉
FROM EMP
WHERE (JOB,SAL) IN (SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB);--서브 쿼리를 사용
WHERE (JOB,SAL) IN (('CLERK',1300),('SALESMAN',1600),('ANALYST',3000),('MANAGER',2975),('PRESIDENT',5000));
/
위의 WEHER의 내용을 풀어서 정리한 경우, 아래의 사항와 같다.
WHERE
JOB='CLERK' AND SAL=1300 OR
JOB='SALESMAN' AND SAL=1600 OR
JOB='ANALYST' AND SAL=3000 OR
JOB='MANAGER' AND SAL=2975 OR
JOB='PRESIDENT' AND SAL=5000;
/

2. 셀프 조인(SELF JOIN)

🔖 중요]
- 셀프 조인(SELF JOIN)은 한 테이블 내의 칼럼끼리 연결하는 조인하는 방법
- FROM 절에 동일 테이블을 두 번 언급해야 하기 때문에 별칭을 사용

조건식 ⭐]
SELECT
E1.ENAME || '의 매니저는' || E2.ENAME || '입니다'
FROM EMP E1 JOIN EMP E2 ON E1.MGR=E2.EMPNO;

3. 집합 연산자

🔖 중요]
- 여러 개의 SELECT 문 쿼리 결과 셋을 합치거(합집합)나 공통된 행만 추출(교집합) 하거나
첫 번째 결과 집합에만 존재하는 행(차집합) 만 포함시킬때 사용하는 연산자

💡 집합 연산자 사용시 주의사항]
- 각 SELECT 쿼리의 컬럼의 개수와 데이터 타입이 동일하게 부여
- 첫 번째 쿼리의 컬럼명(별칭)이 결과셋의 컬럼명으로 적용
- 즉 두 번째 SELECT쿼리의 컬러명(별칭)은 결과셋에 적용 불가
- ORDER BY는 맨 마지막에 한번만 사용 할 수 있으며 집합 연산자에 의한 모든 결과를 정렬
- 정렬은 첫번째 SELECT쿼리의 컬럼명(별칭) 및 컬럼 인덱스(1부터 시작)으로 정렬

1) UNION
두 개 이상의 SELECT 문 결과를 결합할 때 사용
중복된 행을 제외 하고 유니크한 행만 결과에 포함
따라서 결과 집합에 중복 행이 없다(합집합)

2) UNION ALL(합집합)
두 개 이상의 SELECT 문 결과를 결합할 때 사용
중복된 행을 제거하지 않고 모든 행을 결과에 포함 따라서 결과 집합에 중복 행 발생 가능성 ↑
UNION은 쿼리를 결과를 합치고 중복을 제거하는 연산 작업이 행해짐으로 쿼리의 속도 및 부하가 발생
중복을 제거할 필요가 없으면 UNION ALL을 사용하는 것이 효율적

조건식 ⭐]
SELECT job [AS 직무], deptno
FROM emp
WHERE sal >= 2000
UNION [ALL]
SELECT job [AS 직책], deptno
FROM emp
WHERE deptno = 20
ORDER BY 직무 --컬럼명(별칭)으로 정렬.(직책으로 정렬시 "직책": 부적합한 식별자)
[ORDER BY 1] -- 컬럼 인덱스로 정렬

UNION, UNION ALL 예시 📖]
SELECT
DEPTNO,
SAL 연봉
FROM EMP
WHERE SAL >=2000

UNION로 작성한 경우, 중복값 제거
UNION ALL로 작성한 경우, 중복값 모두 추출

SELECT
DEPTNO,
MAX(SAL)월급
FROM EMP
WHERE DEPTNO = 20
GROUP BY DEPTNO;

예시 문제 📙]
부서별 최소/최고 연봉을 받은 직원의 정보 조회

❗방법 1.
SELECT
ename,
sal,
deptno
FROM emp
WHERE (deptno, sal)
IN (SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno

UNION ALL

SELECT
deptno,
MIN(sal)
FROM emp
GROUP BY deptno)
ORDER BY DEPTNO, sal DESC;;

❗방법 2.
SELECT
ename,
sal,
deptno
FROM emp
WHERE (deptno, sal) IN
(SELECT deptno, MAX(sal) FROM emp GROUP BY deptno)
OR (deptno, sal)
IN(SELECT deptno, MIN(sal) FROM emp GROUP BY deptno)
ORDER BY DEPTNO,sal DESC;;

3) INTERSECT(교집합)
연산자는 두 개 이상의 SELECT 문 결과에서 공통된 행만 추출
결과 집합에는 공통된 행만 포함되며, 중복된 행 제거

INTERSEC 예시 📖]
SELECT job AS 직업, deptno
FROM emp
WHERE sal >= 2000
INTERSECT
SELECT job AS 직책, deptno
FROM emp
WHERE DEPTNO=20
ORDER BY 2;

4) MINUS(차집합)
MINUS 연산자는 첫 번째 SELECT 문의 결과에서 두 번째 SELECT 문의 결과를 제외
결과 집합에는 첫 번째 결과 집합에만 존재하는 행만 포함
MINUS는 두 결과 집합 간의 차이를 구할 때 사용

MINUS 예시 📖]
SELECT job AS 직업, deptno
FROM emp
WHERE sal >= 2000
MINUS
SELECT job AS 직책, deptno
FROM emp
WHERE DEPTNO=20
ORDER BY 2;

4. TOP-n쿼리

SELECT쿼리 결과에서 위에서부터 순서대로 몇 개만 가져오는 경우에 사용
데이터가 입력된 순서대로 혹은 서브 퀴리에 의해 생성된 결과 셋에 레코드가 생성된 순서대로 내부적으로 번호가 순차적으로 부여되며 ROWNUM이라는 컬럼에 내부적으로 저장

ROWNUM 컬럼을 조회하는 예제 📖]
SELECT E.* ,
ROWNUM FROM EMP E;--인라인 뷰 미 사용

서브쿼리 이용한 TOP-n쿼리 조회 예제 📖]
SELECT E. ,
ROWNUM FROM
(SELECT FROM EMP ORDER BY HIREDATE DESC)E
--표현방법 1) WHERE ROWNUM <=3;
--표현방법 2) FETCH FIRST 3 ROWS ONLY;--인라인 뷰 사용

추가 참고사항 📌]
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
OFFSET 3 ROWS FETCH NEXT 3 ROWS WITH TIES;--중복값이 있는 경우 포함

WITH 별칭 이용한 TOP-n쿼리 조회 예제 📖]
WITH SAL_ORDER AS (SELECT FROM EMP ORDER BY SAL DESC)
SELECT FROM SAL_ORDER
--표현방법 1) WHERE ROWNUM <=3;
--표현방법 2) FETCH FIRST 3 ROWS ONLY;

OFFSET 이용한 TOP-n쿼리 조회 예제 📖]
SELECT * FROM EMP ORDER BY SAL DESC
--위의 쿼리만 조회하는 경우, 추출 결과값
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO
7934 | MILLER | CLERK | 7782 | 82/01/23 | 1300 | 10 |
7654 | MARTIN | SALESMAN | 7698 | 81/09/28 | 1250 | 1400 | 30 (⭐동일한 값이 2개 이므로 위의 데이터 제외 처리)
7521 | WARD | SALESMAN | 7698 | 81/02/22 | 1250 | 500 | 30
OFFSET 7 ROWS FETCH NEXT 2 ROWS ONLY;
--아래의 쿼리를 함께 조회하는 경우, 추출 결과값
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO
7934 | MILLER | CLERK | 7782 | 82/01/23 | 1300 | 10 |
7521 | WARD | SALESMAN | 7698 | 81/02/22 | 1250 | 500 | 30

추가 참고사항 1.📌]
SELECT * FROM EMP ORDER BY SAL DESC
--50%으로 데이터만 추출하는 경우
FETCH FIRST 50 PERCENT ROWS ONLY;
--위의 결과값과 동일하게 OFFSET으로 추출하는 경우
OFFSET 0 ROWS FETCH NEXT 6 ROWS ONLY;

추가 참고사항 2.📌]
특정 구간에 있는 레코드 (Between A and B 이용:페이징시)

조건식 ⭐]
SELECT
FROM
(
Select a.,rownum num -서브쿼리
FROM (
Select * from employees order by salary desc(서브쿼리안의 서브쿼리)
) a
)
WHERE num between 11 and 20

❗Step 1)
서브 쿼리안의 서브쿼리는 특정 컬럼(보통 PK컬럼)으로 ORDER BY DESC
그리고 생성된 테이블 별칭을 부여

❗Step 2)
서브 쿼리안의 SELECT절에서 STEP1의 별칭.*,ROUNUM 컬럼 별칭을 기술한다
Step3)
밖의 WHERE절에서 ROWNUM을 별칭한이름으로 between a and b한다.

특정 구간에 있는 레코드 (Between A and B 이용:페이징시) 예제 📖]
SELECT
FROM
(
SELECT T., ROWNUM R
FROM(SELECT * FROM EMP ORDER BY SAL DESC)T
)
WHERE R BETWEEN 3 AND 5;

기타 사항 💡]
서브쿼리, WITH, OFFSET 없이 짧게 표현할 수 있는 방법
SELECT * FROM EMP ORDER BY SAL DESC
FETCH FIRST 3 ROWS ONLY;

5. SQL 문제 16~18, 25~27번

📙문제 16]
직원 중에 연봉이 최소인 직원의 이름, 연봉, 직책 그리고 150%인상된 연봉을 출력해라

SELECT
EMPNO 사원번호,
ENAME 이름,
JOB 직책,
SAL 연봉,
SAL*1.5 인상연봉
FROM EMP
WHERE SAL = (SELECT MIN(SAL) FROM EMP);

📙문제 17]
직원 중에 최대/최소 연봉을 받은 직원의 연봉,이름,직책을 최소 연봉부터 출력하여라

SELECT
EMPNO 사원번호,
ENAME 이름,
JOB 직책,
SAL 연봉
FROM EMP
WHERE SAL IN ((SELECT MIN(SAL) FROM EMP) , (SELECT MAX(SAL) FROM EMP))
--WHERE SAL = (SELECT MIN(SAL) FROM EMP) OR SAL = (SELECT MAX(SAL) FROM EMP)
ORDER BY SAL;

📙문제 25]
SCOTT계정에서 연봉이 평균 이하인 직원들의 이름,연봉,부서 코드,입사일을 출력 하여라.

SELECT
*
FROM EMP
WHERE SAL < (SELECT AVG(SAL) FROM EMP);

📙문제 26]
SCOTT 계정에서 각 부서 별로 최고 연봉을 받는 직원의 이름,연봉,부서 코드,입사일을 출력 하여라

서브쿼리
SELECT
DEPTNO,
MAX(SAL)
FROM EMP
GROUP BY DEPTNO;

SELECT
EMPNO 사원번호,
ENAME 이름,
SAL 연봉,
DEPTNO 부서코드,
HIREDATE 입사일
FROM EMP
WHERE (DEPTNO,SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);

📙문제 27]
SCOTT 계정에서 각 부서 별로 최고 연봉을 받는 직원의 이름,연봉,부서 코드,부서명,입사일을 출력하여라

SELECT
EMPNO 사원번호,
ENAME 이름,
SAL 연봉,
E.DEPTNO 부서코드,
D.DNAME 부서명,
HIREDATE 입사일
FROM EMP E JOIN DEPT D ON e.deptno=d.deptno
WHERE (D.DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP DEPT GROUP BY DEPTNO);

📙문제 28]
EMP테이블에서 부서 코드가 30인 직원 중에 연봉 높은 순서로 위 에서 부터 3명을 출력 하여라
출력시 컬럼은 이름,연봉,직책,부서 코드를 출력하여라.

SELECT
ENAME 이름,
SAL 연봉,
JOB 직책,
DEPTNO 부서코드
FROM EMP
WHERE DEPTNO=30
ORDER BY SAL DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

SELECT
ENAME 이름,
SAL 연봉,
JOB 직책,
DEPTNO 부서코드
FROM (SELECT T.,ROWNUM R FROM (SELECT FROM EMP WHERE DEPTNO=30 ORDER BY SAL DESC) T)
WHERE R BETWEEN 1 AND 3;