취준/CS지식

상관 서브 쿼리

buddlee 2025. 9. 12. 17:13

공무원 전산직 데이터베이스론 기출 문제를 풀다가 3중 상관서브쿼리가 나와서 화가 나서 정리하면서 작성했습니다. ㅠㅠ

GPT 아니 Gemini야 앞으로 너를 써야겟다... GPT는 제대로 설명을 못해주더라고요... 멍충이....

상관 서브쿼리(Correlated Subquery) 이해 및 문제 풀이

1. 상관 서브쿼리, 도대체 무엇일까요?

일반적인 서브쿼리는 혼자서도 잘 실행됩니다. 

SELECT * FROM 학생 WHERE 학년 = (SELECT MAX(학년) FROM 학생)

이 쿼리에서 (SELECT MAX(학년) FROM 학생) 부분은 독립적으로 실행해서 '4'라는 결과를 내놓고, 바깥 쿼리는 이 '4'라는 값을 받아 사용합니다.

즉, '안쪽 쿼리가 먼저 한 번 실행되고, 그 결과를 바깥 쿼리가 이어받아 사용'하는 구조입니다.

 

 

하지만 상관 서브쿼리는 다릅니다. 이름 그대로 '서로 관련(Correlated)이 있는' 쿼리입니다.

핵심 개념: 안쪽의 서브쿼리가 독립적으로 실행될 수 없고, 바깥쪽 메인 쿼리의 값을 참조하여 실행됩니다.

마치 프로그래밍의 중첩 반복문(nested for loop)과 같습니다.

    for (outer_loop_value in Outer_Table) {
    // outer_loop_value를 가지고
    for (inner_loop_value in Inner_Table) {
        if (condition using outer_loop_value) {
            // ... 어떤 작업을 수행
        }
    }
}

이처럼 바깥쪽 쿼리가 한 행(row)을 읽을 때마다, 그 행의 특정 값(예: 공급업체.업체번호)을 안쪽 서브쿼리로 전달하여 서브쿼리를 '매번 새로 실행'합니다.

2. 문제의 SQL 쿼리 순차적으로 따라가기

 

이제 실제 문제의 쿼리가 컴퓨터 내부에서 어떻게 돌아가는지 한 단계씩 따라가 보겠습니다.

<보기 2>의 SQL문

    SELECT 업체번호, 업체명
FROM 공급업체
WHERE NOT EXISTS (
    SELECT 부품.부품번호
    FROM 부품
    WHERE 부품.색상='빨강' AND EXISTS (
        SELECT *
        FROM 카탈로그
        WHERE 카탈로그.부품번호 = 부품.부품번호
          AND 카탈로그.업체번호 = 공급업체.업체번호
    )
);

먼저, 이 쿼리를 한국어로 번역해 봅시다.

  • 최종 목표: 공급업체 테이블에서 업체번호와 업체명을 찾고 싶다.
  • 조건 (WHERE NOT EXISTS ...): 괄호 안의 서브쿼리를 실행했을 때, 결과가 존재하지 않는 (NOT EXISTS), 즉 결과 행이 하나도 없는 공급업체만 골라내라.
  • 괄호 안 서브쿼리의 의미:
    • 색상이 '빨강'인 부품 중에서... (WHERE 부품.색상='빨강')
    • 현재 바깥쪽에서 검사 중인 바로 그 공급업체(공급업체.업체번호)가 공급하는 부품 (EXISTS (SELECT * FROM 카탈로그 ...)).
  • 한 문장으로 요약: "색상이 '빨강'인 부품을 공급하는 사실이 없는 (즉, 빨간색 부품을 하나도 취급하지 않는) 공급업체를 모두 찾아라."

이제, 이 논리를 가지고 데이터베이스가 일하는 순서대로 시뮬레이션해 보겠습니다.

[1단계] 메인 쿼리가 공급업체 테이블을 한 줄씩 읽기 시작합니다.

가장 먼저 공급업체 테이블의 첫 번째 행인 **'1, 공급A'**를 선택합니다.
이제부터 이 쿼리의 모든 공급업체.업체번호는 임시로 **1**이 됩니다.

[2단계] 공급업체.업체번호 = 1 인 상태로 WHERE절의 서브쿼리를 실행합니다.

이제 아래와 같은 서브쿼리가 실행됩니다. (7번째 줄에서 "카탈로그.업체번호 = 1"의 '1'이 1단계에서 넘어온 값입니다.)

    -- '공급A'가 빨간색 부품을 공급하는가? 를 체크하는 쿼리
SELECT 부품.부품번호
FROM 부품
WHERE 부품.색상='빨강' AND EXISTS (
    SELECT *
    FROM 카탈로그
    WHERE 카탈로그.부품번호 = 부품.부품번호
      AND 카탈로그.업체번호 = 1
)
  1. FROM 부품 WHERE 부품.색상='빨강': 부품 테이블에서 색상이 '빨강'인 것을 찾습니다.
    • 부품 테이블을 보니 (1, 부품1, 빨강)이 있네요. 부품.부품번호는 **1**입니다.

2.  AND EXISTS (...): 이제 기존의 공급업체.업체번호 =1, 부품.부품번호 = 1 이라는 값을 가지고 맨 안쪽 EXISTS 쿼리를 실행합니다.

  • 카탈로그 테이블을 봅시다. 업체번호=1 이면서 부품번호=1 인 행이 있나요?
  • 카탈로그 테이블에는 (1, 3, 10000), (2, 1, 20000), (3, 2, 30000) 가 있습니다.
  • (업체번호=1, 부품번호=1) 조건에 맞는 행은 없습니다.
        -- '공급A(업체번호=1)'가 '부품1(부품번호=1)'을 공급하는가?
SELECT *
FROM 카탈로그
WHERE 카탈로그.부품번호 = 1
  AND 카탈로그.업체번호 = 1
  1. 결과 판정:
    • 맨 안쪽 EXISTS 쿼리의 결과가 비어있으므로 EXISTS는 FALSE가 됩니다.
    • 따라서 WHERE 부품.색상='빨강' AND FALSE 조건은 만족하는 부품이 없게 됩니다.
    • 결론적으로, [2단계]에서 실행한 서브쿼리는 결과가 비어있습니다 (0개의 행).
  2. 최종 NOT EXISTS 판정:
    • [2단계] 서브쿼리의 결과가 비어있으므로, NOT EXISTS 조건은 TRUE가 됩니다.
    • 최종 결정: (1, 공급A)는 우리가 찾던 결과가 맞습니다! 결과 목록에 추가합니다.

[3단계] 메인 쿼리가 공급업체 테이블의 다음 행을 읽습니다.

이번에는 두 번째 행인 **'2, 공급B'**를 선택합니다.
이제 이 쿼리의 모든 공급업체.업체번호는 임시로 **2**가 됩니다.

[4단계] 공급업체.업체번호 = 2 인 상태로 WHERE절의 서브쿼리를 실행합니다.

    -- '공급B'가 빨간색 부품을 공급하는가? 를 체크하는 쿼리
SELECT 부품.부품번호
FROM 부품
WHERE 부품.색상='빨강' AND EXISTS (
    SELECT *
    FROM 카탈로그
    WHERE 카탈로그.부품번호 = 부품.부품번호
      AND 카탈로그.업체번호 = 2
)
  1. FROM 부품 WHERE 부품.색상='빨강': 똑같이 '빨강' 부품인 부품번호=1을 찾습니다.
  2. AND EXISTS (...): 부품.부품번호 = 1 을 가지고 맨 안쪽 EXISTS 쿼리를 실행합니다.
    • 카탈로그 테이블을 봅시다. 업체번호=2 이면서 부품번호=1 인 행이 있나요?
    • 네, (2, 1, 20000) 행이 존재합니다!
  3. SQL
     
        -- '공급B(업체번호=2)'가 '부품1(부품번호=1)'을 공급하는가?
SELECT *
FROM 카탈로그
WHERE 카탈로그.부품번호 = 1
  AND 카탈로그.업체번호 = 2
  4. 결과 판정:
    • 맨 안쪽 EXISTS 쿼리의 결과가 존재하므로 EXISTS는 TRUE가 됩니다.
    • 따라서 WHERE 부품.색상='빨강' AND TRUE 조건은 만족됩니다.
    • 결론적으로, [4단계]에서 실행한 서브쿼리는 부품번호 '1'을 결과로 가집니다. 즉, 결과가 비어있지 않습니다.
  5. 최종 NOT EXISTS 판정:
    • [4단계] 서브쿼리의 결과가 존재하므로, NOT EXISTS 조건은 FALSE가 됩니다.
    • 최종 결정: (2, 공급B)는 우리가 찾던 결과가 아닙니다. 결과 목록에서 제외합니다.

[5단계] 메가인 쿼리가 공급업체 테이블의 마지막 행을 읽습니다.

마지막 행인 **'3, 공급C'**를 선택합니다. 공급업체.업체번호는 **3**이 됩니다.

[6단계] 공급업체.업체번호 = 3 인 상태로 서브쿼리를 실행합니다.

    -- '공급C'가 빨간색 부품을 공급하는가? 를 체크하는 쿼리
SELECT 부품.부품번호
FROM 부품
WHERE 부품.색상='빨강' AND EXISTS (
    SELECT *
    FROM 카탈로그
    WHERE 카탈로그.부품번호 = 부품.부품번호
      AND 카탈로그.업체번호 = 3
)
  1. FROM 부품 WHERE 부품.색상='빨강': 똑같이 '빨강' 부품인 부품번호=1을 찾습니다.
  2. AND EXISTS (...): 부품.부품번호 = 1 을 가지고 맨 안쪽 EXISTS 쿼리를 실행합니다.
    • 카탈로그 테이블을 봅시다. 업체번호=3 이면서 부품번호=1 인 행이 있나요?
    • 없습니다.
  3. SQL
     
        -- '공급C(업체번호=3)'가 '부품1(부품번호=1)'을 공급하는가?
SELECT *
FROM 카탈로그
WHERE 카탈로그.부품번호 = 1
  AND 카탈로그.업체번호 = 3
  4. 결과 판정:
    • 맨 안쪽 EXISTS 쿼리 결과가 비어있으므로 EXISTS는 FALSE가 됩니다.
    • [6단계] 서브쿼리는 결과가 비어있습니다.
  5. 최종 NOT EXISTS 판정:
    • 서브쿼리의 결과가 비어있으므로, NOT EXISTS 조건은 TRUE가 됩니다.
    • 최종 결정: (3, 공급C)는 우리가 찾던 결과가 맞습니다! 결과 목록에 추가합니다.

3. 최종 결과 및 정답

지금까지의 과정을 종합하면, 최종 결과 목록에는 다음이 포함됩니다.

  • (1, 공급A)
  • (3, 공급C)

따라서 정답은 ③번 입니다.

합격 Tip!

  1. SQL을 한국어로 먼저 해석하는 습관을 들이자! 운이 좋으면 일일히 SQl을 안봐도 빠르게 문제가 풀릴지도?
  2. 상관 서브쿼리가 보이면 중첩 반복문을 떠올리자. 쿼리라 생각하니까 헷갈리지만 반복문이라 생각하니까 이해하기가 쉽네 ㅠㅠ
  3. EXISTS vs IN: EXISTS는 서브쿼리의 결과가 '존재하는지 여부(True/False)'만 따집니다. 결과 내용이 무엇인지는 중요하지 않고, 한 행이라도 있으면 바로 True를 반환하므로 대용량 데이터에서 성능이 더 좋은 경우가 많습니다. 

 

위 내용은 GPT가 작성한 내용을 몇 부분만 수정했지만 혹시나 도움이 되실분들이 있을까 싶어서 올립니다.




'취준/CS지식'의 다른글

  • 현재글상관 서브 쿼리

티스토리툴바