SQL 구문 참조

45개 항목

Basic SELECTSELECT

SELECT col1, col2 FROM table;

SELECT name, email FROM users;

ℹ Use * to select all columns (avoid in production)

SELECT with aliasSELECT

SELECT col AS alias FROM table;

SELECT first_name AS name, created_at AS joined FROM users;

SELECT DISTINCTSELECT

SELECT DISTINCT col FROM table;

SELECT DISTINCT country FROM customers;

WHERE clauseSELECT

SELECT ... FROM table WHERE condition;

SELECT * FROM orders WHERE status = 'pending' AND total > 100;

ORDER BYSELECT

SELECT ... ORDER BY col ASC|DESC;

SELECT name, price FROM products ORDER BY price DESC;

LIMIT / OFFSETSELECT

SELECT ... LIMIT n OFFSET m;

SELECT * FROM posts LIMIT 20 OFFSET 40;

ℹ SQL Server: use TOP n or FETCH NEXT

LIKE pattern matchSELECT

WHERE col LIKE 'pattern'

SELECT * FROM users WHERE email LIKE '%@gmail.com';

IN listSELECT

WHERE col IN (v1, v2, ...)

SELECT * FROM orders WHERE status IN ('pending', 'processing');

BETWEEN rangeSELECT

WHERE col BETWEEN v1 AND v2

SELECT * FROM orders WHERE total BETWEEN 100 AND 500;

IS NULL checkSELECT

WHERE col IS NULL | IS NOT NULL

SELECT * FROM users WHERE deleted_at IS NULL;

CASE expressionSELECT

CASE WHEN cond THEN val ... ELSE val END

SELECT name, CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS type FROM users;

COUNTAggregates

SELECT COUNT(*) | COUNT(col) FROM table;

SELECT COUNT(*) AS total, COUNT(email) AS with_email FROM users;

SUM / AVG / MIN / MAXAggregates

SELECT SUM(col), AVG(col), MIN(col), MAX(col) FROM table;

SELECT SUM(total) AS revenue, AVG(total) AS avg_order FROM orders;

GROUP BYAggregates

SELECT col, AGG(x) FROM table GROUP BY col;

SELECT country, COUNT(*) AS users FROM users GROUP BY country;

HAVINGAggregates

... GROUP BY col HAVING AGG(x) > n

SELECT country, COUNT(*) as cnt FROM users GROUP BY country HAVING COUNT(*) > 100;

GROUP BY with ORDERAggregates

... GROUP BY col ORDER BY AGG(x) DESC

SELECT status, COUNT(*) cnt FROM orders GROUP BY status ORDER BY cnt DESC;

INNER JOINJOINs

SELECT ... FROM a INNER JOIN b ON a.id = b.a_id;

SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;

LEFT JOINJOINs

SELECT ... FROM a LEFT JOIN b ON a.id = b.a_id;

SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id;

RIGHT JOINJOINs

SELECT ... FROM a RIGHT JOIN b ON a.id = b.a_id;

SELECT o.id, u.name FROM orders o RIGHT JOIN users u ON o.user_id = u.id;

FULL OUTER JOINJOINs

SELECT ... FROM a FULL OUTER JOIN b ON condition;

SELECT a.id, b.id FROM table_a a FULL OUTER JOIN table_b b ON a.key = b.key;

ℹ Not supported in MySQL 8 — use UNION of LEFT + RIGHT JOIN

SELF JOINJOINs

SELECT ... FROM t AS a JOIN t AS b ON a.col = b.col;

SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

CROSS JOINJOINs

SELECT ... FROM a CROSS JOIN b;

SELECT colors.name, sizes.label FROM colors CROSS JOIN sizes;

ℹ Returns Cartesian product — rows(a) × rows(b)

Subquery in WHERESubqueries

WHERE id IN (SELECT id FROM ...)

SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

Scalar subquerySubqueries

SELECT (SELECT COUNT(*) FROM ...) AS n

SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u;

EXISTSSubqueries

WHERE EXISTS (SELECT 1 FROM ...)

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

CTE (WITH clause)Subqueries

WITH cte AS (SELECT ...) SELECT * FROM cte;

WITH recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days') SELECT user_id, COUNT(*) FROM recent GROUP BY user_id;

Recursive CTESubqueries

WITH RECURSIVE cte AS (base UNION ALL recursive) ...

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

ROW_NUMBER()Window Fns

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

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

RANK / DENSE_RANKWindow Fns

RANK() OVER (ORDER BY col DESC)

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS pay_rank FROM employees;

ℹ RANK skips numbers on ties; DENSE_RANK does not

LAG / LEADWindow Fns

LAG(col, n) OVER (ORDER BY ...)

SELECT date, revenue, LAG(revenue,1) OVER (ORDER BY date) AS prev_revenue FROM daily_sales;

SUM() OVER (running total)Window Fns

SUM(col) OVER (ORDER BY col ROWS UNBOUNDED PRECEDING)

SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM transactions;

NTILE(n)Window Fns

NTILE(n) OVER (ORDER BY col)

SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

INSERT single rowDML

INSERT INTO table (col1, col2) VALUES (v1, v2);

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

INSERT multiple rowsDML

INSERT INTO table (cols) VALUES (r1), (r2), ...;

INSERT INTO tags (name) VALUES ('sql'), ('database'), ('tutorial');

INSERT … SELECTDML

INSERT INTO t1 (cols) SELECT cols FROM t2;

INSERT INTO archive SELECT * FROM orders WHERE created_at < '2023-01-01';

UPDATEDML

UPDATE table SET col = val WHERE condition;

UPDATE users SET status = 'inactive', updated_at = NOW() WHERE last_login < NOW() - INTERVAL '90 days';

DELETEDML

DELETE FROM table WHERE condition;

DELETE FROM sessions WHERE expires_at < NOW();

ℹ Always include WHERE — omitting it deletes all rows

UPSERT (ON CONFLICT)DML

INSERT … ON CONFLICT (col) DO UPDATE SET …

INSERT INTO settings (key, value) VALUES ('theme', 'dark') ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

ℹ PostgreSQL syntax. MySQL uses ON DUPLICATE KEY UPDATE.

TRUNCATEDML

TRUNCATE TABLE table;

TRUNCATE TABLE temp_imports;

ℹ Faster than DELETE; resets auto-increment; cannot be rolled back in some DBs

CREATE TABLEDDL

CREATE TABLE name (col type constraints, ...);

CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW());

ALTER TABLE ADD COLUMNDDL

ALTER TABLE t ADD COLUMN col type;

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

ALTER TABLE RENAMEDDL

ALTER TABLE t RENAME COLUMN old TO new;

ALTER TABLE users RENAME COLUMN username TO display_name;

CREATE INDEXDDL

CREATE INDEX name ON table (col);

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

DROP TABLEDDL

DROP TABLE [IF EXISTS] table;

DROP TABLE IF EXISTS temp_staging;

FOREIGN KEYDDL

REFERENCES other_table(col) ON DELETE action

CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE);

SQL 절 실행 순서

FROM→

JOIN→

WHERE→

GROUP BY→

HAVING→

SELECT→

DISTINCT→

ORDER BY→

LIMIT

이것은 논리적인 처리 순서입니다. WHERE는 SELECT 이전에 실행되므로 다음에서 SELECT 별칭을 참조할 수 없습니다 WHERE 절 — 대신 CTE 또는 하위 쿼리를 사용하세요. HAVING은 필터링 가능 GROUP BY 집계에.

NULL 처리 규칙

표현식	결과	참고
NULL = NULL	FALSE / UNKNOWN	Use IS NULL instead
NULL != NULL	UNKNOWN	Any comparison with NULL is UNKNOWN
NULL + 5	NULL	Arithmetic on NULL propagates NULL
COALESCE(NULL, 'x')	'x'	Returns first non-NULL value
NULLIF(a, a)	NULL	Returns NULL if both args are equal
COUNT(*)	Counts all rows	COUNT(col) skips NULLs

SQL 빠른 참조 FAQ

SQL 절 실행 순서는 어떻게 되나요?

SQL 절은 다음 논리적 순서로 실행됩니다: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET. WHERE는 SELECT 별칭을 참조할 수 없지만 HAVING은 GROUP BY 열을 참조할 수 있으므로 이 순서가 중요합니다.

INNER JOIN과 LEFT JOIN의 차이점은 무엇인가요?

INNER JOIN은 두 테이블에서 조인 조건이 일치하는 행만 반환합니다. LEFT JOIN(LEFT OUTER JOIN)은 왼쪽 테이블의 모든 행을 반환하며, 일치하지 않는 오른쪽 테이블 열에는 NULL을 반환합니다. 오른쪽에 일치 항목이 있는지 여부에 관계없이 왼쪽 테이블의 모든 레코드를 원할 때 LEFT JOIN을 사용하세요.

SQL에서 윈도우 함수란 무엇인가요?

윈도우 함수는 현재 행과 관련된 행 집합에서 값을 계산하지만 GROUP BY 집계와 달리 단일 행으로 축소하지 않습니다. 일반적인 윈도우 함수: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER(). OVER() 절을 사용하여 윈도우를 정의합니다.

CTE(공통 테이블 표현식)란 무엇인가요?

CTE는 SELECT, INSERT, UPDATE 또는 DELETE 문 앞에 WITH 키워드로 정의된 명명된 임시 결과 집합입니다. CTE는 중첩 하위 쿼리보다 가독성을 향상시키며, 조직도나 카테고리 트리와 같은 계층적 데이터를 위해 재귀적(WITH RECURSIVE)으로 사용할 수 있습니다.

Docker 치트 시트 — 명령어 참조 Docker Compose 변환기 Git 치트 시트 — Git 명령어 참조 Bash 치트 시트 — 명령어 참조 & 예제 Chmod 계산기 — Unix 파일 권한 계산기 Cron 표현식 빌더 — 스케줄 생성기

SQL 절 실행 순서

FROM→

JOIN→

WHERE→

GROUP BY→

HAVING→

SELECT→

DISTINCT→

ORDER BY→

LIMIT

NULL 처리 규칙

표현식	결과	참고
NULL = NULL	FALSE / UNKNOWN	Use IS NULL instead
NULL != NULL	UNKNOWN	Any comparison with NULL is UNKNOWN
NULL + 5	NULL	Arithmetic on NULL propagates NULL
COALESCE(NULL, 'x')	'x'	Returns first non-NULL value
NULLIF(a, a)	NULL	Returns NULL if both args are equal
COUNT(*)	Counts all rows	COUNT(col) skips NULLs

SQL 구문 참조

SQL 절 실행 순서

NULL 처리 규칙

SQL 빠른 참조 FAQ

SQL 절 실행 순서는 어떻게 되나요?

INNER JOIN과 LEFT JOIN의 차이점은 무엇인가요?

SQL에서 윈도우 함수란 무엇인가요?

CTE(공통 테이블 표현식)란 무엇인가요?

관련 도구

SQL 구문 참조

SQL 절 실행 순서

NULL 처리 규칙

SQL 빠른 참조 FAQ

SQL 절 실행 순서는 어떻게 되나요?

INNER JOIN과 LEFT JOIN의 차이점은 무엇인가요?

SQL에서 윈도우 함수란 무엇인가요?

CTE(공통 테이블 표현식)란 무엇인가요?

관련 도구