EXISTS와 NOT EXISTS를 사용한 서브 쿼리 예제 및 실무 활용

1. 핵심 원리

서브 쿼리에서 EXISTS와 NOT EXISTS는 특정 조건에 따라 다른 테이블의 데이터 존재 여부를 확인하는 데 사용됩니다. EXISTS는 서브 쿼리의 결과가 존재하면 참(True)을 반환하고, NOT EXISTS는 서브 쿼리의 결과가 존재하지 않으면 참(True)을 반환합니다.

2. 실무에서 쓰이는 이유

실무에서는 데이터 무결성을 검증하거나 참조 무결성을 유지하기 위해 EXISTS와 NOT EXISTS를 자주 사용합니다. 대규모 데이터셋에서 빠르게 데이터의 존재 여부를 확인할 수 있어 유용합니다.

3. 실무에서 구체적으로 사용되는 상황 예측

고객 관리 시스템에서 특정 고객이 주문 기록에 있는지 확인할 때.
인사 관리 시스템에서 특정 부서에 소속된 직원이 있는지 확인할 때.
재고 관리 시스템에서 특정 제품이 판매된 이력이 있는지 확인할 때.

4. 자주 사용되는 윈도우 표시 및 설명

이번 학습의 EXISTS와 NOT EXISTS에 관련된 윈도우 함수로 ROW_NUMBER()와 DENSE_RANK()가 있습니다. 이러한 함수는 특정 조건에 따라 데이터에 순위를 매기거나 고유한 식별자를 할당할 때 사용됩니다.

ROW_NUMBER(): 데이터셋 내의 각 행에 고유한 번호를 부여하며, 동일한 순위를 갖는 행이 없습니다.

  SELECT ename, sal, ROW_NUMBER() OVER(ORDER BY sal DESC) AS row_num
  FROM emp;

DENSE_RANK(): 동일한 값을 가진 행에 동일한 순위를 부여하며, 연속된 순위를 유지합니다.

  SELECT ename, sal, DENSE_RANK() OVER(ORDER BY sal DESC) AS rank
  FROM emp;

5. 연습 문제

EMP 테이블에서 DEPT 테이블에 존재하는 부서에 속한 사원들의 정보를 출력하는 SQL 쿼리를 작성하시오.
EMP 테이블에서 DEPT 테이블에 존재하지 않는 부서에 속한 사원들의 정보를 출력하는 SQL 쿼리를 작성하시오.

6. 연습 문제 해답 및 테이블 표시

EMP 테이블에서 DEPT 테이블에 존재하는 부서에 속한 사원들의 정보를 출력하는 SQL 쿼리:

   SELECT *
   FROM emp e
   WHERE EXISTS (SELECT *
                 FROM dept d
                 WHERE e.deptno = d.deptno);

   EMPNO  ENAME  JOB       DEPTNO
   7839   KING   PRESIDENT 10
   7566   JONES  MANAGER   20

EMP 테이블에서 DEPT 테이블에 존재하지 않는 부서에 속한 사원들의 정보를 출력하는 SQL 쿼리:

   SELECT *
   FROM emp e
   WHERE NOT EXISTS (SELECT *
                     FROM dept d
                     WHERE e.deptno = d.deptno);

   EMPNO  ENAME  JOB      DEPTNO
   7788   SCOTT  ANALYST  30

7. 응용 문제

특정 사원이 소속된 부서에 다른 사원들이 존재하는지 확인하고, 존재하지 않으면 해당 사원의 부서 정보를 출력하는 SQL 쿼리를 작성하시오.

8. 응용 문제 해답 및 테이블 표시

특정 사원이 소속된 부서에 다른 사원들이 존재하지 않는 경우 해당 부서 정보를 출력하는 SQL 쿼리:

SELECT *
FROM dept d
WHERE NOT EXISTS (SELECT *
                  FROM emp e
                  WHERE e.deptno = d.deptno
                  AND e.ename <> 'CLARK');

9. 전자상거래 업계에서 쓰일 수 있는 경우 예측

특정 제품이 현재 판매 중인지 확인하고, 판매 중인 제품의 카테고리 정보를 추출할 때 EXISTS를 사용할 수 있습니다.
고객 관리 시스템에서 특정 고객이 구매 기록이 없는지 확인하고, 구매 기록이 없는 고객의 정보를 출력할 때 NOT EXISTS를 사용할 수 있습니다.

10. 전자상거래 업계에서 사용할 수 있는 경우 예상 상황 문제 2가지 및 그 경우에 대한 SQL 코드 작성

특정 제품이 카테고리에 존재하는지 확인하고, 존재하지 않으면 해당 카테고리 정보를 출력하는 SQL 코드:

   SELECT *
   FROM categories c
   WHERE NOT EXISTS (SELECT *
                     FROM products p
                     WHERE p.category_id = c.category_id);

특정 고객이 구매한 내역이 존재하는지 확인하고, 존재하지 않으면 해당 고객 정보를 출력하는 SQL 코드:

   SELECT *
   FROM customers cu
   WHERE NOT EXISTS (SELECT *
                     FROM orders o
                     WHERE o.customer_id = cu.customer_id);

11. 10번에 대한 SQL 코드를 시각적으로 이해하기 쉽도록 테이블 예시 작성

카테고리와 제품 관련 테이블 예시:

   CATEGORY_ID  CATEGORY_NAME
   1            Electronics
   2            Books
   3            Clothing

   PRODUCT_ID  PRODUCT_NAME  CATEGORY_ID
   101         TV            1
   102         Laptop        1

고객과 주문 관련 테이블 예시:

   CUSTOMER_ID  CUSTOMER_NAME
   201          John Doe
   202          Jane Smith

   ORDER_ID  CUSTOMER_ID  ORDER_DATE
   301       201          2023-01-10

견해 요약: EXISTS와 NOT EXISTS는 복잡한 조건 검증을 간단하고 효율적으로 처리하는 데 필수적인 도구입니다.

또끼

Related Posts