중첩 쿼리 분석

하나의 SELECT 문의 실행 결과를 다른 쿼리의 필터 조건으로 활용하는 기법입니다. 내부 쿼리가 먼저 실행되며, 그 결과가 외부 쿼리에 전달됩니다.

-- 상품 분류 테이블 활용 예시
-- '전자기기' 카테고리에 속한 상품 목록 조회
SELECT category_code FROM item_category WHERE category_title = '전자기기';
SELECT * FROM merchandise WHERE category_code = (SELECT category_code FROM item_category WHERE category_title = '전자기기');

-- 특정 상품의 카테고리명 확인
SELECT category_title FROM item_category WHERE category_code = (SELECT category_code FROM merchandise WHERE item_name = '갤럭시');

-- 평균 가격보다 비싼 상품 검색
SELECT * FROM merchandise WHERE unit_price > (SELECT AVG(unit_price) FROM merchandise);

데카르트 곱(Cross Join)

두 테이블의 모든 행을 조합하여 반환하며, 실무에서는 거의 사용되지 않습니다.

-- 카테시안 곱 생성 방식
SELECT * FROM category_master, product_master;
SELECT * FROM product_master CROSS JOIN category_master;

내부 조인(Inner Join)

양쪽 테이블의 공통된 데이터만 반환하는 조인 방식입니다.

-- 암시적 내부 조인 (쉼표 구분)
SELECT * FROM product_master pm, category_master cm WHERE cm.category_id = pm.cat_id;

-- 명시적 내부 조인 (JOIN 키워드 사용)
SELECT * FROM category_master cm INNER JOIN product_master pm ON cm.category_id = pm.cat_id;
-- INNER 생략 가능
SELECT * FROM category_master cm JOIN product_master pm ON cm.category_id = pm.cat_id;

왼쪽 외부 조인(Left Outer Join)

왼쪽 테이블의 모든 레코드를 포함하고, 오른쪽 테이블에서 매칭되는 데이터가 없으면 NULL로 채웁니다.

-- 카테고리 기준 상품 정보 조회 (상품이 없는 카테고리도 표시)
SELECT * FROM category_master cm LEFT JOIN product_master pm ON cm.category_id = pm.cat_id;

-- 상품 기준 카테고리 정보 조회
SELECT * FROM product_master pm LEFT JOIN category_master cm ON cm.category_id = pm.cat_id;

오른쪽 외부 조인(Right Outer Join)

오른쪽 테이블의 모든 레코드를 기준으로 조인합니다.

SELECT * FROM category_master cm RIGHT JOIN product_master pm ON cm.category_id = pm.cat_id;

재귀적 자기 조인(Self Join)

동일한 테이블을 두 개의 별칭으로 분리하여 계층 구조를 표현합니다.

-- 지역 계층 테이블 생성
CREATE TABLE region_hierarchy (
    region_id INT PRIMARY KEY AUTO_INCREMENT,
    region_name VARCHAR(20),
    parent_region_id INT
);

INSERT INTO region_hierarchy VALUES 
(1, '서울특별시', NULL), (2, '부산광역시', NULL),
(3, '강남구', 1), (4, '서초구', 1), (5, '해운대구', 2);

-- 상위-하위 지역 관계 조회
SELECT r1.region_id AS 상위코드, r1.region_name AS 상위지역,
       r2.region_name AS 하위지역, r2.region_id AS 하위코드
FROM region_hierarchy r1 
JOIN region_hierarchy r2 ON r1.region_id = r2.parent_region_id;

-- 특정 상위 지역의 하위 목록 필터링
SELECT a.region_id, a.region_name, b.region_name, b.region_id 
FROM region_hierarchy a 
JOIN region_hierarchy b ON a.region_id = b.parent_region_id 
WHERE a.region_name = '서울특별시';

중첩 쿼리의 세 가지 활용 패턴

-- 패턴 1: 단일 값 비교 (WHERE 절)
SELECT * FROM employee WHERE monthly_salary > (SELECT AVG(monthly_salary) FROM employee);

-- 패턴 2: 다중 값 비교 (IN 연산자)
SELECT * FROM employee WHERE dept_code IN 
(SELECT dept_code FROM department WHERE dept_name IN ('영업팀', '재무팀'));

-- 패턴 3: 파생 테이블 (FROM 절 서브쿼리)
SELECT d.dept_name, d.office_location, e.* 
FROM department d 
JOIN (SELECT * FROM employee WHERE monthly_salary > 5000) e 
ON d.dept_code = e.dept_code;

UNION 세로 결합

여러 SELECT 결과를 세로로 연결하며, 컬럼 수와 데이터 타입이 일치해야 합니다.

-- 중복 제거 (UNION)
SELECT * FROM employee WHERE monthly_salary > 8000
UNION
SELECT * FROM employee WHERE dept_code = 10;

-- 중복 포함 (UNION ALL)
SELECT * FROM employee WHERE monthly_salary > 8000
UNION ALL
SELECT * FROM employee WHERE dept_code = 10;

-- 서로 다른 테이블의 동일 구조 데이터 결합
CREATE TABLE instructor (
    emp_no INT, emp_name VARCHAR(20), gender VARCHAR(10)
);
CREATE TABLE trainee (
    student_no INT, student_name VARCHAR(20), gender VARCHAR(10)
);

SELECT emp_no AS id, emp_name AS name, gender FROM instructor WHERE gender = '남성'
UNION
SELECT student_no, student_name, gender FROM trainee WHERE gender = '남성';

수학 연산 함수

-- 반올림 처리
SELECT ROUND(3.141592);      -- 3
SELECT ROUND(3.141592, 2);   -- 3.14
SELECT ROUND(3.145, 2);      -- 3.15

-- 천 단위 구분 포맷
SELECT FORMAT(12345.1415926, 3);  -- 12,345.142

-- 버림/올림
SELECT FLOOR(12345.941592);  -- 12345 (내림)
SELECT CEIL(12345.141592);   -- 12346 (올림)

-- 나머지, 거듭제곱, 난수
SELECT MOD(17, 5);           -- 2
SELECT POW(3, 4);            -- 81
SELECT RAND();               -- 0~1 사이 무작위 실수
SELECT RAND(100);            -- 시드 기반 고정 난수

문자열 처리 함수

-- 대소문자 변환
SELECT UPPER('hello'), LOWER('WORLD');

-- 문자열 치환
SELECT REPLACE('데이터 분석가', '데이터', 'AI');

-- 문자열 결합
SELECT CONCAT('A', 'B', 'C', 123);           -- 단순 결합
SELECT CONCAT_WS('|', 'A', 'B', 'C');        -- 구분자 포함 결합

-- 반복, 역순, 추출
SELECT REPEAT('안녕', 3);                    -- 안녕안녕안녕
SELECT REVERSE('Python');                    -- nohtyP
SELECT SUBSTRING('데이터베이스', 3, 3);       -- 베이스
SELECT LEFT('데이터베이스', 4);              -- 데이터
SELECT RIGHT('데이터베이스', 3);             -- 이스

-- 길이 측정
SELECT CHAR_LENGTH('한글');                  -- 문자 수: 2
SELECT LENGTH('한글');                       -- 바이트 수: 6 (UTF-8)

-- 실용 예시: 이름의 첫 글자만 대문자로
SELECT CONCAT(UPPER(LEFT(student_name, 1)), SUBSTRING(student_name, 2)) FROM student;

날짜/시간 함수

-- 현재 시점 조회
SELECT NOW();                    -- 2024-09-25 15:56:31
SELECT CURDATE();                -- 2024-09-25
SELECT CURTIME();                -- 15:57:15

-- 날짜 차이 계산
SELECT DATEDIFF('2024-12-25', '2024-01-01');  -- 358

-- 날짜 가감
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 6 MONTH);

-- 특정 구성요소 추출
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW());

-- 형식 지정 출력
SELECT DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %H시 %i분 %s초');

-- 유닉스 타임스탬프 변환
SELECT UNIX_TIMESTAMP();         -- 현재 초 단위 타임스탬프
SELECT FROM_UNIXTIME(1700000000); -- 초를 날짜로 변환

실전 활용 사례

-- 연도별 마지막 로그인 시간 조회
CREATE TABLE user_access_log (
    user_id INT,
    access_time DATETIME
);

-- 2020년 각 사용자의 마지막 접속 기록
SELECT user_id, MAX(access_time) AS last_login
FROM user_access_log
WHERE YEAR(access_time) = 2020
GROUP BY user_id;