멋쟁이 사자처럼 21회차 ( 03 / 28 )

🦁멋쟁이 사자처럼 15기/3월달 수업 내용 정리

멋쟁이 사자처럼 21회차 ( 03 / 28 )

코딩하는 하마 2025. 3. 28. 16:57

[학습 목표]

1. 서브 쿼리의 개념과 작성 위치 (WHERE, SELECT, FROM절 등)를 이해하고 적용할 수 있다.

2. 단일 행 서브쿼리와 다중 행 서브쿼리의 차이점과 적절한 연산자 (IN,ANY,ALL 등)의 사용법을 설명할 수 있다.

3. 서브쿼리를 활용하여 메인 쿼리의 조건을 동적으로 설정하고, 복잡한 조건의 데이터를 조회할 수 있다.

4. 상호 연관 서브쿼리(Correlated Subquery)의 동작 방식을 이해하고, 행마다 조건이 달라지는 상황에 적절히 활용할 수 있다.

서브 쿼리

중첩 쿼리 (= 수직 필터링 추출)를 말함 (select , where , from )등 다양한 곳에 서브 쿼리를 줄 수 있다.

-> 로직을 분리해서 조건 필터링을 한다

ex)

-- Q1. JONES의 월급보다/ 더 많은 월급을 받는 사원의 이름과 봉급을 출력하시오.
SELECT ENAME , SAL FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = "JONES");

단일행과 다중행에 따른 연산자

1) 단일행과 다중행

단일 행 또는 다중 행 결과를 반환할 수 있고, 사용되는 연산자가 달라진다.
- 단일 행 : 서브쿼리 결과가 1개의 행만 리턴
연산자: =, >, <, >=, <=, != 등 일반 비교 연산자

- 다중행 : 서브쿼리 결과가 여러 개의 행을 리턴
연산자: IN, NOT IN, ANY, ALL, EXISTS, NOT EXISTS 등

2) 서브쿼리 연산자

IN :서브쿼리 결과 목록 중 포함된 값이면 참
NOT IN :서브쿼리 결과 목록 중 포함되지 않으면 참
> ANY :서브쿼리 결과 중 하나라도 초과하면 참
< ALL :서브쿼리 결과 모두보다 작으면 참
EXISTS :서브쿼리 결과가 존재하면 참
NOT EXISTS: 서브쿼리 결과가 없으면 참

ex) IN 연산자 사용

-- Q2. 직업이 'SALESMAN'인 사원과 같은 월급을 받는 사원의 이름과 월급을 출력하시오.
SELECT ENAME , SAL FROM EMP WHERE SAL IN (SELECT SAL  FROM EMP WHERE JOB = 'SALESMAN');
-> 에러 남 ( 내부 쿼리의 결과값이 1줄 이상이여서) 

SELECT SAL FROM EMP WHERE JOB = 'SALESMAN'; 실행 시 결과

[실행 시 결과]

위 결과를 봤듯이 실행 결과가 한 줄이 아닌 여러 줄인 것을 볼 수 있다. 그래서 첫 줄의 코드는 에러가 날 수 있음을 알 수 있다. 아래와 같이 코드를 수정하면 원하던 결과값이 실행된다.

SELECT ENAME , SAL FROM EMP WHERE SAL IN (SELECT SAL  FROM EMP WHERE JOB = 'SALESMAN');

3) ANY , ALL 연산자

ANY ( DATA OR DATA OR ....NULL)
= ANY      하나라도 만족하는 값이 있으면 결과를 리턴하며 IN과 동일함
ANY      값들 중 최소값 보다 크면 결과를 리턴
>= ANY  값들 중 최소값 보다 크거나 같으면 결과를 리턴
< ANY      값들 중 최대값 보다 작으면 결과를 리턴
<= ANY  값들 중 최대값 보다 작거나 같으면 결과를 리턴
<> ANY  모든 값들 중 다른 값만 리턴, 값이 하나일 때만 가능함

ALL( DATA AND DATA AND ....NULL)
ALL 값들 중 최대값 보다 크면 결과를 리턴

>= ALL  값들 중 최대값 보다 크거나 같으면 결과를 리턴
< ALL      값들 중 최소값 보다 작으면 결과를 리턴
<= ALL  값들 중 최소값 보다 작거나 같으면 결과를 리턴
= ALL      모든 값들과 같아야 결과를 리턴, 값이 하나일 때만 가능함
<> ALL  모든 값들과 다르면 결과를 리턴

-- Q9-1 20번 부서의 사원 중 가장 많은 월급을 받은 사원들보다 더 많은 월급을 받는 사원의 이름과 월급을 출력하자.
-- ALL 또는 ANY 사용
SELECT ENAME , SAL 
FROM EMP 
WHERE SAL >ALL ( SELECT SAL FROM EMP WHERE DEPTNO = 20);

-- Q9-2 20번 부서의 사원 중 가장 적은 월급을 받은 사원들보다 더 많은 월급을 받는 사원의 이름과 월급을 출력하자.

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

Recursive Common Table Expression [ 재귀적 CTE ]

1) 재귀적 CTE

Recursive Common Table Expressions [계층 구조를 재귀적으로 탐색할 때 사용] : 재귀적 CTE
WITH RECURSIVE -> 자기 자신을 반복 호출하는 쿼리 : 계층 구조 , 반복 구조 확인하는 쿼리

[형태]

WITH RECURSIVE cte (n) AS
(
  SELECT 1       [1] 시작값 
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5   -- [2] 다음값을 생성
)
SELECT * FROM cte;

ex)

WITH RECURSIVE GUGUDAN AS (
  -- [1] 초기값 지정 3*1
  SELECT 3 AS DAN , 1 AS NUM , 3*1 AS RESULT 
  
  UNION ALL
   -- [2] 재귀적으로 NUM을 1씩 증가시키면서 9까지 반복
   SELECT DAN , NUM+1 , DAN * ( NUM+1)
   FROM GUGUDAN 
   WHERE NUM <9
   )
   SELECT CONCAT( DAN , " * "  ,NUM," = ",RESULT) AS GUGUDAN_PRN
   FROM GUGUDAN;

2) 프로시저로 만들어서 호출해보기

마우스 우클릭 -> Create Stored Procedures

그 후 APPLY를 눌러줘서 저장한다.

그리고 쿼리문에 다음과 같이 입력하면 프로시저가 호출되어 코드가 실행된다.

 CALL 03_GUGUDAN();

만약에 저 코드에서 단을 내가 지정하고 결과값을 출력하고 싶다면 인자값을 주어주면 된다.

CREATE DEFINER=`hippo`@`%` PROCEDURE `03_GUGUDAN`(IN P_DAN INT)
BEGIN
WITH RECURSIVE GUGUDAN AS (
  -- [1] 초기값 지정 입력 받은 단 *1
  SELECT P_DAN AS DAN , 1 AS NUM , P_DAN*1 AS RESULT 
  
  UNION ALL
   -- [2] 재귀적으로 NUM을 1씩 증가시키면서 9까지 반복
   SELECT DAN , NUM+1 , DAN * ( NUM+1)
   FROM GUGUDAN 
   WHERE NUM <9
   )
   SELECT CONCAT( DAN , " * "  ,NUM," = ",RESULT) AS GUGUDAN_PRN
   FROM GUGUDAN;
END

호출도 인자값 주어주는 것 빼고는 비슷하다.

CALL 03_GUGUDAN(7);

MULTI COLUMN SUBQUERY

1) MULTI COLUMN SUBQUERY 예제 보기

ex)

-- Q1) 직업이 'SALESMAN' 인 사원과 같은 부서에서 근무 하고 (AND) 
 -- 같은 월급을 받는 사원들의 이름 , 월급 , 부서번호를 출력하자. 
 
 SELECT ENAME , SAL , DEPTNO
 FROM EMP 
 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE JOB = 'SALESMAN') 
 AND  SAL IN (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');
/*
ALLEN	1600.00	30
WARD	1250.00	30
MARTIN	1250.00	30
TURNER	1500.00	30
*/

-- 두 컬럼을 묶어서 정리할 수도 있음 (결과값은 동일)
SELECT ENAME , SAL , DEPTNO
 FROM EMP 
 WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL FROM EMP WHERE JOB = 'SALESMAN') 
/*
ALLEN	1600.00	30
WARD	1250.00	30
MARTIN	1250.00	30
TURNER	1500.00	30
*/

2) EXISTS 와 NOT EXISTS

EXISTS와 NOT EXISTS를 사용하여 특정 조건이 충족되는지 여부만을 판단하는 논리적 테스트를 수행한다.

ex)

-- 5) EXISTS와 NOT EXISTS를 사용하여 특정 조건이 충족되는지 여부만을 판단하는 논리적 테스트를 수행한다. 
 -- EXISTS를 사용해서 {부서에 사원이 존재 하는지  확인 후 부서명을 출력} 해보자.
 -- 부서에 사원이 존재하는 부서명을 출력해보자.
 
SELECT DNAME
FROM dept d
WHERE EXISTS (SELECT 1
              FROM emp 
              WHERE DEPTNO = d.DEPTNO);
 
 SELECT DNAME
FROM dept d
WHERE EXISTS (SELECT *
              FROM emp 
              WHERE DEPTNO = d.DEPTNO);

CORRELATED SUBQUERIES (상관 서브 쿼리)

서브쿼리가 외부 쿼리의 컬럼을 참조하는 경우를 말하며 서브쿼리는 외부 쿼리의 각 행에 대해 반복적으로 실행된다.

외부쿼리 컬럼 참조 -> 행바다 서브쿼리가 실행된다.

ex)

 -- 각 부서에서 가장 높은 급여를 받는 사원의 모든 내용을 출력 해보자.
  SELECT  *
  FROM EMP E1
  WHERE  SAL  =  (
                  SELECT MAX(SAL)
                  FROM EMP  E2
                  WHERE  E1.DEPTNO = E2.DEPTNO -- E1.DEPTNO 주쿼리 = 외부쿼리
                 ) ;

위 코드의 동작 원리는 아래와 같다.

1. EMP 테이블의 첫번째 행 (E1)의 행을 읽음 ( E1.DETPNO = 10 )
2. 서브 쿼리 실행 MAX(SAL) = 5000
3. 주쿼리 행의 SAL과 비교 E1.SAL = 5000 이면 통과, 아니면 제외
4. EMP 테이블의 두번째 행 (E1)의 행을 읽음 ( E1.DETPNO = 20 )
5. 서브 쿼리 실행 MAX(SAL) = 100000;
6. 주쿼리 행의 SAL과 비교 E1.SAL = 100000이면 통과 , 아니면 제외
....
행마다 다른 DEPTNO가 들어가있다.-> 서브쿼리가 매번 다른 결과를 낸다 -> 결과에 따라 주쿼리 조건이 동적으로 계산된다.

ex) 상관 쿼리를 조인으로 바꾸어보기

SELECT * 
FROM EMP E1 
JOIN (SELECT DEPTNO,MAX(SAL) AS MAX_SAL FROM EMP GROUP BY DEPTNO ) E2  ON E1.DEPTNO = E2.DEPTNO AND E1.SAL = E2.MAX_SAL;

ex) 윈도우 함수로 바꾸어보기

SELECT ENAME , SAL , DEPTNO 
FROM (  
SELECT ENAME , SAL , DEPTNO , RANK() OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC) AS RNK   FROM EMP
         ) T
WHERE RNK = 1; -- 급여가 1위인 사람만 출력

ex) 아래와 같은 경우는 안됨

 SELECT  *
  FROM EMP E1
  WHERE  SAL  =  (
                  SELECT MAX(SAL)
                  FROM EMP  E2
                  JOIN E1 USING(DEPTNO)
                 );

[에러메세지]

Error Code: 1146. Table 'my_emp.e1' doesn't exist 0.000 sec

위는 서브쿼리가 외부쿼리의 별칭을 직접 참조 할 수 없다.

'🦁멋쟁이 사자처럼 15기 > 3월달 수업 내용 정리' 카테고리의 다른 글

멋쟁이 사자처럼 22회차 ( 03 /31 ) (0)	2025.03.31
멋쟁이 사자처럼 20회차 ( 03 / 27 ) (0)	2025.03.27
멋쟁이 사자처럼 19회차 ( 03 / 26 ) (0)	2025.03.26
멋쟁이 사자처럼 17회차 ( 03 / 21 ) (0)	2025.03.21
멋쟁이 사자처럼 16회차 ( 03 / 20 ) (0)	2025.03.20

현재글멋쟁이 사자처럼 21회차 ( 03 / 28 )

codinghippo702

etc thread, DB 연동, crud, 백준 11659, 에디터 (1406), Aggregation, threadgroup, nosql, Partition, CallableStatement, 배열과 리스트, URI, MongoDB, Virtual Thread, 평균 (1546), 알파벳 개수 (10808), 백준, 숫자의 합 (11720), 구간합, 구간합 구하기,

Today :
Yesterday :

codinghippo702