SQL Cheat Sheet

Concept

A compact quick reference. Standard syntax is shown; dialect variants are noted in comments.

Why It Matters

Useful for revision before interviews and as a desk reference while working.

Syntax

GROUP BY and HAVING

SELECT [DISTINCT] col1, col2 AS alias, expr
FROM table t
[JOIN other o ON o.key = t.key]
[WHERE condition]
[GROUP BY col1]
[HAVING aggregate_condition]
[ORDER BY col1 [ASC|DESC] [NULLS FIRST|LAST]]
[LIMIT n OFFSET m];                 -- SQL Server: TOP (n) / OFFSET m ROWS FETCH NEXT n ROWS ONLY
                                    -- Standard/Oracle: FETCH FIRST n ROWS ONLY
-- Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

=  <>  !=  <  >  <=  >=
AND  OR  NOT
BETWEEN a AND b            -- inclusive
IN (v1, v2)   NOT IN (...) -- NOT IN breaks with NULLs
LIKE 'A%'  LIKE '_b%'      -- % any chars, _ one char
IS NULL   IS NOT NULL
EXISTS (subquery)   NOT EXISTS (subquery)
x IS DISTINCT FROM y       -- null-safe (MySQL: NOT (x <=> y))

INSERT INTO t (c1, c2) VALUES (v1, v2);
INSERT INTO t (c1, c2) VALUES (v1, v2), (v3, v4);
INSERT INTO t (c1, c2) SELECT a, b FROM s WHERE ...;
INSERT INTO t (c1) VALUES (v1) RETURNING id;             -- PostgreSQL/SQLite; SQL Server: OUTPUT INSERTED.id
INSERT INTO t (k, v) VALUES (1, 'x')
ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v;             -- PostgreSQL/SQLite upsert
-- MySQL: ON DUPLICATE KEY UPDATE v = VALUES(v); SQL Server/Oracle: MERGE

UPDATE t SET c1 = v1, c2 = c2 + 1 WHERE condition;
UPDATE t SET c = s.c FROM s WHERE s.id = t.id;            -- PostgreSQL
UPDATE t JOIN s ON s.id = t.id SET t.c = s.c;             -- MySQL
UPDATE t SET c = s.c FROM t JOIN s ON s.id = t.id;        -- SQL Server

DELETE FROM t WHERE condition;
DELETE FROM t WHERE id IN (SELECT id FROM ...);
TRUNCATE TABLE t;                                          -- all rows, fast, DDL

CREATE TABLE orders (
    order_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- MySQL: AUTO_INCREMENT; SQL Server: IDENTITY(1,1)
    customer_id  BIGINT NOT NULL,
    order_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    status       VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    total        DECIMAL(12,2) NOT NULL CHECK (total >= 0),
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id) ON DELETE RESTRICT,
    CONSTRAINT chk_orders_status CHECK (status IN ('PENDING', 'SHIPPED', 'DELIVERED', 'CANCELLED'))
);

ALTER TABLE t ADD COLUMN c VARCHAR(50);                    -- SQL Server/Oracle: ADD c ...
ALTER TABLE t DROP COLUMN c;
ALTER TABLE t RENAME COLUMN a TO b;                        -- SQL Server: sp_rename
ALTER TABLE t ALTER COLUMN c TYPE VARCHAR(100);            -- PostgreSQL
ALTER TABLE t MODIFY c VARCHAR(100) NOT NULL;              -- MySQL
ALTER TABLE t ALTER COLUMN c VARCHAR(100) NOT NULL;        -- SQL Server
ALTER TABLE t ADD CONSTRAINT uq_t_c UNIQUE (c);
ALTER TABLE t DROP CONSTRAINT uq_t_c;
ALTER TABLE t RENAME TO t2;

DROP TABLE t;
DROP TABLE IF EXISTS t;
DROP TABLE t CASCADE;                                      -- PostgreSQL (Oracle: CASCADE CONSTRAINTS)

PRIMARY KEY (c)            -- unique + not null, one per table
FOREIGN KEY (c) REFERENCES p (pk) [ON DELETE CASCADE | SET NULL | RESTRICT]
UNIQUE (c1, c2)
NOT NULL
CHECK (condition)          -- accepts NULL (UNKNOWN)
DEFAULT value

FROM a INNER JOIN b ON b.k = a.k          -- matches only
FROM a LEFT  JOIN b ON b.k = a.k          -- all a (filters on b go in ON)
FROM a RIGHT JOIN b ON b.k = a.k          -- all b
FROM a FULL  JOIN b ON b.k = a.k          -- all a and b (not MySQL)
FROM a CROSS JOIN b                       -- every combination
FROM emp e JOIN emp m ON m.id = e.mgr_id  -- self join
FROM a LEFT JOIN b ON b.k = a.k WHERE b.k IS NULL   -- anti-join: a without b

Example

Quick "which tool?" guide:

How many per group?

GROUP BY + COUNT Rows with no match LEFT JOIN... IS NULL / NOT EXISTS Top N per group ROW_NUMBER / DENSE_RANK in CTE Compare with previous row

SUM() OVER (ORDER BY... ROWS UNBOUNDED PRECEDING) Rows to columns SUM(CASE...) / PIVOT Columns to rows UNION ALL / UNPIVOT / LATERAL VALUES ROW_NUMBER()... WHERE rn > 1

Expected Result

You can reconstruct any common query from this sheet within a few minutes.

Common Mistakes

Using the cheat sheet without checking your DBMS's dialect notes.

Best Practices

Print it, annotate it with your own dialect's syntax, and review it before interviews.

Practice Questions

Without looking, write the frame clause for a 7-row moving average.

Write the anti-join pattern two ways.