[데이터베이스개론·SQL] 250120

이슬비·2025년 1월 20일

Database sql

데이터베이스개론·SQL

목록 보기

5/9

서브쿼리(Subquery)

SQL문 안에 포함된 또 다른 SQL문이다. 메인쿼리에 값을 제공하거나 조건을 지정하는 역할을 한다.

서브쿼리 분류로는 다음과 같다.

연관 여부에 따른 분류 : 비연관 서브쿼리, 연관 서브쿼리.
위치에 따른 분로 : 스칼라 서브쿼리, 인라인 뷰, 중첩 서브쿼리.
반환값 유형에 따른 분류 : 단일 행 서브쿼리, 다중행 서브쿼리, 다중열 서브쿼리.

비연관 서브쿼리

서브쿼리가 독립적으로 실행되어 결과를 반환한다. 메인쿼리와 서브쿼리 간 상호작용이 없다.

--평균 급여보다 높은 급여를 받는 직원 조회
SELECT NAME
FROM EMPLOYEES
WHERE SALARY > (
  SELECT AVG(SALARY)
  FROM EMPLOYEES
);

연관 서브쿼리

서브쿼리가 메인쿼리의 각 행마다 실행된다. 메인쿼리의 데이터를 서브쿼리에서 참조하게 된다.

--동일한 부서에서 급여가 가장 높은 직원 조회
SELECT NAME
FROM EMPLOYEES E1
WHERE SALARY = (
  SELECT MAX(SALARY)
  FROM EMPLOYEES E2
  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
);

스칼라 서브쿼리

서브쿼리가 컬럼 값으로 반환되어 SELECT 절에서 사용한다. 주로 집계 값이나 계산 결과를 포함할 때 활용한다.

--각 직원의 급여와 해당 부서의 평균 급여를 조회
SELECT NAME, SALARY,
  (SELECT AVG(SALARY)
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = E.DEPARTMENT_ID) AS AVG_SALARY
FROM EMPLOYEES E;

인라인 뷰

FROM 절에서 서브쿼리 결과를 테이블처럼 사용한다. 별칭을 붙여 활용이 가능하다.

--부서별 평균 급여를 계산하고 각 부서의 이름과 평균 급여를 조회
SELECT D.DEPARTMENT_NAME, T.AVG_SALARY
FROM DEPARTMENTS D,
  (SELECT DEPARTMENT_ID, AVG(SALARY) AS AVG_SALARY
  FROM EMPLOYEES
  GROUP BY DEPARTMENT_ID) T
WHERE D.DEPARTMENT_ID = T.DEPARTMENT_ID;

중첩 서브쿼리

WHERE 절에서 조건을 설정하기 위해 서브쿼리를 사용한다. 서브쿼리 결과에 따라 메인쿼리의 필터링을 결정한다.

--특정 부서에 속한 직원 조회
SELECT NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (
  SELECT DEPARTMENT_ID
  FROM DEPARTMENTS
  WHERE DEPARTMENT_NAME = 'Department 1');

HAVING 절에서 조건을 설정하기 위해 집계 함수와 함께 활용한다.

--평균 급여 이상인 부서의 총 급여 조회
SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL_SALARY
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING SUM(SALARY) > (
  SELECT AVG(SUM(SALARY))
  FROM EMPLOYEES
  GROUP BY DEPARTMENT_ID);

단일 행 서브쿼리

서브쿼리가 단일 행과 단일 열을 반환한다. 비교 연산자와 함께 사용이 가능하다.

--가장 높은 급여를 받는 직원 조회
SELECT NAME
FROM EMPLOYEES
WHERE SALARY = (
  SELECT MAX(SALARY)
  FROM EMPLOYEES
);

다중 행 서브쿼리

서브쿼리가 여러 행을 반환한다. 다중 행 연산자와 함께 사용한다.

--특정 부서들에 속한 직원 조회
SELECT NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (
  SELECT DEPARTMENT_ID
  FROM DEPARTMENTS
  WHERE LOCATION = 'New York'
);

  --특정 부서와 직무를 가진 직원 조회
SELECT NAME
FROM EMPLOYEES
WHERE (DEPARTMENT_ID, JOB_ID) IN (
  SELECT DEPARTMENT_ID, JOB_ID
  FROM JOB_HISTORY
);

서브쿼리 사용 시 유의사항

성능 최적화
- 비연관 서브쿼리는 독립적으로 실행되므로 더 빠름.
- 연관 서브쿼리는 메인 쿼리의 각 행마다 실행되므로 성능 저하 가능.
적절한 반환값 확인
- 서브쿼리가 반환하는 값의 유형(단일 값, 다중 값)에 따라 연산자 선택이 중요.
JOIN 대체 가능성
- 일부 서브쿼리는 JOIN으로 대체할 수 있어 성능 개선 가능.

서브쿼리 예제

--가장 많은 잔액을 가진 계좌의 잔액과 계좌 ID를 조회
SELECT ACCOUNT_ID, BALANCE
FROM ACCOUNTS
WHERE BALANCE = (
  SELECT MAX(BALANCE)
  FROM ACCOUNTS
);

--고객 중 가장 최근에 가입한 고객의 이름과 가입 날짜를 조회
SELECT NAME, CREATED_AT
FROM CUSTOMERS
WHERE CREATED_AT = (
  SELECT MAX(CREATED_AT)
  FROM CUSTOMERS
);

--잔액이 평균 잔액보다 높은 계좌를 조회
SELECT ACCOUNT_ID, BALANCE
FROM ACCOUNTS
WHERE BALANCE > (
  SELECT AVG(BALANCE)
  FROM ACCOUNTS
);

--각 지점에서 대출 금액이 가장 높은 대출을 조회
SELECT BRANCH_ID, LOAN_ID, AMOUNT
FROM LOANS L1
WHERE AMOUNT = (
  SELECT MAX(AMOUNT)
  FROM LOANS L2
  WHERE L1.BRANCH_ID = L2.BRANCH_ID
);

--각 계좌의 거래 중에서 가장 큰 거래 금액을 조회
SELECT ACCOUNT_ID, TRANSACTION_ID, AMOUNT
FROM TRANSACTIONS T1
WHERE AMOUNT = (
  SELECT MAX(AMOUN)
  FROM TRANSACTIONS T2
  WHERE T1.ACCOUNT_ID = T2.ACCOUNT_ID
);

집합연산자

SQL에서 두 개 이상의 SELECT 결과를 결합하여 하나의 결과 집합을 생성한다. 데이터베이스에서 집합 이론을 기반으로 작동하며, 서로 다른 쿼리의 결과를 비교하거나 결합할 때 유용하다.

`UNION`

두 SELECT 결과를 합친 뒤 중복된 행은 제거한다. 결과는 정렬된 상태로 반환된다.

SELECT 컬럼명 FROM 테이블1
UNION
SELECT 컬럼명 FROM 테이블2;

ALIAS 사용 시 첫 번째 테이블에 적용한 명이 사용된다.

`UNION ALL`

두 SELECT 결과를 합치며 중복된 행도 포함한다. 결과는 정렬되지 않는다.

SELECT 컬럼명 FROM 테이블1
UNION ALL
SELECT 컬럼명 FROM 테이블2;

`INTERSECT`

두 SELECT 결과의 교집합을 반환하며 중복된 행은 하나만 반환한다.

SELECT 컬럼명 FROM 테이블1
INTERSECT
SELECT 컬럼명 FROM 테이블2;

`MINUS`

첫 번째 SELECT 결과에서 두 번째 SELECT 결과를 제외한 차집합을 반환한다. 중복된 행은 하나만 반환한다.

SELECT 컬럼명 FROM 테이블1
MINUS
SELECT 컬럼명 FROM 테이블2;

집합연산자 사용 시 유의사항

SELECT 절에 나열된 컬럼 수와 데이터 타입이 동일해야 함.
결과 집합의 순서는 기본적으로 보장되지 않음.
ORDER BY는 최종 결과에만 적용 가능.
대량의 데이터 처리 시 성능에 유의해야 함.
ALIAS는 맨 처음 SELECT 절을 따름.

집합연산자 예제

  --CUSTOMERS와 LOANS에서 공통적으로 존재하는 고객 ID를 조회
  SELECT CUSTOMER_ID FROM CUSTOMERS
  INTERSECT
  SELECT CUSTOMER_ID FROM LOANS;

  --TRANSACTIONS에서 거래 유형과 ACCOUNT의 계좌 유형을 중복 없이 결합하여 조회
  SELECT TRANSACTION_TYPE FROM TRANSACTIONS
  UNION
  SELECT ACCOUNT_TYPE FROM ACCOUNTS;

윈도우 함수

행(Row)을 기준으로 특정 범위(Window)를 정의하여 작업하는 함수이다. 집계 함수와 함께 데이터를 그룹핑하지 않고도 계산 가능하다.

순위 함수

RANK : 순위를 계산하며 동일한 값은 같은 순위를 부여 (순위 건너뜀).
DENSE_RANK : 순위를 계산하며 동일한 값은 같은 순위를 부여 (순위 건너뛰지 않음).
ROW_NUMBER : 순위를 고유하게 매김 (중복 없음).

SELECT 컬럼명,
  RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼 ASC) AS rank,
  DENSE_RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼 ASC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY 컬럼 ORDER BY 컬럼 ASC) AS row_number
FROM 테이블명;

--EMPLOYEES 테이블에서 급여(SALARY) 순으로 순위를 계산
SELECT EMPLOYEE_ID, NAME, SALARY,
  RANK() OVER (ORDER BY SALARY DESC) AS rank
FROM EMPLOYEES;

--EMPLOYEES 테이블에서 급여(SALARY) 순으로 순위를 계산 - NULL 값 제외
SELECT EMPLOYEE_ID, SALARY,
  RANK() OVER (ORDER BY SALARY DESC) AS rank
FROM EMPLOYEES
WHERE SALARY IS NOT NULL;

--순위가 1위인 값들만 계산
SELECT * FROM (
  SELECT EMPLOYEE_ID, SALARY,
  RANK() OVER (ORDER BY SALARY DESC) AS rank
  FROM EMPLOYEES
  WHERE SALARY IS NOT NULL
 )
 WHERE rank = 1;

--EMPLOYEES 테이블에서 급여(SALARY) 순으로 밀집 순위를 계산
SELECT EMPLOYEE_ID, NAME, SALARY,
  DENSE_RANK() OVER (ORDER BY SALARY DESC) AS dense_rank
FROM EMPLOYEES;

--EMPLOYEES 테이블에서 급여(SALARY) 순으로 고유한 순위 계산
SELECT EMPLOYEE_ID, NAME, SALARY,
  ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS row_number
FROM EMPLOYEES;

`SUM` 함수

--부서별 급여 합계 계산
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
  SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS DEPT_TOTAL
FROM EMPLOYEES;

--부서별 급여 누적합계 계산
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
  SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS DEPT_TOTAL
FROM EMPLOYEES;

`AVG` 함수

--부서별 평균 급여를 계산
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
  AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS DEPT_AVG
FROM EMPLOYEES;

`MAX` 함수

--부서별 최대 급여를 계산
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
  MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS DEPT_MAX
FROM EMPLOYEES;

SELECT * FROM (
  SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
    MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS DEPT_MAX
    FROM EMPLOYEES
)
WHERE DEPT_MAX = SALARY;

`MIN` 함수

--부서별 최소 급여를 계산
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
  MIN(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS DEPT_MIN
FROM EMPLOYEES;

`COUNT` 함수

--부서별 직원 수를 계산
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
  COUNT(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS DEPT_COUNT
FROM EMPLOYEES;

`PARTITION BY`와 `ORDER BY`

PARTITION BY
- 데이터를 특정 그룹으로 나눔.
- 그룹별로 윈도우 함수가 작동.
ORDER BY
- 데이터를 정렬하여 윈도우 함수의 동작 순서를 지정.
- 누적 계산에 주로 사용.

Quiz
윈도우 함수의 OVER 절에 아무것도 없는 경우 SQL 실행이 어떻게될까?
SELECT STATUS, LOAN_ID,
  COUNT(LOAN_ID) OVER () AS STATUS_COUNT
FROM LOANS;
특정 그룹 없이 전체 데이터를 대상으로 집계 함수가 작동된다.

이슬비

이전 포스트

[데이터베이스개론·SQL] 250116

다음 포스트

[데이터베이스개론·SQL] 250120