SQL Cheatsheet + Query Generator
SQL syntax reference by DBMS and CRUD query generator.
26 items
SELECT
Retrieve data from a table.
SELECT col1, col2 FROM table
SELECT name, age FROM users WHERE age > 20;
WHERE
Filter data with conditions.
WHERE condition AND/OR condition
SELECT * FROM users WHERE age >= 20 AND status = 'active';
ORDER BY
Sort results in ascending or descending order.
ORDER BY col ASC|DESC
SELECT * FROM users ORDER BY created_at DESC;
GROUP BY / HAVING
Group data and apply aggregate conditions.
GROUP BY col HAVING condition
SELECT dept, COUNT(*) cnt FROM emp GROUP BY dept HAVING cnt > 5;
LIMIT / TOP / FETCH
Limit the number of rows returned (pagination). Syntax varies by DBMS.
LIMIT n OFFSET m
SELECT * FROM users LIMIT 10 OFFSET 20;
DISTINCT
Return only unique values, removing duplicates.
SELECT DISTINCT col FROM table
SELECT DISTINCT department FROM employees;
INNER JOIN
Return only matching rows from both tables.
INNER JOIN t2 ON t1.col = t2.col
SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;
LEFT JOIN
Return all rows from the left table with matching right rows.
LEFT JOIN t2 ON t1.col = t2.col
SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id;
RIGHT JOIN
Return all rows from the right table with matching left rows.
RIGHT JOIN t2 ON t1.col = t2.col
SELECT o.id, u.name FROM orders o RIGHT JOIN users u ON o.user_id = u.id;
FULL OUTER JOIN
Return all rows from both tables (NULL where no match).
(직접 지원 안 함 — LEFT + RIGHT UNION)
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.id;
CROSS JOIN
Return the Cartesian product of both tables.
CROSS JOIN t2
SELECT c.name, s.size FROM colors c CROSS JOIN sizes s;
INSERT
Insert new rows into a table.
INSERT INTO table (col1, col2) VALUES ('v1', 'v2')INSERT INTO users (name, email) VALUES ('홍길동', 'hong@test.com');UPDATE
Modify existing row data.
UPDATE table SET col1 = 'v1' WHERE condition
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';
DELETE
Remove rows from a table.
DELETE FROM table WHERE condition
DELETE FROM users WHERE status = 'deleted' AND updated_at < '2024-01-01';
UPSERT (INSERT or UPDATE)
Update if row exists, insert if not. Syntax varies significantly by DBMS.
INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col)
INSERT INTO users (id, name) VALUES (1, '홍길동') ON DUPLICATE KEY UPDATE name = VALUES(name);
CREATE TABLE
Create a new table. Auto-increment ID syntax varies by DBMS.
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE
Modify table structure (add/modify/drop columns).
ALTER TABLE t ADD col TYPE / MODIFY col TYPE / DROP col
ALTER TABLE users ADD phone VARCHAR(20); ALTER TABLE users MODIFY name VARCHAR(200); ALTER TABLE users DROP COLUMN phone;
CREATE INDEX
Create an index to improve query performance.
CREATE INDEX idx ON table (col)
CREATE INDEX idx_users_email ON users (email); CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
String Functions
Concatenation, substring, length, case conversion, and replacement functions.
CONCAT(a,b), SUBSTRING(s,pos,len), LENGTH(s), UPPER(s), LOWER(s), TRIM(s), REPLACE(s,old,new)
SELECT CONCAT(first_name, ' ', last_name) AS full_name, LENGTH(email) AS email_len FROM users;
Date/Time Functions
Current date, date arithmetic, and format conversion. Varies significantly by DBMS.
NOW(), CURDATE(), DATE_ADD(d, INTERVAL n DAY), DATEDIFF(d1,d2), DATE_FORMAT(d,fmt)
SELECT NOW(), DATE_ADD(created_at, INTERVAL 30 DAY) AS expires FROM users;
Aggregate Functions
COUNT, SUM, AVG, MAX, MIN, and string aggregation functions.
COUNT(*), SUM(col), AVG(col), MAX(col), MIN(col), GROUP_CONCAT(col)
SELECT dept, COUNT(*) cnt, AVG(salary) avg_sal, GROUP_CONCAT(name) names FROM emp GROUP BY dept;
Cast / NULL Functions
Type casting (CAST) and NULL replacement (COALESCE, IFNULL, ISNULL, NVL).
CAST(expr AS type), IFNULL(expr, default), COALESCE(a,b,...)
SELECT CAST(price AS DECIMAL(10,2)), IFNULL(phone, 'N/A') FROM products;
Subquery / IN
Nest a SELECT inside another SELECT for conditions or data.
SELECT * FROM t WHERE col IN (SELECT col FROM t2)
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 10000);
EXISTS
Check if matching rows exist in a subquery (more efficient than IN for large data).
WHERE EXISTS (SELECT 1 FROM t2 WHERE ...)
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
CTE (WITH Clause)
Break complex queries into named temporary results for readability.
WITH cte AS (SELECT ...) SELECT * FROM cte (8.0+)
WITH top_users AS ( SELECT user_id, SUM(total) as total_spent FROM orders GROUP BY user_id ORDER BY total_spent DESC LIMIT 10 ) SELECT u.name, t.total_spent FROM users u JOIN top_users t ON u.id = t.user_id;
Window Functions (ROW_NUMBER, RANK)
Advanced analytics: ranking and numbering by partition without GROUP BY.
ROW_NUMBER() OVER (PARTITION BY col ORDER BY col) (8.0+)
SELECT name, dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees;
SQL Cheatsheet: Compare SQL syntax differences across MySQL, PostgreSQL, SQL Server, Oracle, and SQLite at a glance. Search and copy DBMS-specific syntax and examples for SELECT, JOIN, INSERT, UPDATE, DELETE, and more. The CRUD Query Generator lets you input table names and columns to automatically generate SQL queries tailored to your chosen DBMS.