<Database>SUBQUERY

이제내손을java...·2024년 3월 19일

Database 개념정리

목록 보기

5/11

안녕하세요 오늘은 SELECT문의 마지막 관문이라고 할 수 있는
SUBQUERY에 대해 정리해보았습니다!!😊😊✌️

SUBQUERY

SELECT문에 또다른 SELECT문을 작성하는 것이다.

주 SELECT 문 보조 SELECT 문

서브쿼리문은 반드시 괄호로 묶어줘야하한다

서브쿼리는 SELECT문의 컬럼위치, FROM 절,WHERE 절 에 사용이 가능

INSERT, UPDATE,CREATE문에도 사용
-- SELECT 컬럼명,(SELECT 컬럼명 FROM 테이블)
: 스칼라서브쿼리, 단일행서브쿼리
-- FROM (SELECT 컬럼명.... FROM 테이블) : 다중행, 다중행다중열 서브쿼리
-- WHERE 컬럼명 비교연산(SELECT 컬럼명 FROM 테이블명) : 단일행, 다중행, 다중열 서브쿼리

윤은해 사원과 동일한 급여를 받고 있는 사원 조회하기

SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME='윤은해';
SELECT *
FROM EMPLOYEE
WHERE SALARY=(SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME='윤은해');

D5부서의 평균급여보다 급여를 많이 받는 사원 조회하기

SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE='D5';
SELECT E.*,(SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE='D5') AS AVG_SAL
FROM EMPLOYEE E
WHERE SALARY>=(SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE='D5');

1. 단일행 서브쿼리

서브쿼리의 결과 (RESULTSET)가 1개열 1개행인 경우를 말한다.

전체사원의 평균급여보다 많은 급여를 받는 사원의 이름, 급여, 부서코드 조회하기
✋문제풀이

SELECT EMP_NAME,SALARY,DEPT_CODE
FROM EMPLOYEE E
WHERE SALARY>=(SELECT AVG(SALARY) FROM EMPLOYEE);

부서가 총무부인 사원을 조회하기

SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE=(SELECT DEPT_ID FROM DEPARTMENT WHERE DEPT_TITLE='총무부');

직책이 과장인 사원을 조회하기

SELECT *
FROM EMPLOYEE
WHERE JOB_CODE=(SELECT JOB_CODE FROM JOB WHERE JOB_NAME='과장');

2. 다중행 서브쿼리

서브쿼리의 결과가 1개컬럼, 다수의 행을 갖는 것이다.

직책이 과장, 부장인 사원 조회하기

SELECT JOB_CODE FROM JOB WHERE JOB_NAME IN('과장','부장'); 
-- 2개의 행이 나옴 (다중행)

SELECT *
FROM EMPLOYEE
WHERE JOB_CODE IN(SELECT JOB_CODE FROM JOB WHERE JOB_NAME IN('과장','부장')); -- JOB_CODE가 1개이기때문에 () 와 비교할 수 없다-> IN을 사용

다중행 서브쿼리 대소비교하기

ANY OR,ALL()AND 이용

1. ANY

컬럼>(=)ANY() : 다중행 서브쿼리의 결과 중 하나라도 크면 참
-> 다중행 서브쿼리 결과 중 최소값보다 크면 참

컬럼<(=)ANY() : 다중행 서브쿼리의 결과 중 하나라도 작은면 참
-> 다중행 서브쿼리 결과 중 최대값보다 작으면 참
-> D5,D6사원의 급여보다 많이 받는 사원
SELECT *
FROM EMPLOYEE
WHERE SALARY> ANY(SELECT SALARY FROM EMPLOYEE WHERE DEPT_CODE IN('D5','D6'));  
-- > 180만원 이상 받는 사원들만 출력됨
SELECT *
FROM EMPLOYEE
WHERE SALARY< ANY(SELECT SALARY FROM EMPLOYEE WHERE DEPT_CODE IN('D5','D6'));  
-- > 최대값보다 작게 받는 값들이 출력됨
2. ALL

컬럼>(=)ALL() : 다중행 서브쿼리의 결과 중 모든값보다 크면 참
->다중행 서브쿼리 결과 중 최대값보다 크면 참

컬럼<(=)ALL() : 다중행 서브쿼리의 결과 중 모든값보다 크면 참
->다중행 서브쿼리 결과 중 최소값보다 크면 참
SELECT *
FROM EMPLOYEE
WHERE SALARY> ALL(SELECT SALARY FROM EMPLOYEE WHERE DEPT_CODE IN('D5','D6')); -- 390만원 이상 받는 사원들이 출력됨
SELECT *
FROM EMPLOYEE
WHERE SALARY<ALL(SELECT SALARY FROM EMPLOYEE WHERE DEPT_CODE IN('D5','D6')); -- 180만원 보다 적게 받는 사원들이 출력됨

3. 다중열 서브쿼리

서브쿼리의 결과가 1개행, 다수 컬럼을 갖는 것이다.

퇴직한 여사원의 같은 부서, 같은 직급에 해당하는 사원 조회하기

SELECT DEPT_CODE,JOB_CODE FROM EMPLOYEE WHERE ENT_YN='Y';
//위는 퇴직한 사원의 부서, 직급을 구하는 문이고 이SELECT문이 서브문으로 들어가게된다. 
SELECT DEPT_CODE,JOB_CODE,EMP_NAME
FROM EMPLOYEE
WHERE (DEPT_CODE,JOB_CODE)=(SELECT DEPT_CODE,JOB_CODE FROM EMPLOYEE WHERE ENT_YN='Y');

기술지원부 이면서 급여가 200만원인 사원이 있음
그 사원의 이름, 부서코드, 급여 출력하기(다중열 서브쿼리로 풀어보기)

SELECT DEPT_CODE,SALARY FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID 
WHERE SALARY=2000000 AND DEPT_TITLE='기술지원부';

위의 SELECT문은 기술지원부이면서 급여가 200만원 이상인 사원을 골라내줌(서브쿼리문으로 들어감)

SELECT EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE (DEPT_CODE,SALARY)
        =(SELECT DEPT_CODE,SALARY FROM EMPLOYEE WHERE DEPT_CODE=(SELECT DEPT_ID FROM DEPARTMENT WHERE DEPT_TITLE='기술지원부') 
        AND SALARY=2000000);

4. 다중열 다중행 서브쿼리

서브쿼리의 결과가 다수행, 다수 컬럼을 갖는 것이다.

FROM 절에 많이 사용함 -> INLINE VIEW 가상의 테이블을 만들어놓고 사용할 때를 말함
SELECT * 
FROM(SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID JOIN JOB USING(JOB_CODE)); 

상관서브쿼리

서브쿼리의 SELECT문을 작성할 때 메인쿼리의 값을 가져와 사용하는 구문이다.

예를 들어 본인이 속한 부서의 사원수 출력, 본인이 속한 부서의 급여 평균보다 급여를 많이 받는 사원조회하기를 하면 본인이라는 값이 바뀌면서 해당하는 값도 바뀌면서 출력/집계된다.
-- 상품에 달려있는 댓글갯수, 게시글에 달려있는 첨부파일 갯수

✋문제풀이

본인이 속한 부서의 사원수를 구하기

SELECT COUNT(*) FROM EMPLOYEE GROUP BY DEPT_CODE;

--> 본인이 속한 부서의 사원 수가 아님 그냥 부서별로 사원수를 구한것,
정확하게 상관 서브쿼리를 사용해서 풀기⤵️

SELECT EMP_NAME,DEPT_CODE,(SELECT COUNT(*)FROM EMPLOYEE WHERE DEPT_CODE=E.DEPT_CODE) AS 사원수
FROM EMPLOYEE E;

본인이 속한 부서의 사원수가 3명이상인 사원만 조회하기

SELECT *
FROM EMPLOYEE E
WHERE (SELECT COUNT(*)FROM EMPLOYEE WHERE DEPT_CODE=E.DEPT_CODE)>=3;

EXISTS예약어

이용해서 ROW를 필터할 수 있음

서브쿼리의 결과가 1개 이상이면 TRUE, 0개면 FALSE반환하는 연산

✋문제풀이

매니저인 사원을 조회하기

SELECT *
FROM EMPLOYEE E
WHERE EXISTS(SELECT 1 FROM EMPLOYEE WHERE MANAGER_ID=E.EMP_ID);

최고급여를 받는 사원 조회하기

SELECT *
FROM EMPLOYEE E
WHERE NOT EXISTS(SELECT * FROM EMPLOYEE WHERE SALARY>E.SALARY);

자신이 속한 직급의 평균급여보다 많은 급여를 받는 사원의 이름, 직책명, 급여 조회하기

SELECT EMP_NAME,SALARY,JOB_NAME 
FROM EMPLOYEE E JOIN JOB J ON E.JOB_CODE=J.JOB_CODE
WHERE SALARY> (SELECT AVG(SALARY) FROM EMPLOYEE WHERE JOB_CODE=E.JOB_CODE);

FROM절에 서브쿼리 사용하기 -> 논리적인 가상테이블

INLINE VIEW : 1회용으로 사용하는 구문 -> 재사용 불가능
STORED VIEW : 저장해놓고 사용하는 구문 -> 재사용가능 -> VIEW 테이블
➡️ RESULTSET을 테이블처럼 이용할 수 있게 해주는 기능이다.
SELECT *
FROM (SELECT EMP_NAME, EMAIL, PHONE, SALARY*12 AS YEAR_SAL
                FROM EMPLOYEE);

ex)

성별을 추가한 가상테이블을 만들어서 이용하기

SELECT E.*, DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여')
FROM EMPLOYEE E
WHERE SUBSTR(EMP_NO,8,1)=2;

⤵️

SELECT *
FROM ( SELECT E.*, DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여') AS GENDER
            FROM EMPLOYEE E)
WHERE GENDER='여';

이런식으로 통계쿼리, 자주사용하는 JOIN, 집합연산결과, 가상컬럼이 추가된 SELECT문일 때 view 많이 쓰인다.

✋문제풀이

연봉을 포함한 사원의 사원명, 부서명, 직책명, 자신이 속한 부서 평균급여(버림, 원화)
-- 평균급여가 300만원이상인 사원 구하기

SELECT V.*,EMP_NAME,TO_CHAR(DEPT_AVG,'L999,999,999') AS DEPT_AVG

FROM ( SELECT EMP_NAME,NVL(DEPT_TITLE,'인턴') AS DEPT_TITLE, JOB_NAME, (SALARY+(SALARY*NVL(BONUS,0)))*12 AS YEAR_SAL,
                                (SELECT FLOOR(AVG(SALARY)) FROM  EMPLOYEE WHERE E.DEPT_CODE=DEPT_CODE) AS DEPT_AVG
                FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON DEPT_CODE=DEPT_ID
                                JOIN JOB J ON E.JOB_CODE=J.JOB_CODE) V
WHERE YEAR_SAL>=3000000;

➡️ 별칭을 설정하여 다른조건에서도 별칭으로 사용할 수 있다(테이블안에서만), 사용자에게 보여주고싶지않은 컬럼을 걸러서 설정해서 보여줄 수 있는 기능도 한다.

⤵️ 아이디에 2가 들어간 사람을 조회할 때
서브쿼리문으로 ROW를 UNION으로 합친 테이블을 만들어놓고 사용함.

SELECT * 
FROM (
    SELECT EMP_ID AS ID, EMP_NAME AS NAME
    FROM EMPLOYEE
    UNION
    SELECT DEPT_ID, DEPT_TITLE
    FROM DEPARTMENT
    UNION
    SELECT JOB_CODE, JOB_NAME
    FROM JOB
    )
WHERE ID LIKE '%2%' ;

인라인뷰 안에 인라인뷰를 넣을 수도 있다.⤵️

SELECT A.*
 FROM (
        SELECT T.*,(SELECT AVG(SALARY) FROM EMPLOYEE WHERE JOB_CODE=T.JOB_CODE) AS AVG_JOB
        FROM (
            SELECT SALARY*12 AS YEAR_SAL, E.*,D.*, J.JOB_CODE AS JOB_JOB_CODE, J.JOB_NAME,
                (SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE=E.DEPT_CODE) AS AVG_DEPT
            FROM EMPLOYEE E JOIN DEPARTMENT D ON DEPT_CODE=DEPT_ID
                        JOIN JOB J ON E.JOB_CODE=J.JOB_CODE
        ) T
 ) A;

ROW 순위정하기

오라클이 기본적으로 제공하는 ROWNUM이라는 가상컬럼을 이용하는 방법

오라클이 제공하는 WINDOW함수 RANK()를 이용하는 방법

ROWNUM

SELECT한 데이터에 일련번호를 붙이는 것이다. 테이블이나, 특정 집합에서 원하는 만큼의 행만 가져오고 싶을 때, 행의 개수를 제한하는 용도로 사용한다.

✋문제풀이

급여를 적게받는 TOP-3 조회하기

 SELECT ROWNUM, E.*
 FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY) E
 WHERE ROWNUM <=3;

급여를 적게받는 5~10위 사원 사원명, 급여 조회하기
-- ROWNUM을 이용해서 가져오기

 SELECT ROWNUM, A.* 
 FROM(
        SELECT ROWNUM AS RNUM,  E.*
        FROM (
            SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY
            ) E
        ) A
 WHERE A.ROWNUM BETWEEN 5 AND 10;

-- ROWNUM은 중간번호의 ROW를 찾지 못함(어디가 5번이지?🤔) 그래서 한번 더 FROM 안에 씌워줘야한다. (가상컬럼으로 지정/고정값으로 만들어줌)

SELECT ROWNUM AS RNUM, E.*
        FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY) E
WHERE ROWNUM BETWEEN 5 AND 10;

급여를 많이 받은 사원 3명 조회하기
사원명 부서명, 직책명, 급여

SELECT *
FROM (SELECT ROWNUM AS RNUM, E.* 
       FROM
      (SELECT EMP_NAME,DEPT_TITLE,JOB_NAME, SALARY
         FROM EMPLOYEE 
                 LEFT JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
                 JOIN JOB USING(JOB_CODE)
 ORDER BY SALARY DESC)E)
-- WHERE RNUM BETWEEN 1 AND 3;

급여를 많이 받는 10등 15등까지 조회하기
사원명 부서명, 직책명, 급여

WHERE RNUM BETWEEN 10 AND 15;

RANK() OVER(ORDER BY ) : 동일값이 나왔을때 번호를 생략 - 누락번호발생함

DENSE_RANK() OVER(ORDER BY ) OVER : 동일값 나왔을때 번호를 생략하지않음 - 누락번호 없음

✋문제풀이

 SELECT *
 FROM  ( SELECT RANK() OVER(ORDER BY SALARY) AS "ORDER", 
 			DENSE_RANK() OVER(ORDER BY SALARY) AS "ORDER2",
            E.*
            FROM EMPLOYEE E
                 )
 WHERE "ORDER" BETWEEN 5 AND 10;

평균급여를 많이 받는 부서 3개출력하기
-- 부서명, 평균급여

 SELECT *
 FROM (
     SELECT DEPT_TITLE, AVG(SALARY), RANK() OVER(ORDER BY AVG(SALARY) DESC) AS AVG_ORDER
     FROM EMPLOYEE 
                    LEFT JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
     GROUP BY DEPT_TITLE
     )
 WHERE AVG_ORDER BETWEEN 1 AND 3;

WITH

한개 SELECT문안에서 지정된 SELECT문을 재사용하기 위해 사용한다.
이름이 부여된 서브쿼리이며,
임시테이블을 만든다는 관점에서보면 VIEW와 쓰임새가 비슷한데
차이점이 있다면 VIEW는 한번 만들어놓으면 DROP 할 때까지 없어지지 않지만 WITH절의 경우 한번 실행할 쿼리문내에 정의되어 있을 경우 그 쿼리문안에서만 실행된다는 차이점이 있다.

WITH 
    TEST_WITH AS (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY  SALARY DESC),
    TEST_WITH2 AS (SELECT * FROM EMPLOYEE)

SELECT EMP_NAME,SALARY FROM TEST_WITH
UNION
SELECT EMAIL, BONUS
FROM TEST_WITH2;

점점 어려워지고있는 .. ㅜㅜ 하지만 이겨내봅시당 !😊

이제내손을java...

히죽코딩

이전 포스트

<Database> 그룹 함수

다음 포스트

<Database>SUBQUERY

Database 개념정리

SUBQUERY

1. 단일행 서브쿼리

2. 다중행 서브쿼리

ANY OR,ALL()AND 이용

3. 다중열 서브쿼리

4. 다중열 다중행 서브쿼리

상관서브쿼리

EXISTS예약어

ROW 순위정하기

ROWNUM

RANK() OVER(ORDER BY ) : 동일값이 나왔을때 번호를 생략 - 누락번호발생함

DENSE_RANK() OVER(ORDER BY ) OVER : 동일값 나왔을때 번호를 생략하지않음 - 누락번호 없음

WITH

<Database> 그룹 함수

<Database>DDL

0개의 댓글