본문 바로가기
프로그래밍 언어/SQL

SQL 활용

by Jinger 2025. 5. 24.

서브쿼리

    서브쿼리(Subquery)는 SELECT, INSERT, UPDATE, DELETE 등의 SQL문 내부에서 값을 계산하거나 조건으로 사용하기 위해 포함된 또 다른 SELECT문이다. 즉, 다른 쿼리 안에 포함된 쿼리, 즉 "쿼리 속의 쿼리" 를 의미한다. 보통 괄호 ()로 감싸며, 외부 쿼리(main query)가 서브쿼리의 결과를 사용한다. 서브쿼리 위치에 따라서도 아래와 같이 나뉘기도 한다.

SELETE 스칼라 서브쿼리
FROM 인라인 뷰
WHERE, HAVING 중첩 서브쿼리

스칼라 서브쿼리 (Scalar Subquery)

   결과가 오직 하나의 값(1행 1열)만 나오는 서브쿼리로 칼럼을 대신하여 사용된다. 주로 SELECT, WHERE, HAVING 절 등에서 값처럼 사용된다. 주된 특징으로는 반드시 하나의 값만 반환하며, 산술식, 조건식, 컬럼처럼 다룰 수 있다.

SELECT name,
       salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

 

    위 예시에서 (SELECT AVG(salary) ...)가 스칼라 서브쿼리이다.

인라인 뷰 (Inline View)

    FROM절에 쓰이는 서브쿼리 기반의 임시 테이블 임시 테이블처럼 별칭을 붙여 사용하며, 그 위에 WHERE, GROUP BY, ORDER BY 등 사용 가능하다. 인라인 뷰는 뷰처럼 사용되지만 CREATE 없이 일시적으로 정의된다. 주로 서브쿼리 결과를 기준으로 추가 조건, 정렬 등을 처리한다.

SELECT name, total_salary
FROM (
    SELECT name, salary * 12 AS total_salary
    FROM employees
) AS yearly
WHERE total_salary > 50000;

    위 예시에 내부 SELECT가 인라인 뷰이다.

중첩 서브쿼리(Subquery)

   중첩 서브쿼리는 메인 쿼리 내부에 포함된 SELECT 문으로, 메인 쿼리의 조건이나 컬럼 값을 구하는 데 사용된다. 괄호로 감싸고, 내부에서 결과를 반환하여 메인 쿼리에 전달한다. 중첩 서브쿼리는 또 메인쿼리와의 관계와 데이터와의 관계에 따라 분류할 수 있다.

메인 쿼리와의 관계 기준

비연관 서브쿼리 (Non-Correlated Subquery)

   비연관 서브쿼리는 메인 쿼리의 컬럼을 참조하지 않는 서브쿼리를 의미한다. 특징으로 한 번만 실행되고, 결과를 메인 쿼리가 재사용하는 것이 있다.

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

   비연관 서브쿼리의 예시를 보면 평균 급여는 한 번만 계산, 이후 각 직원의 급여와 비교하는 것을 볼 수 있다.

연관 서브쿼리 (Correlated Subquery)

   연관 서브쿼리는 서브쿼리가 메인 쿼리의 컬럼을 참조하는 서브쿼리를 의미한다. 비연관 서브쿼리와 달리 메인 쿼리의 각 행마다 서브쿼리가 반복 실행된다.

SELECT name
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE dept_id = e.dept_id
);

 

반환되는 데이터의 형태 기준

단일 행 서브쿼리 (Scalar / Single Row Subquery)

   단일 행 서브쿼리는 서브쿼리의 1건 이하의를 반환하는 서브쿼리이다. 메인 쿼리에서 일반 값처럼 사용하며, 비교연산자(=, <, >, <=) 등을 사용할 수 있다. 결과가 1건 이하이기에 1열 1행으로 출력되는 특징이 있다.

SELECT name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

 

다중 행 서브쿼리 (Multi Row Subquery)

   다중 행 서브쿼리는 여러 건의 데이터를 반환하는 서브쿼리이다. 다중 행 비교 연산자 (IN, ANY, ALL)와 함께 사용해야 할 수 있다. 결과가 여러 개이므로 여러 행으로 출력된다.

SELECT name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = '서울');

다중 컬럼 서브쿼리 (Multi Column Subquery)

   다중 컬럼 서브쿼리는 결과가 여러 열(컬럼)으로 반환하는 서브쿼리이다. 예를 들어 (col1, col2) 형태로 조건 비교한다.

SELECT name
FROM employees
WHERE (dept_id, job_id) IN (
  SELECT dept_id, job_id FROM job_rules WHERE level = '상급'
);

 

뷰

     뷰는 하나 이상의 테이블 또는 다른 뷰에서 SELECT한 결과를 저장한 가상의 테이블이다. 즉, SELECT 쿼리 결과를 이름 붙여 저장한 객체이다. 실제로 데이터를 저장하진 않지만, 기존 테이블을 간편하게 재사용하고, 보안성과 유지보수성을 높이는 데 유용하게 사용된다.

뷰의 특징

  • 데이터 저장 X → 테이블처럼 조회는 가능하지만, 실제 데이터는 테이블에 존재함
  • 동적으로 반영 → 원본 테이블 데이터가 바뀌면, 뷰 결과도 함께 바뀜
  • 보안에 유리 → 민감한 컬럼을 숨긴 뷰만 사용자에게 제공 가능
  • 복잡한 쿼리 재사용 가능 → 유지보수 편리

뷰 생성

CREATE VIEW 뷰이름 AS
SELECT문;

 

 

뷰 수정

CREATE OR REPLACE VIEW 뷰이름 AS
SELECT문;

 뷰 삭제

DROP VIEW 뷰이름;

종류

일반 뷰 (Simple View) 단일 테이블 기반, 집계 없이 조회
복합 뷰 (Complex View) JOIN, 집계함수 등 포함 가능
업데이트 가능한 뷰 일부 뷰는 INSERT, UPDATE, DELETE 가능 (단, 제약 존재)
읽기 전용 뷰 GROUP BY, DISTINCT, JOIN이 있으면 갱신 불가

주의사항

  • 뷰는 원본 테이블이 변경되면 무효화될 수 있음 (컬럼 삭제 등)
  • 뷰 기반으로 또 다른 뷰 생성 가능 (중첩 뷰)
  • 성능 측면에서는 무분별한 중첩 뷰 사용 지양 (실행 시마다 원본 쿼리 수행)

집합 연산자

   집합 연산자란 여러 개의 SELECT 쿼리 결과를 하나로 결합하거나 공통된 부분만 추출하거나 차집합을 구할 때 사용하는 연산자이다.  집합 연산자를 사용하려면 두 SELECT문의 결과가 같은 컬럼 수와 데이터 타입 순서를 가져야 한다. 집합 연산자의 특징은 결과에는 정렬이 없다. ORDER BY에 경우 마지막 SELECT 뒤에서 전체에 적용한다.

SELECT name FROM table1  
UNION  
SELECT name FROM table2;

→ OK (컬럼 개수 같음)

SELECT name, age FROM table1  
UNION  
SELECT name FROM table2;

→ ❌ 오류 (컬럼 개수 불일치)

UNION ALL – 합집합 (중복 포함)

   합집합은 두 SELECT 결과를 중복된 행 포함 있는 그대로 합치는 집합 연산이다.

SELECT name FROM a
UNION ALL
SELECT name FROM b;

→ a와 b의 모든 데이터를 중복 포함하여 연결

UNION – 합집합 (중복 제거)

   UNION ALL과 비슷하지만, 중복을 자동으로 제거해 합치는 집합 연산이다. UNION은 자동 정렬과 중복 제거가 있기 때문에 UNION ALL보다 느릴 수 있다.

SELECT name FROM a
UNION
SELECT name FROM b;

→ a와 b에 모두 있는 값은 한 번만 출력

INTERSECT – 교집합

   두 SELECT 결과에 모두 포함된 행만 반환하는 집합 연산이다. 일부 DBMS(MySQL 등)는 지원하지 않는다.

SELECT name FROM a
INTERSECT
SELECT name FROM b;

→ a와 b에 공통으로 존재하는 name만 출력

MINUS / EXCEPT – 차집합

   첫 번째 SELECT에는 있고, 두 번째에는 없는 행만 반환하는 집합 연산이다. Oracle에서는 MINUS를 PostgreSQL/SQL Server에서는 EXCEPT를 사용한다.

-- Oracle
SELECT name FROM a
MINUS
SELECT name FROM b;

-- PostgreSQL / SQL Server
SELECT name FROM a
EXCEPT
SELECT name FROM b;

→ a에는 있지만 b에는 없는 name만 출력

 

그룹 함수

   그룹 함수는 그룹별 집계 결과를 한 단계 더 확장하거나, 그룹 내 순서를 지정하거나, 집계 구분을 표시할 때 사용된다.

ROLLUP – 단계별 집계

    GROUP BY의 확장 기능을 가지고 있다. 그룹화된 컬럼을 전체 소계 및 총합까지 포함하여 계층적으로 요약한다. 

SELECT 부서, 직급, SUM(급여)
FROM employees
GROUP BY ROLLUP(부서, 직급);

→ 결과:

  • 부서 + 직급 단위 급여 합계
  • 부서별 전체 합계
  • 전체 총합까지 자동 포함

CUBE – 다차원 집계

   ROLLUP은 위→아래 방향 요약이라면, CUBE는 모든 조합으로 집계 모든 가능한 그룹 조합의 합계를 자동으로 계산한다.

SELECT 부서, 직급, SUM(급여)
FROM employees
GROUP BY CUBE(부서, 직급);

→ 결과:

  • 부서+직급 조합
  • 부서별 합계
  • 직급별 합계
  • 전체 총합
    → 4가지 조합 전부 포함됨

GROUPING – 소계/총계 여부 확인

    GRUOPING은 ROLLUP, CUBE 사용 시, 해당 행이 일반 그룹인지 소계/총계인지 구분하는 함수이다. 단, 소계/총계 컬럼은 NULL이 되므로 구분 필요하다.

SELECT 부서, 직급,
       SUM(급여) AS 합계,
       GROUPING(부서) AS 부서그룹,
       GROUPING(직급) AS 직급그룹
FROM employees
GROUP BY ROLLUP(부서, 직급);

→ GROUPING() 결과가 1이면 소계/총계
→ 부서그룹=1, 직급그룹=1이면 전체 총계

ROW_NUMBER() – 행 번호 부여 (윈도우 함수)

 ROW_NEMBR은 그룹 내 순번을 부여하는 함수이다. 보통 OVER(PARTITION BY ... ORDER BY ...)와 함께 사용 특정 그룹에서 상위 N개 추출할 때 유용하다.

SELECT name, dept_id, salary,
       ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees;

→ 각 부서별 급여 순위를 부여 (1, 2, 3...)

윈도우 함수

   윈도우 함수는 집계 함수와 달리 그룹 전체를 한 번에 요약하지 않고, 각 행마다 그룹 내 계산 결과를 함께 보여주는 함수이다.

OVER() 절을 이용해 지정한 윈도우(범위) 내에서 연산을 수행하여 집계 결과를 각 행 단위로 출력해준다. (집계 + 행 유지)

순위 함수 (Ranking Functions)

   그룹 내에서 순위를 부여할 때 사용된다. 모두 OVER(PARTITION BY ... ORDER BY ...)와 함께 사용한다.

ROW_NUMBER() 순서대로 고유한 번호 부여 (중복 없음) 1, 2, 3...
RANK() 같은 값은 같은 순위, 다음은 건너뜀 1, 1, 3...
DENSE_RANK() 같은 값은 같은 순위, 다음 순위는 연속됨 1, 1, 2... 
SELECT name, salary,
       RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS 순위
FROM employees;

 

집계 함수 (Aggregate as Window)

    기존 SUM, AVG, COUNT, MIN, MAX 등을 윈도우 함수로 사용 가능하다.

SELECT name, salary,
       AVG(salary) OVER(PARTITION BY dept_id) AS 부서평균급여
FROM employees;

행 순서 함수 (Navigation / Offset Functions)

LAG() 이전 행의 값 참조 LAG(salary, 1) → 바로 앞 급여
LEAD() 다음 행의 값 참조 LEAD(name, 1) → 다음 사람 이름
FIRST_VALUE() 윈도우 내 첫 번째 값 부서 내 가장 먼저 입사한 사람
LAST_VALUE() 윈도우 내 마지막 값 부서 내 가장 마지막 입사자 
SELECT name, salary,
       LAG(salary, 1) OVER(ORDER BY salary) AS 이전급여
FROM employees;

 

비율 함수 (Ratio Functions)

NTILE(n) n등분 구간으로 나누기 (분위수 등)
PERCENT_RANK() 백분율 순위 (0 ~ 1)
CUME_DIST() 누적 비율 (해당 행 이하의 비율) 
SELECT name, salary,
       CUME_DIST() OVER(ORDER BY salary) AS 누적비율
FROM employees;

 

 

Top-N 쿼리

   특정 조건에 따라 상위 N개의 데이터만 조회할 때 사용하는 기법으로, 과거에는 ROWNUM, 지금은 윈도우 함수의 순위 함수(ROW_NUMBER, RANK, DENSE_RANK)를 주로 사용한다.

ROWNUM(Oracle 전통 방식)

    ROWNUM은 Oracle 전용 문법으로SELECT 결과에 번호를 자동으로 붙인다. WHERE절에서 ROWNUM <= N 조건으로 상위 N개를 필터링하여 사용한다. ROWNUM은 정렬 전에 부여되므로, 반드시 서브쿼리로 정렬 먼저 수행 후 ROWNUM 필터링해야한다.

SELECT *
FROM (
  SELECT * FROM employees ORDER BY salary DESC
)
WHERE ROWNUM <= 3;

윈도우 함수(표준 방식)

   모든 RDBMS에서 사용 가능한 함수이다. ROW_NUMBER()는 정렬 순서대로 고유한 번호 부여한다. WHERE rn <= N 또는 WHERE 순위 = 1 형태로 사용한다.

SELECT *
FROM (
  SELECT name, salary,
         ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn
  FROM employees
) AS ranked
WHERE rn <= 3;

 

셀프 조인 (Self Join)

   셀프 조인은 하나의 테이블을 자기 자신과 조인해서 사용하는 방식이다. 즉, 같은 테이블을 두 번 참조하여 행과 행 사이의 관계를 표현할 수 있게 한다. 주로 같은 테이블 내에서 다른 행과 비교할 때 사용한다.

SELECT e.name AS 직원명, m.name AS 상사명
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
자기 참조 같은 테이블의 다른 행과 연결됨
별칭 필요 같은 테이블을 두 번 이상 쓰기 때문에 반드시 별칭(Alias) 필요
JOIN 사용 일반적인 INNER JOIN, LEFT JOIN 모두 사용 가능

계층 쿼리

   자기 자신을 참조하는 테이블에서 계층 구조를 따라 상하관계를 탐색하는 SQL 기법이다. 예를 들어, 부모-자식 구조 또는 트리 구조처럼 위계가 있는 데이터를 탐색할 때 사용한다.

예: 조직도(CEO → 부서장 → 팀원), 카테고리(대분류 → 중분류 → 소분류), 댓글(부모 → 답글)

Oracle 방식: CONNECT BY + START WITH

SELECT employee_id, name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

 

START WITH 루트 노드 지정 (보통 부모가 없는 행)
CONNECT BY 부모-자식 연결 조건 (PRIOR 사용)
LEVEL 계층 깊이 표시 (1부터 시작)

표준 방식: WITH RECURSIVE (PostgreSQL, SQL Server 등)

WITH RECURSIVE emp_tree AS (
  -- anchor: 루트 찾기
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive part: 자식 탐색
  SELECT e.employee_id, e.name, e.manager_id, t.level + 1
  FROM employees e
  JOIN emp_tree t ON e.manager_id = t.employee_id
)
SELECT * FROM emp_tree;

 

정규 표현식

   정규 표현식(Regular Expression, 정규식)은 문자열에서 패턴을 찾거나 치환하거나 추출할 때 사용하는 문자열 검색 도구이다. SQL, Python, Java, JavaScript 등 거의 모든 프로그래밍 언어와 데이터베이스에서 지원한다.

자주 쓰는 정규 표현식 기호

. 임의의 한 문자 a.b → aab, acb
* 앞 문자가 0회 이상 반복 bo* → b, bo, boo
+ 앞 문자가 1회 이상 반복 go+ → go, goo, gooo
? 앞 문자가 0 또는 1회 colou?r → color, colour
^ 문자열의 시작 ^A → A로 시작하는 문자열
$ 문자열의 끝 ing$ → ing으로 끝나는 문자열
[...] 대괄호 안 문자 중 하나 [abc] → a, b, c
[^...] 대괄호 안 문자 제외 [^0-9] → 숫자 아닌 문자
` ` 또는 (OR)
( ) 그룹화 (ab)+ → ab, abab, ababab
{n} 정확히 n번 a{2} → aa
{n,} n번 이상 a{2,} → aa, aaa, ...
{n,m} n~m회 반복 a{2,4} → aa, aaa, aaaa

목적 정규 표현식

숫자만 ^[0-9]+$
영문자만 ^[A-Za-z]+$
이메일 검증 ^[\w\.-]+@[\w\.-]+\.\w{2,}$
휴대폰 번호 ^01[0-9]-\d{3,4}-\d{4}$
한글 포함 [가-힣]

 

반응형
저작자표시 (새창열림)

'프로그래밍 언어 > SQL' 카테고리의 다른 글

SQL 관리 구문  (0) 2025.05.24
SQL 기본  (0) 2025.05.24
데이터 모델과 SQL  (2) 2025.05.24
데이터 모델링의 이해  (3) 2025.05.24

관련글

댓글

티스토리툴바