SQL Syntax Cheatsheet

MySQL SQL Syntax Interactive Cheatsheet

This chapter is a quick reference guide covering the most commonly used MySQL SQL statements and functions. Each concept is accompanied by code examples and real-world scenarios. Whether you are a beginner or an experienced developer, you can quickly look up and learn SQL syntax.

Usage Tips: This chapter uses an "example-driven" approach. Each SQL statement is accompanied by specific use cases. It is recommended to copy examples and run them in your local MySQL environment to deepen your understanding.

SELECT — Query Data

Basic SELECT Statement

Simplest SELECT

-- Query all columns and rows
SELECT * FROM employees;

-- Query specific columns
SELECT employee_id, name, salary FROM employees;

-- Query a limited number of rows
SELECT * FROM employees LIMIT 10;

-- Query 5 rows starting from row 10 (MySQL 8.0+)
SELECT * FROM employees LIMIT 10, 5;
-- Or use OFFSET (clearer)
SELECT * FROM employees LIMIT 5 OFFSET 10;

Column Aliases and Expressions

-- Use aliases
SELECT
  employee_id,
  name,
  salary * 12 AS annual_salary,
  salary * 1.1 AS next_year_salary
FROM employees;

-- Use CASE expression
SELECT
  name,
  salary,
  CASE
    WHEN salary >= 5000 THEN 'Senior'
    WHEN salary >= 3000 THEN 'Mid-level'
    ELSE 'Junior'
  END AS level
FROM employees;

DISTINCT — Remove Duplicates

-- Query all distinct departments
SELECT DISTINCT department FROM employees;

-- Remove duplicates on multiple columns
SELECT DISTINCT department, job_title FROM employees;

WHERE — Filter Conditions

Common WHERE Patterns

-- Combine conditions
SELECT * FROM employees
WHERE (department = 'IT' OR department = 'HR')
  AND salary >= 4000;

-- BETWEEN for range queries (more efficient)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- LIKE for fuzzy queries (pay attention to performance)
SELECT * FROM users
WHERE email LIKE '%@gmail.com';

-- IN for multiple values
SELECT * FROM products
WHERE category_id IN (1, 3, 5, 7);

JOIN — Table Joins

Four JOIN Types

INNER JOIN — Inner Join (Only matching rows)

-- Query each employee with department info
SELECT
  e.employee_id,
  e.name,
  d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

LEFT JOIN — Left Outer Join (Keep all left rows)

-- Query all employees with departments (keep even without department)
SELECT
  e.employee_id,
  e.name,
  COALESCE(d.department_name, 'No Department') AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

RIGHT JOIN — Right Outer Join (Keep all right rows)

-- Query all departments with employees (keep even without employees)
SELECT
  d.department_name,
  COUNT(e.employee_id) AS employee_count
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id;

FULL OUTER JOIN — Full Outer Join (MySQL uses UNION)

-- MySQL simulates FULL OUTER JOIN with UNION
SELECT
  COALESCE(e.employee_id, -1) AS emp_id,
  COALESCE(e.name, 'No Employee') AS employee_name,
  COALESCE(d.department_name, 'No Department') AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT
  COALESCE(e.employee_id, -1),
  COALESCE(e.name, 'No Employee'),
  COALESCE(d.department_name, 'No Department')
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

Multi-Table JOINs

-- Join three tables: employees → departments → managers
SELECT
  e.employee_id,
  e.name,
  d.department_name,
  m.name AS manager_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN employees m ON d.manager_id = m.employee_id;

Performance Tips: - Always use indexed columns in JOIN conditions (usually primary keys and foreign keys) - Filter with WHERE before JOINing (reduce rows to join) - Avoid functions in JOIN conditions (e.g., YEAR(date_column) = 2024) - Use EXPLAIN to check JOIN order and index usage

Subqueries — Nested Queries

Scalar Subqueries (Return single value)

-- Find employees with above-average salary
SELECT
  name,
  salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Column Subqueries (Return one column)

-- Query employees in certain departments
SELECT * FROM employees
WHERE department_id IN (
  SELECT department_id FROM departments
  WHERE location = 'New York'
);

Correlated Subqueries (Subquery references outer table)

-- Find highest-paid employee in each department
SELECT
  e.employee_id,
  e.name,
  e.salary
FROM employees e
WHERE e.salary = (
  SELECT MAX(salary)
  FROM employees
  WHERE department_id = e.department_id
);

Performance Warning: Correlated subqueries execute once for every outer row, causing many database accesses. Use JOINs or window functions instead for much better performance.

CTE — Common Table Expression

Basic CTE (WITH clause)

-- Define a temporary result set, then use in main query
WITH high_earners AS (
  SELECT employee_id, name, salary
  FROM employees
  WHERE salary > 5000
)
SELECT
  department_id,
  COUNT(*) AS count,
  AVG(salary) AS avg_salary
FROM high_earners
GROUP BY department_id;

Recursive CTE (MySQL 8.0+)

-- Generate numbers from 1 to 10 (example)
WITH RECURSIVE numbers AS (
  -- Base query (recursion termination)
  SELECT 1 AS n
  UNION ALL
  -- Recursive query
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

Organization Tree Query

-- Query employee's full management chain (from employee to top manager)
WITH RECURSIVE emp_hierarchy AS (
  -- Base: select specified employee
  SELECT
    employee_id, name, manager_id, 0 AS level
  FROM employees
  WHERE employee_id = 101

  UNION ALL

  -- Recursive: query managers level by level
  SELECT
    e.employee_id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN emp_hierarchy eh ON e.employee_id = eh.manager_id
)
SELECT * FROM emp_hierarchy;

Aggregate Functions — Group Statistics

Common Aggregate Functions

GROUP BY — Aggregate by Group

-- Count employees and average salary by department
SELECT
  department_id,
  COUNT(*) AS emp_count,
  AVG(salary) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

HAVING — Filter After Grouping

-- Query departments with average salary over 4000
SELECT
  department_id,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 4000;

DML — Data Modification

INSERT — Insert Data

-- Insert single row
INSERT INTO employees (name, salary, department_id)
VALUES ('Alice', 5000, 1);

-- Insert multiple rows
INSERT INTO employees (name, salary, department_id) VALUES
('Bob', 4500, 2),
('Charlie', 5500, 1),
('Diana', 4000, 3);

-- Insert from another table
INSERT INTO employees_backup (name, salary, department_id)
SELECT name, salary, department_id
FROM employees
WHERE hire_date > '2024-01-01';

UPDATE — Update Data

-- Update single column
UPDATE employees SET salary = 5500 WHERE employee_id = 1;

-- Update multiple columns
UPDATE employees
SET
  salary = salary * 1.1,
  updated_at = NOW()
WHERE department_id = 1;

-- Bulk update with JOIN
UPDATE employees e
INNER JOIN departments d ON e.department_id = d.department_id
SET e.salary = e.salary * 1.05
WHERE d.location = 'New York';

DELETE — Delete Data

-- Delete rows matching condition
DELETE FROM employees WHERE hire_date < '2020-01-01';

-- Conditional delete with JOIN
DELETE e FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.status = 'inactive';

Safety Tips: - Verify conditions with SELECT before UPDATE/DELETE - Batch large DML operations to avoid long locks - Have backups and rollback plans for critical operations

Built-in Functions

String Functions

Date Functions

Math Functions

Performance Optimization Tips

Index-Friendly Queries

• Create indexes on WHERE clause columns
• Avoid functions on indexed columns: ❌ WHERE YEAR(date) = 2024 → ✓ WHERE date >= '2024-01-01' AND date < '2025-01-01'
• Use BETWEEN instead of multiple OR: ❌ salary = 3000 OR salary = 4000 OR salary = 5000 → ✓ salary BETWEEN 3000 AND 5000

Avoid Full Table Scans

• Use LIMIT to restrict result set size
• Use wildcards only on the right in LIKE: name LIKE 'John%'
• Avoid SELECT *, query only needed columns

Optimize JOINs and Subqueries

• Replace subqueries with JOINs (especially correlated subqueries)
• Use EXISTS instead of IN (when right side is a subquery)
• Filter with WHERE first, then JOIN

Frequently Asked Questions

Q1: How to handle NULL values?

In MySQL, NULL represents "unknown" or "no value". Use IS NULL instead of = NULL: ✓ WHERE email IS NULL ❌ WHERE email = NULL Use COALESCE() or IFNULL() to provide default values for NULL: SELECT name, COALESCE(phone, 'No Phone') FROM users;

Q2: Difference between UNION and UNION ALL?

UNION removes duplicate rows (expensive), UNION ALL keeps all rows (faster). If you know there are no duplicates, use UNION ALL.

Q3: Which is faster: IN vs EXISTS?

When the right side is a subquery, EXISTS is usually faster because it only checks if matching rows exist. IN has to build the complete subquery result set.

Q4: Why must non-aggregate columns be in GROUP BY?

MySQL 5.7+'s strict mode requires all non-aggregate columns in SELECT to be in GROUP BY. This prevents ambiguous results. If a column doesn't need grouping, put it in an aggregate function like MAX(column).

Q5: How to find slow queries?

Use EXPLAIN to analyze query execution plans, checking for full table scans and index usage. In MySQL command line: EXPLAIN SELECT * FROM employees WHERE salary > 5000; Pay attention to the type column: ALL (full scan), index (full index scan), range (range), ref (exact match), const (constant).