Cheat SheetsSQLDML & Querying

DML & Querying — Cheat Sheet

SQL · 8 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org
DML & Querying
SQL8 topicsQuick revision reference
1

SELECT & WHERE Clause

SELECT retrieves columns from tables; WHERE filters rows using comparison, logical, and special operators before any results are returned.

  • ✓Logical processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
  • ✓WHERE is processed before SELECT, so column aliases are not available in WHERE.
  • ✓AND has higher precedence than OR — always use parentheses when mixing them.
  • ✓Avoid SELECT * in production queries; project only needed columns.
  • ✓Filter as early as possible in WHERE to reduce rows before joins and aggregations.
  • ✓In Spring Data JPA, complex WHERE clauses should use @Query or Specifications.
SQL — SELECT/WHERE patterns and common mistakes
-- Bad: SELECT * reads all columns, including LOB columns (slow over network)
SELECT * FROM orders WHERE status = 'pending';

-- Good: project only what you need
SELECT id, user_id, total_amount, created_at
FROM orders
WHERE status = 'pending';

-- Comparison operators
SELECT id, full_name, salary
FROM employees
WHERE salary > 75000
  AND department_id = 3
  AND hired_at >= '2020-01-01';

-- OR with parentheses (critical — AND binds tighter than OR)
SELECT id, email FROM users
WHERE (status = 'active' OR status = 'trial')
  AND created_at >= CURRENT_DATE - INTERVAL '30 days';

-- Column alias NOT usable in WHERE (use the expression again)
SELECT id, total_amount * 1.18 AS total_with_tax   -- alias defined here
FROM orders
WHERE total_amount * 1.18 > 1000;                  -- repeat expression; alias not allowed
2

ORDER BY, LIMIT & OFFSET

ORDER BY sorts result rows; LIMIT restricts the count returned; OFFSET skips rows — together they enable pagination, but naive OFFSET pagination degrades at scale.

  • ✓Without ORDER BY, row order is non-deterministic — never rely on implicit ordering.
  • ✓Always include a unique tiebreaker (id) in ORDER BY when paginating.
  • ✓OFFSET pagination is O(n) per page — performance degrades linearly with page depth.
  • ✓Keyset (cursor) pagination uses WHERE on the last seen value — O(1) per page at any depth.
  • ✓PostgreSQL: NULLS FIRST / NULLS LAST controls NULL sort position explicitly.
  • ✓Spring Data Pageable uses OFFSET internally — switch to keyset for high-page-count APIs.
SQL — ORDER BY with NULLS handling and LIMIT
-- Sort by salary descending, then by name ascending as tiebreaker
SELECT id, full_name, salary, department_id
FROM employees
ORDER BY salary DESC, full_name ASC;

-- NULLs: PostgreSQL default is NULLS LAST for ASC, NULLS FIRST for DESC
-- Make it explicit to avoid surprises
SELECT id, full_name, commission
FROM employees
ORDER BY commission DESC NULLS LAST;   -- NULLs go to the bottom

-- LIMIT: top 5 highest-paid employees
SELECT id, full_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- MySQL LIMIT with OFFSET (page 3, 10 rows per page)
SELECT id, email, created_at
FROM users
ORDER BY created_at DESC, id DESC  -- always include unique tiebreaker!
LIMIT 10 OFFSET 20;
3

DISTINCT, BETWEEN, IN & LIKE

DISTINCT eliminates duplicate result rows; BETWEEN tests range membership; IN tests set membership; LIKE performs pattern matching with wildcard characters.

  • ✓DISTINCT deduplicates on the full projected row — not on a single column.
  • ✓BETWEEN is inclusive: BETWEEN 10 AND 20 includes both 10 and 20.
  • ✓NOT IN with a NULL in the list returns zero rows due to three-valued logic.
  • ✓LIKE with a leading wildcard ('%text') cannot use a B-Tree index.
  • ✓LIKE 'prefix%' (no leading wildcard) CAN use a B-Tree index.
  • ✓Use pg_trgm GIN index for efficient LIKE '%substring%' queries in PostgreSQL.
SQL — DISTINCT, BETWEEN, IN with NOT IN NULL pitfall
-- DISTINCT: unique product categories ordered by a customer
SELECT DISTINCT product_category
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.user_id = 42;

-- DISTINCT ON (PostgreSQL only): keep one row per distinct value in given columns
SELECT DISTINCT ON (user_id)
    user_id, order_id, total_amount, created_at
FROM orders
ORDER BY user_id, created_at DESC;   -- keeps latest order per user

-- BETWEEN (inclusive on both ends)
SELECT id, total_amount
FROM orders
WHERE total_amount BETWEEN 100.00 AND 500.00
  AND created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- IN: equivalent to multiple OR conditions (cleaner syntax)
SELECT id, email FROM users
WHERE status IN ('active', 'trial', 'grace_period');

-- NOT IN pitfall: if the list contains a NULL, entire result can be empty!
SELECT id FROM users
WHERE id NOT IN (1, 2, NULL);  -- returns 0 rows! NULL poisons NOT IN
-- Fix: use NOT EXISTS or filter NULLs from the list
4

NULL Handling

NULL represents the absence of a value; SQL uses three-valued logic (TRUE/FALSE/UNKNOWN), so NULL comparisons require IS NULL/IS NOT NULL and functions like COALESCE and NULLIF.

  • ✓Any comparison to NULL returns UNKNOWN — always use IS NULL / IS NOT NULL.
  • ✓Three-valued logic: WHERE filters include only TRUE rows; UNKNOWN rows are excluded.
  • ✓COUNT(*) counts all rows; COUNT(col) ignores NULLs in that column.
  • ✓COALESCE(a, b, c) returns the first non-NULL argument — ANSI standard.
  • ✓NULLIF(a, b) returns NULL when a = b — useful to prevent division-by-zero.
  • ✓AVG(col) divides by the count of non-NULL rows, not total rows.
SQL — NULL comparisons and three-valued logic
-- Correct: IS NULL / IS NOT NULL
SELECT id, full_name, manager_id
FROM employees
WHERE manager_id IS NULL;     -- finds top-level employees (no manager)

-- Wrong: = NULL never matches anything
SELECT id FROM employees WHERE manager_id = NULL;   -- returns 0 rows!

-- Three-valued logic demonstration
SELECT
    NULL = NULL        AS eq_null_null,     -- UNKNOWN → false in WHERE
    NULL IS NULL       AS is_null,          -- TRUE
    NULL != 5          AS neq_five,         -- UNKNOWN
    NOT NULL           AS not_null_val,     -- UNKNOWN
    TRUE AND NULL      AS true_and_null,    -- UNKNOWN
    FALSE AND NULL     AS false_and_null,   -- FALSE (short-circuit)
    TRUE OR NULL       AS true_or_null;     -- TRUE (short-circuit)

-- NULL in aggregate functions
SELECT
    COUNT(*)           AS total_rows,          -- counts ALL rows
    COUNT(commission)  AS rows_with_commission,-- ignores NULLs
    AVG(commission)    AS avg_commission,      -- ignores NULLs (not divides by total)
    SUM(commission)    AS total_commission     -- NULLs treated as 0
FROM employees;
5

CASE Expressions

CASE is SQL's conditional expression; simple CASE compares one value to multiple options, while searched CASE evaluates independent boolean conditions for flexible branching.

  • ✓CASE is an expression, not a statement — it can appear anywhere an expression is valid.
  • ✓Simple CASE compares one value to constants; searched CASE evaluates arbitrary conditions.
  • ✓CASE short-circuits: it stops evaluating at the first TRUE branch.
  • ✓CASE inside SUM/COUNT implements conditional aggregation (the SQL pivot pattern).
  • ✓CASE in ORDER BY enables custom sort sequences beyond simple ASC/DESC.
  • ✓Without ELSE, unmatched CASE returns NULL — always add an explicit ELSE.
SQL — simple CASE, searched CASE, and CASE in GROUP BY
-- Simple CASE: map status code to display label
SELECT
    id,
    total_amount,
    CASE status
        WHEN 'pending'   THEN 'Awaiting Payment'
        WHEN 'paid'      THEN 'Processing'
        WHEN 'shipped'   THEN 'On the Way'
        WHEN 'delivered' THEN 'Delivered'
        ELSE 'Unknown'
    END AS status_label
FROM orders;

-- Searched CASE: range-based salary banding
SELECT
    id,
    full_name,
    salary,
    CASE
        WHEN salary < 40000                  THEN 'Junior'
        WHEN salary BETWEEN 40000 AND 80000  THEN 'Mid-level'
        WHEN salary BETWEEN 80001 AND 120000 THEN 'Senior'
        ELSE 'Principal'
    END AS salary_band
FROM employees;

-- CASE in GROUP BY for custom bucketing
SELECT
    CASE
        WHEN total_amount < 500   THEN 'small'
        WHEN total_amount < 2000  THEN 'medium'
        ELSE 'large'
    END         AS order_size,
    COUNT(*)    AS order_count,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY 1;
6

String Functions

SQL string functions (CONCAT, SUBSTRING, TRIM, UPPER, LOWER, REPLACE, LENGTH, REGEXP) manipulate text columns for data cleaning, formatting, and search operations.

  • ✓String functions on indexed columns in WHERE prevent index use — create expression indexes.
  • ✓CONCAT_WS skips NULL values and inserts a separator — cleaner than nested CONCALTs.
  • ✓CHAR_LENGTH returns character count; LENGTH returns byte count (different for UTF-8 multi-byte chars).
  • ✓TRIM is essential in ETL pipelines where source data contains leading/trailing spaces.
  • ✓STRING_AGG (PostgreSQL) / GROUP_CONCAT (MySQL) collapse rows into a delimited string.
  • ✓Storing CSV in a column and parsing with string functions is an anti-pattern — normalise instead.
SQL — core string functions (PostgreSQL / MySQL)
-- PostgreSQL / MySQL string functions

-- CONCAT: join strings (CONCAT_WS includes separator, skips NULLs)
SELECT CONCAT(first_name, ' ', last_name)             AS full_name FROM employees;
SELECT CONCAT_WS(', ', city, state, country)          AS address   FROM users;

-- UPPER / LOWER: normalise for case-insensitive comparison
SELECT id FROM users WHERE LOWER(email) = 'alice@example.com';
-- Better: use a case-insensitive collation or expression index

-- TRIM / LTRIM / RTRIM: remove whitespace (important in ETL)
UPDATE users SET email = TRIM(email) WHERE email != TRIM(email);

-- LENGTH / CHAR_LENGTH: byte length vs character length (matters for multi-byte UTF-8)
SELECT id, CHAR_LENGTH(description) FROM products WHERE CHAR_LENGTH(description) > 500;

-- SUBSTRING: extract part of a string (1-based index)
SELECT SUBSTRING(phone, 1, 3) AS country_code FROM users; -- e.g. '+91'

-- REPLACE: substitute occurrences
SELECT REPLACE(description, 'old_brand', 'new_brand') FROM products;

-- POSITION / STRPOS: find first occurrence
SELECT POSITION('@' IN email)  AS at_pos FROM users;   -- standard
SELECT STRPOS(email, '@')      AS at_pos FROM users;   -- PostgreSQL alias
7

Date/Time Functions

Date/time functions (NOW, DATEDIFF, DATE_TRUNC, EXTRACT, DATE_ADD) are essential for time-series analysis, reporting windows, and expiry/scheduling logic in backend systems.

  • ✓Store all timestamps in UTC; convert to local time in the application layer.
  • ✓NOW() returns transaction start time — consistent within a transaction.
  • ✓DATE_TRUNC is the key function for grouping time-series data by day/week/month.
  • ✓Using functions on indexed date columns in WHERE (YEAR(col)=2024) prevents index use.
  • ✓Fix: use range predicates (col >= '2024-01-01' AND col < '2025-01-01').
  • ✓TIMESTAMPTZ (PostgreSQL) stores timezone offset; TIMESTAMP does not — prefer TIMESTAMPTZ.
SQL — date/time functions (PostgreSQL + MySQL)
-- Current time
SELECT NOW();               -- transaction start time (consistent in tx)
SELECT CURRENT_TIMESTAMP;   -- ANSI equivalent of NOW()
SELECT CLOCK_TIMESTAMP();   -- actual wall-clock time (PostgreSQL)

-- DATE_TRUNC: truncate to start of period (PostgreSQL)
SELECT DATE_TRUNC('day',   created_at) AS day_start   FROM orders;
SELECT DATE_TRUNC('month', created_at) AS month_start FROM orders;
SELECT DATE_TRUNC('week',  created_at) AS week_start  FROM orders;

-- MySQL equivalent: DATE_FORMAT or DATE
SELECT DATE(created_at)                          AS day_start   FROM orders;
SELECT DATE_FORMAT(created_at, '%Y-%m-01')       AS month_start FROM orders;

-- EXTRACT: pull a single field out of a timestamp
SELECT EXTRACT(YEAR  FROM created_at) AS yr,
       EXTRACT(MONTH FROM created_at) AS mo,
       EXTRACT(DOW   FROM created_at) AS day_of_week  -- 0=Sunday (PostgreSQL)
FROM orders;

-- MySQL: YEAR(), MONTH(), DAYOFWEEK()
SELECT YEAR(created_at), MONTH(created_at) FROM orders;

-- Interval arithmetic: orders created in the last 30 days
SELECT id, created_at FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days';          -- PostgreSQL
-- MySQL:
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);
8

INSERT, UPDATE & DELETE

INSERT adds rows, UPDATE modifies existing rows, and DELETE removes rows; each supports set-based operations and must be used carefully to avoid unintended mass mutations.

  • ✓Batch INSERT (multi-row VALUES) is far faster than single-row inserts in a loop.
  • ✓UPDATE and DELETE without WHERE affect ALL rows — always double-check the predicate.
  • ✓UPSERT (ON CONFLICT DO UPDATE / ON DUPLICATE KEY UPDATE) is atomic.
  • ✓RETURNING clause (PostgreSQL) fetches modified rows without a second SELECT round-trip.
  • ✓TRUNCATE is DDL and resets sequences; DELETE is DML and is row-by-row logged.
  • ✓In Spring Data JPA, use @Modifying + @Query for bulk UPDATE/DELETE to avoid loading entities.
SQL — INSERT patterns, batch insert, UPSERT
-- Single-row insert
INSERT INTO products (sku, name, price, is_active)
VALUES ('ELEC-001', 'Wireless Headphones', 2999.00, TRUE);

-- Multi-row batch insert (single round-trip, much faster)
INSERT INTO products (sku, name, price, is_active) VALUES
    ('ELEC-002', 'USB-C Hub',         1499.00, TRUE),
    ('ELEC-003', 'Mechanical Keyboard', 3499.00, TRUE),
    ('ELEC-004', 'Webcam HD',          1999.00, FALSE);

-- INSERT ... SELECT: copy rows from another table
INSERT INTO products_archive
SELECT * FROM products WHERE is_active = FALSE;

-- UPSERT: insert or update on conflict (PostgreSQL)
INSERT INTO user_preferences (user_id, theme, notifications_enabled)
VALUES (42, 'dark', TRUE)
ON CONFLICT (user_id)
DO UPDATE SET
    theme                 = EXCLUDED.theme,
    notifications_enabled = EXCLUDED.notifications_enabled,
    updated_at            = NOW();

-- MySQL equivalent
INSERT INTO user_preferences (user_id, theme, notifications_enabled)
VALUES (42, 'dark', 1)
ON DUPLICATE KEY UPDATE
    theme                 = VALUES(theme),
    notifications_enabled = VALUES(notifications_enabled);
Learn this free with Aria, your AI tutor → AiCanCode.org/learn/sql