---
title: "SQL Cheat Sheet: Commands, Clause Order and Dialects"
description: "This SQL cheat sheet puts the commands you look up most in one table, then shows the order SQL actually runs clauses in, joins, aggregates, window functions…"
canonical: "https://www.aifordatabase.com/blog/sql-cheat-sheet/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-10-25
updated: 2026-10-25
---

# SQL Cheat Sheet: Commands, Clause Order and Dialects

**TL;DR:** This SQL cheat sheet puts the commands you look up most in one table, then shows the order SQL actually runs clauses in, joins, aggregates, window functions and CTEs. Where PostgreSQL, MySQL and SQL Server disagree (row limits, upserts, string matching), the syntax is shown side by side so you can copy the version your database accepts.

A SQL cheat sheet is a one-page reference of SQL statement syntax. The core SQL commands cheat sheet fits in five groups: querying (`SELECT`, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`), changing data (`INSERT`, `UPDATE`, `DELETE`, `MERGE`), defining structure (`CREATE`, `ALTER`, `DROP`), controlling access (`GRANT`, `REVOKE`) and transactions (`BEGIN`, `COMMIT`, `ROLLBACK`). Most mistakes come from two things this page covers early: the order clauses are evaluated in, and the dialect differences between PostgreSQL, MySQL and SQL Server.

## SQL statement cheat sheet: the lookup table

| Task | Syntax | Notes |
| --- | --- | --- |
| Select columns | `SELECT a, b FROM t;` | `SELECT *` only for exploring |
| Remove duplicates | `SELECT DISTINCT a FROM t;` | Applies to the whole row selected |
| Filter rows | `WHERE amount > 100 AND status = 'paid'` | Runs before grouping |
| Pattern match | `WHERE email LIKE '%@acme.com'` | `%` any chars, `_` one char |
| List / range | `WHERE id IN (1,2,3)`, `BETWEEN 1 AND 10` | `BETWEEN` is inclusive |
| Null check | `WHERE deleted_at IS NULL` | `= NULL` is never true |
| Sort | `ORDER BY created_at DESC` | Only guaranteed order |
| Aggregate | `COUNT(*)`, `SUM(x)`, `AVG(x)`, `MIN`, `MAX` | `COUNT(col)` skips NULLs |
| Group | `GROUP BY country HAVING COUNT(*) > 5` | `HAVING` filters groups |
| Join | `FROM a JOIN b ON b.a_id = a.id` | See joins section |
| Combine results | `UNION`, `UNION ALL`, `INTERSECT`, `EXCEPT` | `UNION` removes duplicates |
| Insert | `INSERT INTO t (a, b) VALUES (1, 'x');` | Multi-row VALUES works everywhere listed |
| Update | `UPDATE t SET a = 2 WHERE id = 7;` | Forget `WHERE` and every row changes |
| Delete | `DELETE FROM t WHERE id = 7;` | `TRUNCATE` empties the table |
| Create table | `CREATE TABLE t (id INT PRIMARY KEY, ...);` | Add `NOT NULL`, `UNIQUE`, `FOREIGN KEY` |
| Change table | `ALTER TABLE t ADD COLUMN c TEXT;` |  |
| Index | `CREATE INDEX idx_t_a ON t (a);` | Speeds `WHERE`/`JOIN` on `a` |
| Transaction | `BEGIN; ... COMMIT;` or `ROLLBACK;` | SQL Server: `BEGIN TRANSACTION` |

## What are the basic SQL commands?

The basic SQL commands are `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `ALTER` and `DROP`. They split into DQL/DML (reading and changing rows) and DDL (changing structure), with `GRANT`/`REVOKE` (DCL) and `COMMIT`/`ROLLBACK` (TCL) completing the set. A typical analytical query uses only `SELECT` with its clauses:

```
SELECT country, COUNT(*) AS customers, SUM(mrr) AS total_mrr
FROM customers
WHERE status = 'active'
GROUP BY country
HAVING SUM(mrr) > 1000
ORDER BY total_mrr DESC
LIMIT 10;
```

## What is the order of SQL clauses?

SQL is written in one order and evaluated in another. You write `SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT`, but the database logically processes them like this:

| Step | Clause | What it does |
| --- | --- | --- |
| 1 | `FROM` / `JOIN` | Builds the row set |
| 2 | `WHERE` | Drops rows |
| 3 | `GROUP BY` | Collapses rows into groups |
| 4 | `HAVING` | Drops groups |
| 5 | `SELECT` | Computes output columns and aliases; window functions run here |
| 6 | `DISTINCT` | Removes duplicate output rows |
| 7 | `ORDER BY` | Sorts |
| 8 | `LIMIT` / `OFFSET` / `TOP` | Cuts the result |

![SQL evaluates FROM first and the row limit last, regardless of written order](https://cdn.sanity.io/images/upsr56wu/production/bb16d2c3364f9cfdb1dc05fb862efa87f47bda66-1344x326.png?w=1280)

FIG 01 — Aliases defined in SELECT are not visible to WHERE.

This explains the most common error: you cannot use a `SELECT` alias in `WHERE`, because `WHERE` runs first. `ORDER BY` can use the alias because it runs after. It also explains why you filter aggregates with `HAVING`, not `WHERE`. For a deeper look at filtering, see our guide to [CASE WHEN in PostgreSQL](https://www.aifordatabase.com/blog/postgresql-case-when/).

## Joins cheat sheet

| Join | Returns |
| --- | --- |
| `INNER JOIN` | Rows with a match in both tables |
| `LEFT JOIN` | All left rows, NULLs where the right has no match |
| `RIGHT JOIN` | All right rows (rewrite as LEFT for readability) |
| `FULL OUTER JOIN` | All rows from both sides; not in MySQL |
| `CROSS JOIN` | Every combination of rows |
| Self join | A table joined to itself with two aliases |

```
-- customers with no orders
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
```

![Ana has two orders so she appears twice in the join, Ben once](https://cdn.sanity.io/images/upsr56wu/production/fb39d6d8e23105f32d874b78b81903dd0f74188e-1344x410.png?w=1280)

FIG 02 — A join returns one row per match, so totals can double count.

MySQL has no `FULL OUTER JOIN`; emulate it with a `LEFT JOIN` `UNION` a `RIGHT JOIN`. Joining when you only need columns from several tables without a key is covered in [selecting from multiple tables without JOIN](https://www.aifordatabase.com/blog/sql-select-from-multiple-tables-without-join-2026/).

## What differs between MySQL and PostgreSQL syntax?

The core of SQL is portable; row limits, upserts, string handling and identifiers are not. These are the differences you hit most:

|  |  | Task | PostgreSQL | MySQL | SQL Server |
| --- | --- | --- | --- | --- | --- |
| First 10 rows | `LIMIT 10` | `LIMIT 10` | `SELECT TOP (10)` or `OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY` |  |  |
| Skip 20, take 10 | `LIMIT 10 OFFSET 20` | `LIMIT 20, 10` or `LIMIT 10 OFFSET 20` | `OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY` |  |  |
| Upsert | `INSERT ... ON CONFLICT (id) DO UPDATE` | `INSERT ... ON DUPLICATE KEY UPDATE` | `MERGE` |  |  |
| Case-insensitive match | `ILIKE` | `LIKE` (default collations are case-insensitive) | `LIKE` (depends on collation) |  |  |
| Concatenate | `a \` | `\` | `b` or `CONCAT(a, b)` | `CONCAT(a, b)` | `a + b` or `CONCAT(a, b)` |
| Quote identifiers | `"column"` | `` ` `` column | \[column\]\` |  |  |
| Return changed rows | `RETURNING *` | not supported | `OUTPUT inserted.*` |  |  |
| Current time | `now()` | `NOW()` | `GETDATE()` / `SYSDATETIME()` |  |  |
| Auto-increment | `GENERATED ALWAYS AS IDENTITY` | `AUTO_INCREMENT` | `IDENTITY(1,1)` |  |  |

```
-- PostgreSQL upsert
INSERT INTO plans (id, price) VALUES (1, 30)
ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price
RETURNING *;

-- MySQL upsert
INSERT INTO plans (id, price) VALUES (1, 30)
ON DUPLICATE KEY UPDATE price = 30;
```

Version notes worth knowing: `ILIKE` is a PostgreSQL extension, not standard SQL ([PostgreSQL docs](https://www.postgresql.org/docs/current/functions-matching.html)). PostgreSQL got `MERGE` in version 15 ([release notes](https://www.postgresql.org/docs/release/15.0/)). In SQL Server, `TOP ... WITH TIES` requires `ORDER BY` and can return more rows than asked for when the last value ties ([Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql)). In every dialect, a row limit without `ORDER BY` returns an unpredictable subset, and rows skipped by a large `OFFSET` are still computed by the server ([PostgreSQL docs](https://www.postgresql.org/docs/current/queries-limit.html)). Date functions differ most of all; see [GETDATE in PostgreSQL](https://www.aifordatabase.com/blog/postgresql-getdate/) and [DATEDIFF in PostgreSQL](https://www.aifordatabase.com/blog/postgresql-datediff/).

## How do window functions work?

A window function computes a value for each row from a set of related rows (the window) without collapsing them the way `GROUP BY` does. You define the window with `OVER (PARTITION BY ... ORDER BY ...)`. They are supported in PostgreSQL, MySQL 8.0 and later, SQL Server and SQLite 3.25.0 and later ([SQLite docs](https://www.sqlite.org/windowfunctions.html)).

| Function | Returns |
| --- | --- |
| `ROW_NUMBER()` | 1, 2, 3 per partition, no ties |
| `RANK()` / `DENSE_RANK()` | Rank with gaps / without gaps |
| `LAG(x)` / `LEAD(x)` | Previous / next row's value |
| `SUM(x) OVER (ORDER BY d)` | Running total |
| `NTILE(4)` | Quartile bucket |

```
-- latest order per customer
SELECT *
FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
) x
WHERE rn = 1;
```

Window functions run at the `SELECT` step, so you cannot filter on them in `WHERE`; wrap the query as above. More patterns in our [PARTITION BY guide](https://www.aifordatabase.com/blog/sql-partition-by/).

## CTEs (WITH queries)

A common table expression is a named subquery defined with `WITH` that exists for one statement. It makes multi-step logic readable, and `WITH RECURSIVE` walks hierarchies such as org charts or category trees.

```
WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;
```

In PostgreSQL a non-recursive CTE referenced once is inlined into the main query; add `MATERIALIZED` or `NOT MATERIALIZED` after `AS` to override that ([PostgreSQL docs](https://www.postgresql.org/docs/current/queries-with.html)). MySQL supports CTEs from 8.0. `date_trunc` is PostgreSQL; in MySQL use `DATE_FORMAT(created_at, '%Y-%m-01')`.

VIDEO · [All SQL Fundamentals Explained in 10 Minutes | Learn SQL Basics for Beginners | SQL Crash Course](https://www.youtube.com/watch?v=3s0lFtUrhSQ) (Learn with Whiteboard)

## Common mistakes the cheat sheet saves you from

*   `WHERE col = NULL` returns nothing; use `IS NULL`.
*   `COUNT(col)` and `COUNT(*)` differ when `col` has NULLs.
*   `NOT IN (subquery)` returns no rows if the subquery contains a NULL; use `NOT EXISTS`.
*   A `LEFT JOIN` turns into an inner join when you filter the right table in `WHERE`; move that condition into `ON`.
*   Joining two one-to-many tables multiplies rows and inflates `SUM`; aggregate each side in a CTE first. Duplicate rows are easy to find with [this duplicates query](https://www.aifordatabase.com/blog/find-duplicates-sql-query/).

## When you'd rather not write the query

A cheat sheet helps when you know SQL and forget syntax. When the person with the question is in ops, sales or finance, the bottleneck is the query itself. AI for Database connects to PostgreSQL, MySQL, SQL Server and other sources, answers plain-English questions with the SQL it ran shown next to the result, and can pin the answer to a self-refreshing dashboard or an alert. Try AI for Database free at aifordatabase.com, sample data included.

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### What are the basic SQL commands?

SELECT reads rows, INSERT adds them, UPDATE changes them, DELETE removes them, and CREATE, ALTER and DROP manage tables and indexes. GRANT/REVOKE control access and COMMIT/ROLLBACK end transactions.

### What is the order of SQL clauses?

Written order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Logical order is FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT, which is why WHERE cannot see SELECT aliases.

### How do window functions work?

They compute a value per row across a window defined by OVER (PARTITION BY ... ORDER BY ...) while keeping every row, unlike GROUP BY. Use them for rankings, running totals and previous-row comparisons.

### What differs between MySQL and PostgreSQL syntax?

Mainly upserts (ON DUPLICATE KEY UPDATE vs ON CONFLICT), case-insensitive matching (LIKE collation vs ILIKE), identifier quoting (backticks vs double quotes), RETURNING (PostgreSQL only), FULL OUTER JOIN (PostgreSQL only) and date functions.

### What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. HAVING SUM(amount) > 100 works, WHERE SUM(amount) > 100 is an error.

### Is SQL the same in every database?

The ANSI core (SELECT, joins, GROUP BY, CTEs, window functions) is shared. Row limits, upserts, string and date functions, and procedural code differ by vendor, so check the dialect table above before copying a query between databases.

Read next

*   [FILTER IN SQL: WHERE, HAVING AND FILTER REFERENCE →](https://www.aifordatabase.com/blog/filter-in-sql/)
*   [POSTGRES TIMESTAMPTZ: WHAT IT STORES AND WHEN TO USE IT →](https://www.aifordatabase.com/blog/postgres-timestamptz/)
*   [HOW TO FIND DUPLICATES IN SQL (AND DELETE THEM SAFELY) →](https://www.aifordatabase.com/blog/find-duplicates-sql-query/)

Source: https://www.aifordatabase.com/blog/sql-cheat-sheet/