SQL 고급 필터링과 다중 테이블 조회 기법

1. 데이터 필터링 기법

1.1 HAVING을 활용한 그룹 필터링

HAVING 절은 집계 함수 적용 후 그룹화된 결과에 조건을 적용할 때 사용합니다. WHERE 절은 개별 행에 필터를 적용하는 반면, HAVING은 SUM(), AVG(), COUNT() 등의 집계가 완료된 그룹 단위로 조건을 검사합니다.

실무 예시: 30세 이상 직원만 대상으로 부서별 평균 급여를 계산하고, 그 중 평균이 10,000원 초과인 부서만 추출

-- 1단계: 30세 이상 직원 필터 및 부서 그룹화
SELECT dept_code, AVG(compensation) 
FROM staff 
WHERE years_old > 30 
GROUP BY dept_code;

그룹화된 결과에 추가 필터가 필요한 경우 WHERE 대신 HAVING을 사용해야 합니다:

-- 오류: 그룹 필터에 WHERE 사용 불가
SELECT dept_code, AVG(compensation) 
FROM staff 
WHERE years_old > 30 
GROUP BY dept_code 
WHERE AVG(compensation) > 10000;  -- 문법 오류!

-- 정상: HAVING으로 그룹 조건 지정
SELECT dept_code, AVG(compensation) AS avg_pay
FROM staff 
WHERE years_old > 30 
GROUP BY dept_code 
HAVING AVG(compensation) > 10000;

1.2 DISTINCT로 중복 제거

SELECT 결과에서 중복 행을 제거하여 고유 값만 반환합니다. 기본 키가 포함된 경우 중복이 없으므로 DISTINCT 효과가 없습니다.

-- 여러 컬럼 조합의 고유 값 추출
SELECT DISTINCT dept_code, years_old FROM staff;

-- 단일 컬럼 중복 제거
SELECT DISTINCT position_title FROM staff;

1.3 ORDER BY 정렬

결과 집합을 지정한 컬럼 기준으로 정렬합니다. ASC(오름차순)는 기본값이며 DESC(내림차순)를 명시적으로 지정할 수 있습니다.

-- 급여 오름차순 (기본)
SELECT * FROM staff ORDER BY compensation;

-- 급여 내림차순
SELECT * FROM staff ORDER BY compensation DESC;

-- 다중 정렬: 부서코드 내림차순, 같은 부서 내에서는 나이 오름차순
SELECT * FROM staff ORDER BY dept_code DESC, years_old ASC;

-- 복합 조건: 필터 → 그룹화 → 그룹필터 → 정렬
SELECT dept_code, AVG(compensation) AS avg_pay
FROM staff 
WHERE years_old > 30 
GROUP BY dept_code 
HAVING AVG(compensation) > 500 
ORDER BY avg_pay ASC;

1.4 LIMIT으로 결과 수 제한

반환할 행의 수와 시작 위치를 제어합니다.

-- 상위 10개 행만 조회 (OFFSET 0, COUNT 10)
SELECT * FROM staff LIMIT 10;

-- 6번째 행부터 5개 행 조회 (OFFSET 5, COUNT 5)
SELECT * FROM staff LIMIT 5, 5;

-- MySQL 8.0+ 표준 문법
SELECT * FROM staff LIMIT 5 OFFSET 5;

1.5 REGEXP 정규표현식 매칭

문자열 패턴 매칭을 위한 정규표현식 연산자입니다.

패턴	설명	예시
^	문자열 시작	'^b' → "book", "bike"
$	문자열 종료	'st$' → "test", "persist"
.	임의 단일 문자	'b.t' → "bit", "bat", "but"
*	앞 문자 0회 이상	'f*n' → "fn", "fan", "faan"
+	앞 문자 1회 이상	'ba+' → "ba", "bay", "battle"
?	앞 문자 0 또는 1회	'sa?' → "s", "sa"
[...]	문자 집합 중 하나	'[xz]' → "x-ray", "zebra"
[^...]	문자 집합 제외	'[^abc]' → "desk", "fox"
{n,m}	n회 이상 m회 이하	'b{2,4}' → "bb", "bbb", "bbbb"

실습 데이터 준비:

CREATE TABLE personnel (
  fullname VARCHAR(50),
  years INT,
  stature INT,
  sex VARCHAR(10)
);

INSERT INTO personnel VALUES 
('Thomas', 25, 168, 'M'), ('Tom', 20, 172, 'M'),
('Dany', 29, 175, 'M'), ('Jane', 27, 171, 'M'),
('Susan', 24, 173, 'F'), ('Green', 25, 168, 'F'),
('Henry', 21, 160, 'F'), ('Lily', 18, 190, 'M'),
('LiMing', 19, 187, 'M');

정규표현식 활용 예제:

-- 'j'로 시작하는 이름
SELECT * FROM personnel WHERE fullname REGEXP '^j';

-- 'y'로 끝나는 이름
SELECT * FROM personnel WHERE fullname REGEXP 'y$';

-- 'a'와 'y' 사이에 정확히 한 문자
SELECT * FROM personnel WHERE fullname REGEXP 'a.y';

-- 'T' 뒤에 'h'가 0회 이상
SELECT * FROM personnel WHERE fullname REGEXP 'Th*';

-- 'T' 뒤에 'h'가 1회 이상
SELECT * FROM personnel WHERE fullname REGEXP 'Th+';

-- 'S' 뒤에 'a'가 0 또는 1회
SELECT * FROM personnel WHERE fullname REGEXP 'Sa?';

2. 다중 테이블 조회와 중첩 쿼리

2.1 핵심 개념

중첩 쿼리(서브쿼리): 다른 SQL 문 내부에 괄호로 감싼 SELECT 문을 삽입하여, 단계별 문제 해결 방식으로 데이터를 추출합니다.

테이블 조인: JOIN 키워드를 사용해 논리적으로 연관된 여러 테이블의 데이터를 결합하여 단일 결과 집합으로 반환합니다.

실습 환경 구성:

-- 부서 정보 테이블
CREATE TABLE division (
    division_id INT PRIMARY KEY AUTO_INCREMENT,
    division_name VARCHAR(20)
);

-- 사원 정보 테이블
CREATE TABLE member (
    member_id INT PRIMARY KEY AUTO_INCREMENT,
    member_name VARCHAR(20),
    gender ENUM('M','F') NOT NULL DEFAULT 'M',
    years_old INT,
    division_id INT
);

INSERT INTO division VALUES
(200, '개발팀'), (201, '운영팀'), (202, '영업팀'),
(203, '재무팀'), (204, '디자인팀');

INSERT INTO member(member_name, gender, years_old, division_id) VALUES
('ronaldo', 'M', 18, 200), ('avril', 'F', 18, 201),
('kylian', 'M', 38, 202), ('haaland', 'M', 18, 203),
('cristiano', 'M', 28, 204), ('lavigne', 'F', 18, 205);

2.2 중첩 쿼리 활용

케이스 1: 특정 사원이 속한 부서명 조회

-- 분리 실행 방식
SELECT division_id FROM member WHERE member_name = 'ronaldo';
-- 결과: 200
SELECT division_name FROM division WHERE division_id = 200;

-- 중첩 쿼리로 통합
SELECT division_name 
FROM division 
WHERE division_id = (
    SELECT division_id 
    FROM member 
    WHERE member_name = 'ronaldo'
);

케이스 2: 특정 부서에 소속된 모든 사원 조회

-- 분리 실행 방식
SELECT division_id FROM division 
WHERE division_name IN ('개발팀', '운영팀');
-- 결과: 200, 201

SELECT * FROM member 
WHERE division_id = 200 OR division_id = 201;

-- IN 연산자와 중첩 쿼리로 통합
SELECT * FROM member 
WHERE division_id IN (
    SELECT division_id 
    FROM division 
    WHERE division_name IN ('개발팀', '운영팀')
);

2.3 조인 연산과 카르테시안 곱

카르테시안 곱: 두 테이블의 모든 행을 조합한 결과로, 테이블 A가 n행, 테이블 B가 m행이면 결과는 n×m행이 됩니다.

-- 명시적 카르테시안 곱 (조인 조건 없음)
SELECT * FROM division, member;

-- 암시적 카르테시안 곱 (CROSS JOIN)
SELECT * FROM division CROSS JOIN member;

유효 데이터 필터링: 논리적 연관성(division.division_id = member.division_id)을 조건으로 적용하여 의미 있는 결과만 추출합니다.

SELECT * FROM division, member 
WHERE division.division_id = member.division_id;

2.4 조인 유형별 특성

조인 유형	설명
INNER JOIN	양쪽 테이블 모두 매칭되는 행만 반환 (교집합)
LEFT JOIN	왼쪽 테이블 전체 + 오른쪽 매칭 행, 없으면 NULL
RIGHT JOIN	오른쪽 테이블 전체 + 왼쪽 매칭 행, 없으면 NULL
UNION	두 SELECT 결과를 세로로 병합, 중복 제거

-- INNER JOIN: division_id 205는 division 테이블에 없어 제외
SELECT * FROM member m 
INNER JOIN division d ON m.division_id = d.division_id;

-- LEFT JOIN: member 테이블의 모든 행 포함, 매칭 없는 division 정보는 NULL
SELECT * FROM member m 
LEFT JOIN division d ON m.division_id = d.division_id;

-- RIGHT JOIN: division 테이블의 모든 행 포함
SELECT * FROM division d 
RIGHT JOIN member m ON m.division_id = d.division_id;

-- UNION 예제
CREATE TABLE instructor (id INT PRIMARY KEY, fullname VARCHAR(30));
CREATE TABLE learner (id INT PRIMARY KEY, fullname VARCHAR(30));

INSERT INTO instructor VALUES (1, 'ronaldo'), (2, 'lavigne');
INSERT INTO learner VALUES (1, 'ronaldo'), (2, 'avril');

-- 중복 제거된 합집합
SELECT id, fullname FROM instructor
UNION
SELECT id, fullname FROM learner;

2.5 조인과 중첩 쿼리 비교

요구사항: 평균 연령이 25세 이상인 부서명 조회

방법 A: 조인 활용

-- 1단계: 테이블 결합
SELECT * FROM division d 
INNER JOIN member m ON d.division_id = m.division_id;

-- 2단계: 부서별 그룹화 및 평균 연령 계산
SELECT d.division_name, AVG(m.years_old) AS avg_age
FROM division d 
INNER JOIN member m ON d.division_id = m.division_id
GROUP BY d.division_name;

-- 3단계: 평균 연령 조건 필터링
SELECT d.division_name, AVG(m.years_old) AS avg_age
FROM division d 
INNER JOIN member m ON d.division_id = m.division_id
GROUP BY d.division_name
HAVING AVG(m.years_old) > 25;

방법 B: 중첩 쿼리 활용

-- 1단계: member 테이블에서 평균 연령 25세 초과 부서 ID 추출
SELECT division_id 
FROM member 
GROUP BY division_id 
HAVING AVG(years_old) > 25;

-- 2단계: 추출된 ID로 division 테이에서 부서명 조회
SELECT division_name 
FROM division 
WHERE division_id IN (
    SELECT division_id 
    FROM member 
    GROUP BY division_id 
    HAVING AVG(years_old) > 25
);

2.6 EXISTS 연산자

중첩 쿼리의 결과 존재 여부를 논리값(TRUE/FALSE)으로 반환합니다. EXISTS가 TRUE면 외부 쿼리 실행, FALSE면 외부 쿼리 결과 없음.

-- 중첩 쿼리 결과 존재: 외부 쿼리 실행
SELECT * FROM member 
WHERE EXISTS (
    SELECT * FROM division WHERE division_id > 3
);

-- 중첩 쿼리 결과 없음: 외부 쿼리 결과 없음
SELECT * FROM member 
WHERE EXISTS (
    SELECT * FROM division WHERE division_id > 260
);

태그: SQL HAVING GROUP BY JOIN Subquery

7월 17일 01:49에 게시됨

괴물 클럽