SELECT 문

SELECT는 데이터를 "무엇을, 어디서, 어떤 조건으로, 어떤 순서로, 얼마나" 가져올지 선언하는 문
핵심 절은
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT

 

기본 문법

SELECT [ALL | DISTINCT] 컬럼리스트
FROM 테이블
[WHERE 조건]
[GROUP BY 그룹_표현식]
[HAVING 그룹_필터]
[ORDER BY 정렬_표현식 [ASC|DESC]]
[LIMIT [OFFSET ,] 행_수];

논리적 실행 순서

FROM >> WHERE >> GROUP BY >> HAVING >> SELECT >> ORDER BY >> LIMIT

 

 

기본 문법 + 완전판

SELECT [DISTINCT] 컬럼리스트
FROM   테이블/서브쿼리/CTE [AS 별칭]
[JOIN ...]
[WHERE 조건]
[GROUP BY 컬럼리스트]
[HAVING 집계조건]
[WINDOW 창정의]
[ORDER BY 정렬식]
[LIMIT [OFFSET,] 행수]

논리적 실행 순서(실제로 처리되는 순서)
FROM(+JOIN) >> WHERE >> GROUP BY >> 집계함수 >> HAVING >> SELECT >> DISTINCT >> WINDOW >> ORDER BY >> LIMIT

 

 

 

 

컬럼 선택, 별칭, 표현식

 

컬럼 및 별칭

SELECT c.customer_id AS id, c.name AS customer_name
FROM customers AS c;

SELECT *는 빠르지만 스키마 변경에 취약하고 불필요한 I/O를 유발

가능하면 컬럼을 명시하자.

또한 별칭은 가독성을 높인다.

 

 

표현식(파생 컬럼)

SELECT product_id, price, price * 1.1 AS price_with_tax
FROM products;

 

 

조건부 컬럼

SELECT order_id,
       CASE status
         WHEN 'PAID' THEN '완료'
         WHEN 'CANCELLED' THEN '취소'
         ELSE '진행중'
       END AS status_ko
FROM orders;

 

 

중복 제거 - 단일 컬럼

SELECT DISTINCT category FROM products;

중복 제거 비용이 있으므로 필요한 경우에만 사용

가능하면 `GROUP BY`나 인덱스로 중복 자체를 방지.

 

 

다중 컬럼 조합의 유일 행

SELECT DISTINCT customer_id, status FROM orders;

 

 

예약어와 충돌하거나 공백이 있는 별칭은 백틱(`)으로 감싼다.

SELECT name AS `고객 이름` FROM customers;

 

 

 

 

조건절 WHERE

 

기본 비교 & 논리 연산자

SELECT * FROM products
WHERE category = 'book'
  AND price BETWEEN 10000 AND 30000
  AND name LIKE 'SQL%';   -- 앞 자리는 인덱스 활용 가능

 

 

BETWEEN (양끝 포함)

WHERE event_datetime BETWEEN '2025-08-01' AND '2025-08-31'

 

 

IN

WHERE method IN ('CARD','CASH')

 

 

LIKE (와일드카드: % _)

WHERE customer_name LIKE '김%'

 

 

정규식 (MySQL): REGEXP

WHERE email REGEXP '^[A-Za-z0-9._%+-]+@example\\.com$'

 

 

 NULL

SELECT *
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.product_id = p.product_id
);

 `NULL`은 “미정/없음”. 비교 연산(`=`, `<>`) 결과는 `UNKNOWN`이 될 수 있음

NULL`이 아니라 `IS NULL`/`IS NOT NULL` 사용

`NOT IN` 주의: 서브쿼리 결과에 `NULL`이 있으면 전체가 `UNKNOWN`이 되어 매치가 0건이 될 수 있음

 

 

 

정렬 - ORDER BY

 

기본 문법

SELECT * FROM orders ORDER BY order_date DESC, order_id DESC;

 

 

 

LIMIT

SELECT * FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 20 OFFSET 2000;

페이징(주의: OFFSET이 클수록 느려질 수 있음)

LIMIT - 권장 방식

SELECT *
FROM orders
WHERE (order_date, order_id) < ('2025-08-24', 100000)
ORDER BY order_date DESC, order_id DESC
LIMIT 20;

 

대용량에서의 시크 페이징(키 기반)

 

 

 

 

집계와 그룹화 - GROUP BY /  HAVING

 

기본 문법

-- 고객별 총 주문액
SELECT o.customer_id,
       SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.customer_id
HAVING total_amount >= 100000;

 

 

롤업(소계/합계)

SELECT category,
       SUM(price) AS sum_price
FROM products
GROUP BY category WITH ROLLUP;  -- 최종 합계 행이 추가됨

 

 

 

 

JOIN

INNER JOIN	매칭되는 행만
LEFT JOIN	왼쪽을 모두 유지, 오른쪽이 없으면 NULL
RIGHT JOIN	오른쪽을 모두 유지(선호도 낮음. 대부분 LEFT 사용)
CROSS JOIN	데카르트 곱
FULL OUTER JOIN	MySQL 미지원

 

가장 흔한 패턴

SELECT o.order_id, c.name, SUM(oi.quantity) qty
FROM orders o
JOIN customers c  ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, c.name;

 

 

OUTER JOIN + 조건은 반드시 조인 조건과 필터 조건을 분리

SELECT p.product_id, p.name, oi.quantity
FROM products p
LEFT JOIN order_items oi
  ON oi.product_id = p.product_id    -- 조인 조건
WHERE p.category = 'book';            -- 필터 조건

안티조인(미존재 행 찾기)**: `LEFT JOIN ... WHERE 오른쪽_PK IS NULL` 또는 `NOT EXISTS`(대체로 `NOT EXISTS`가 더 안전)

 

 

서브쿼리, `EXISTS`/`IN`/`ANY`/`ALL`

IN: 작은 목록/간단한 서브쿼리에 적합

SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status='PAID');

 

 

EXISTS: 존재 여부만 확인 → 일반적으로 대용량에서 유리

SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id AND o.status='PAID'
);

 

 

ANY/SOME, ALL

SELECT * FROM products
WHERE price > ALL (
  SELECT unit_price FROM order_items WHERE product_id = products.product_id
);

 

 

 

 CTE(`WITH`)와 재귀 쿼리

공통 테이블 표현식(가독성/재사용)

WITH paid_orders AS (
  SELECT order_id, customer_id FROM orders WHERE status='PAID'
)
SELECT c.customer_id, c.name
FROM customers c
JOIN paid_orders p ON p.customer_id = c.customer_id;

 

 

재귀 CTE: 1부터 10까지 생성 예시

WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n+1 FROM seq WHERE n < 10
)
SELECT * FROM seq;

CTE는 MySQL 8.0+ 기능입니다(5.7에는 없음).

 

 

 

윈도 함수(분석 함수)

그룹으로 묶지 않고도 **행 위/아래를 바라보며** 누계, 순위, 전/후 행 비교가 가능

-- 고객별 누적 구매액과 순위
SELECT c.customer_id,
       SUM(oi.quantity * oi.unit_price) AS total_amount,
       RANK() OVER (ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS rnk
FROM customers c
JOIN orders o  ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.customer_id
ORDER BY rnk;

-- 파티션 내 순번/직전 값
SELECT o.order_id,
       o.customer_id,
       o.order_date,
       ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS rn,
       LAG(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS prev_date
FROM orders o;

프레임 지정 예: `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`(누계)

 

 

 

집합 연산자 UNION / UNION ALL

`UNION`은 중복 제거(정렬 비용↑), `UNION ALL`은 단순 합치기(빠름)

SELECT email FROM customers WHERE grade='GOLD'
UNION ALL
SELECT email FROM newsletter_subscribers;

 

 

문자열 / 날짜 / 정규식

문자열

SELECT CONCAT(name, ' (', grade, ')') AS label FROM customers;
SELECT REPLACE(name, '주식회사 ', '') FROM customers;

 

 

날짜/시간(함수에 컬럼을 감싸면 인덱스 주의)

SELECT DATE(order_date) AS d, COUNT(*)
FROM orders
GROUP BY DATE(order_date);

 

 

정규식

SELECT * FROM products WHERE name REGEXP '(?i)mysql|maria(db)?';

 

 

JSON (MySQL 5.7+)

products.extra_specs JSON 컬럼 가정

키 추출

SELECT JSON_EXTRACT(extra_specs, '$.color') AS color FROM products;

 

 

화살표 연산자: -> (JSON), ->> (스칼라)

SELECT extra_specs->>'$.color' AS color_text FROM products;

 

 

JSON 배열 포함 여부

SELECT * FROM products
WHERE JSON_CONTAINS(extra_specs, '"red"', '$.available_colors');

 

 

JSON_TABLE (8.0+): JSON을 테이블처럼 펼치기

SELECT p.product_id, jt.color
FROM products p,
JSON_TABLE(p.extra_specs, '$.available_colors[*]'
           COLUMNS(color VARCHAR(20) PATH '$')) AS jt;

 

 

 

 

예제용 스키마

-- 고객
CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(50) NOT NULL,
  email       VARCHAR(100) UNIQUE,
  grade       ENUM('BASIC','SILVER','GOLD') DEFAULT 'BASIC',
  created_at  DATETIME NOT NULL
);

-- 상품
CREATE TABLE products (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  price      DECIMAL(10,2) NOT NULL,
  category   VARCHAR(30) NOT NULL
);

-- 주문 & 주문 항목
CREATE TABLE orders (
  order_id    INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date  DATETIME NOT NULL,
  status      ENUM('CREATED','PAID','CANCELLED') NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY AUTO_INCREMENT,
  order_id      INT NOT NULL,
  product_id    INT NOT NULL,
  quantity      INT NOT NULL,
  unit_price    DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);