JOIN - 테이블 합치기 완전 정복

📊 JOIN의 종류 한눈에 비교

🏫 실습용 샘플 테이블

✅ INNER JOIN - 교집합 (가장 기본!)

-- 회원과 주문을 합치기 (양쪽에 모두 있는 데이터만)
SELECT
    u.name AS 회원명,
    u.grade AS 등급,
    o.product_name AS 상품명,
    o.amount AS 금액,
    o.status AS 상태
FROM users u                    -- 왼쪽 테이블 (별칭 u)
INNER JOIN orders o             -- 오른쪽 테이블 (별칭 o)
    ON u.id = o.user_id;       -- 연결 조건 (FK = PK)

-- 📤 결과: 홍길동-노트북, 홍길동-키보드, 김영희-마우스 (3건)
-- user_id=99인 모니터 주문은 결과에 없음! (users에 id=99가 없음)
-- 박민수(id=4)도 없음! (orders에 user_id=4가 없음)

✅ LEFT JOIN - 왼쪽 테이블 기준 전체

-- 모든 회원 목록 + 주문 있으면 주문 정보도 표시
SELECT u.name, u.grade, o.product_name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 결과: 홍길동-노트북, 홍길동-키보드, 김영희-마우스, 이철수-NULL, 박민수-NULL (5건)

-- 🔥 현업 응용: 한 번도 주문 안 한 회원 찾기
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;

✅ 3개 이상 테이블 JOIN (현업 필수!)

-- users + orders + products 3개 테이블 조인
SELECT u.name, u.grade, o.order_date, p.product_name, p.category, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id
WHERE o.status = '완료'
ORDER BY o.order_date DESC;

-- 💡 팁: JOIN을 여러 번 체인처럼 연결 가능!
-- 조인 순서: FROM → 첫번째 JOIN → 두번째 JOIN → ...
-- 반드시 테이블 별칭(u, o, p)을 사용하고
-- ON 조건에 인덱스가 있는 컬럼을 써야 성능이 좋습니다!

서브쿼리(Subquery) 완전 정복

📊 서브쿼리 3가지 종류

-- 예제 1: 평균 금액보다 비싼 주문만 조회
SELECT product_name, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- 예제 2: IN + 서브쿼리 (VIP 회원의 주문만 조회)
SELECT product_name, amount, status
FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE grade = 'VIP'
);

-- 예제 3: EXISTS (데이터 존재 여부 체크) - 성능 좋음!
SELECT u.name, u.email
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.status = '완료'
);

-- 예제 4: FROM절 서브쿼리 (인라인 뷰)
SELECT * FROM (
    SELECT u.name, COUNT(o.order_id) AS 주문횟수, SUM(o.amount) AS 총주문액
    FROM users u JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name
) AS summary
WHERE 총주문액 >= 1000000 ORDER BY 총주문액 DESC;

-- 예제 5: CTE (WITH절) - 현업 최신 트렌드!
WITH vip_users AS (
    SELECT id, name FROM users WHERE grade = 'VIP'
),
vip_orders AS (
    SELECT u.name, COUNT(*) AS cnt, SUM(o.amount) AS total
    FROM vip_users u JOIN orders o ON u.id = o.user_id
    WHERE o.status = '완료'
    GROUP BY u.id, u.name
)
SELECT name, cnt AS 주문건수, total AS 총구매액
FROM vip_orders ORDER BY total DESC;

인덱스(Index) 완전 정복 - 검색 속도의 비밀

-- 1. 단일 인덱스 생성
CREATE INDEX idx_email ON users(email);

-- 2. 복합 인덱스 (여러 컬럼)
CREATE INDEX idx_name_grade ON users(name, grade);
-- WHERE name = '홍길동' AND grade = 'VIP' → 최적화!

-- 3. UNIQUE 인덱스
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 4. 인덱스 목록 조회
SHOW INDEX FROM users;

-- 5. 인덱스 삭제
DROP INDEX idx_email ON users;

-- EXPLAIN으로 쿼리 실행 계획 분석!
EXPLAIN SELECT * FROM users WHERE email = 'hong@test.com';
-- type 컬럼이 핵심!
-- const   → PK/UNIQUE로 1건 검색 (최고!)
-- ref     → 인덱스로 검색 (좋음)
-- range   → 인덱스 범위 검색
-- ALL     → 전체 테이블 스캔 (최악! 반드시 개선!)

📋 인덱스 설계 황금 법칙

뷰(View) - 가상 테이블 완전 정복

-- 뷰 생성: 회원별 주문 통계
CREATE VIEW v_user_order_summary AS
SELECT
    u.id AS user_id, u.name AS 회원명, u.grade AS 등급,
    COUNT(o.order_id) AS 주문횟수,
    COALESCE(SUM(o.amount), 0) AS 총구매액,
    MAX(o.order_date) AS 최근주문일
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.grade;

-- 뷰 사용: 일반 테이블처럼 SELECT!
SELECT * FROM v_user_order_summary WHERE 등급 = 'VIP';
SELECT * FROM v_user_order_summary WHERE 총구매액 >= 1000000;

-- 뷰 수정 / 삭제
CREATE OR REPLACE VIEW v_user_order_summary AS ...;
DROP VIEW IF EXISTS v_user_order_summary;
SHOW FULL TABLES WHERE Table_type = 'VIEW';

스토어드 프로시저 & 함수 완전 정복

-- 프로시저 생성
DELIMITER //
CREATE PROCEDURE get_users_by_grade(
    IN p_grade VARCHAR(20),
    OUT p_count INT
)
BEGIN
    SELECT id, name, email, grade FROM users WHERE grade = p_grade;
    SELECT COUNT(*) INTO p_count FROM users WHERE grade = p_grade;
    IF p_count = 0 THEN
        SELECT '해당 등급 회원이 없습니다.' AS message;
    END IF;
END //
DELIMITER ;

-- 프로시저 호출
CALL get_users_by_grade('VIP', @cnt);
SELECT @cnt AS VIP회원수;

-- 사용자 정의 함수: 등급에 따라 할인율 반환
DELIMITER //
CREATE FUNCTION get_discount_rate(p_grade VARCHAR(20))
RETURNS DECIMAL(3,2) DETERMINISTIC
BEGIN
    DECLARE v_rate DECIMAL(3,2);
    IF p_grade = 'VIP' THEN SET v_rate = 0.20;
    ELSEIF p_grade = '골드' THEN SET v_rate = 0.10;
    ELSE SET v_rate = 0.05;
    END IF;
    RETURN v_rate;
END //
DELIMITER ;

-- 함수 사용
SELECT name, grade, get_discount_rate(grade) AS 할인율,
       amount * (1 - get_discount_rate(grade)) AS 할인후금액
FROM users u JOIN orders o ON u.id = o.user_id;

트리거(Trigger) - 자동 실행 이벤트

-- 트리거: users 테이블 UPDATE 시 자동으로 이력 저장
CREATE TABLE user_change_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT, changed_field VARCHAR(50),
    old_value VARCHAR(200), new_value VARCHAR(200),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.grade != NEW.grade THEN
        INSERT INTO user_change_log (user_id, changed_field, old_value, new_value)
        VALUES (OLD.id, 'grade', OLD.grade, NEW.grade);
    END IF;
    IF OLD.email != NEW.email THEN
        INSERT INTO user_change_log (user_id, changed_field, old_value, new_value)
        VALUES (OLD.id, 'email', OLD.email, NEW.email);
    END IF;
END //
DELIMITER ;

-- 테스트: 등급 변경하면 자동으로 로그 저장!
UPDATE users SET grade = 'VIP' WHERE id = 2;
SELECT * FROM user_change_log;

윈도우 함수(Window Function) - 고급 분석의 핵심

-- 기본 문법
함수명() OVER (
    [PARTITION BY 그룹컬럼]  -- 그룹 기준
    [ORDER BY 정렬컬럼]       -- 순서 기준
)

-- 예제 1: 등급별 순위 (PARTITION BY로 그룹 내 순위!)
SELECT u.name, u.grade, o.amount,
    RANK() OVER (PARTITION BY u.grade ORDER BY o.amount DESC) AS 등급내순위
FROM users u JOIN orders o ON u.id = o.user_id;

-- 예제 2: 누적합 & 이전 주문과 비교
SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS 누적합,
    LAG(amount, 1, 0) OVER (ORDER BY order_date) AS 전회금액,
    amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS 증감
FROM orders WHERE user_id = 1;

트랜잭션 심화 & 잠금(Lock) 완전 정복

📊 격리 수준 4단계

-- 트랜잭션 실전 패턴: 안전한 계좌 이체
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 잠금!
UPDATE accounts SET balance = balance - 100000 WHERE id = 1;
UPDATE accounts SET balance = balance + 100000 WHERE id = 2;
INSERT INTO transfer_log (from_id, to_id, amount) VALUES (1, 2, 100000);
COMMIT; -- 성공 시 확정
-- ROLLBACK; -- 실패 시 전부 취소!

쿼리 성능 최적화 실전

-- ❌ 나쁜 예 1: WHERE에 함수 사용 → 인덱스 무력화!
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- ✅ 좋은 예 1: BETWEEN으로 범위 조건 사용
SELECT product_name, amount FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31 23:59:59';

-- ❌ 나쁜 예 2: LIKE '%키워드%' 앞에 % 사용 → 인덱스 무효
SELECT * FROM users WHERE name LIKE '%홍%';

-- ✅ 좋은 예 2: 앞에 % 제거
SELECT name, email FROM users WHERE name LIKE '홍%';

-- ❌ 나쁜 예 3: 상관 서브쿼리 (N+1 문제!)
SELECT name, (SELECT SUM(amount) FROM orders WHERE user_id = u.id) AS 총구매
FROM users u;

-- ✅ 좋은 예 3: JOIN으로 변경
SELECT u.name, SUM(o.amount) AS 총구매
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

📋 성능 최적화 체크리스트

실전 프로젝트: 쇼핑몰 DB 완성 (종합 실습)

-- 쇼핑몰 DB 생성
CREATE DATABASE shop_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shop_db;

-- 카테고리 테이블
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    parent_id INT DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- 상품 테이블
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(12, 0) NOT NULL DEFAULT 0,
    stock INT NOT NULL DEFAULT 0,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    FULLTEXT INDEX idx_search (name, description)
);

-- 회원 테이블
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(50) NOT NULL,
    grade ENUM('일반', '실버', '골드', 'VIP') DEFAULT '일반',
    total_purchase DECIMAL(15, 0) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_grade (grade)
);

-- 주문 테이블
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(12, 0) NOT NULL,
    status ENUM('대기', '결제완료', '배송중', '배송완료', '취소', '환불') DEFAULT '대기',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id),
    INDEX idx_status_date (status, created_at)
);

-- 주문 상세 테이블
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(12, 0) NOT NULL,
    subtotal DECIMAL(12, 0) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 핵심 분석 쿼리: 카테고리별 월별 매출 + 순위
SELECT
    c.name AS 카테고리,
    DATE_FORMAT(o.created_at, '%Y-%m') AS 월,
    COUNT(DISTINCT o.id) AS 주문수,
    SUM(oi.subtotal) AS 매출액,
    RANK() OVER (
        PARTITION BY DATE_FORMAT(o.created_at, '%Y-%m')
        ORDER BY SUM(oi.subtotal) DESC
    ) AS 월별매출순위
FROM categories c
JOIN products p ON c.id = p.category_id
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = '배송완료'
GROUP BY c.id, c.name, DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY 월 DESC, 매출액 DESC;

💼 현업 면접 SQL 심화 Q&A TOP 10

🗺️ 데이터베이스 개발자 완전 학습 로드맵

✅ 이 가이드(3단계) 완료 체크리스트

🔧 추천 실습 환경 & 학습 자료