SQL Commands You Actually Write Daily: A SQL Cheat Sheet for SELECT, JOIN, and GROUP BY

Most of the SQL you write in a year is a small set of patterns repeated thousands of times. You filter rows, you stitch tables together, you roll numbers up into a summary. Get those three right and you cover ninety percent of real work. Get the edge cases wrong — a NULL comparison, a JOIN that silently drops rows, a GROUP BY missing a column — and you ship a report that looks correct and is quietly off by a few hundred rows. This guide walks through the daily commands and the traps that hide inside them.

The SELECT You Write Every Day

A working query has a fixed skeleton, and the clauses run in a fixed order regardless of how you type them: FROM and JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. Knowing that order explains why you cannot use a column alias from the SELECT list inside WHERE — WHERE runs before SELECT exists.

A realistic shape:

SELECT country, COUNT(*) AS signups
FROM users
WHERE created_at >= '2026-01-01'
  AND status = 'active'
GROUP BY country
HAVING COUNT(*) > 50
ORDER BY signups DESC
LIMIT 10;

WHERE filters individual rows before grouping. HAVING filters the groups after aggregation. People reach for HAVING to filter raw rows and then wonder why the query is slow — push every row-level condition into WHERE and let HAVING handle only the aggregates. Other daily basics worth keeping at your fingertips: DISTINCT to dedupe, IN (a, b, c) for set membership, BETWEEN x AND y for inclusive ranges, LIKE 'foo%' for prefix matching, CASE WHEN ... THEN ... END for inline branching, and COALESCE(col, fallback) to replace NULLs with a default.

JOIN Types, and the One Difference That Matters

A JOIN combines rows from two tables on a matching condition. The type controls what happens when there is no match.

• INNER JOIN keeps only rows that match on both sides. No order? The order vanishes from the result.
• LEFT JOIN keeps every row from the left table; columns from the right table come back NULL when there is no match.
• RIGHT JOIN is the mirror image — every row from the right table survives.
• FULL OUTER JOIN keeps unmatched rows from both sides (PostgreSQL has it natively; MySQL fakes it with a UNION).
• CROSS JOIN pairs every left row with every right row — useful for generating combinations, dangerous by accident.

The INNER versus LEFT distinction is where reports go wrong. Say you want customers and their orders. An INNER JOIN gives you only customers who have at least one order. A LEFT JOIN gives you all customers, with NULL order columns for the ones who never bought anything. If you are counting "customers with no orders," you must use a LEFT JOIN and then test WHERE o.id IS NULL — that anti-join pattern is the standard way to find rows in one table missing from another.

-- Customers who have never placed an order
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

A Worked Scenario: Joining and Aggregating

Here is a request I get in some form almost every week: total revenue per customer, including customers who spent nothing, sorted by who spent the most. Two tables — customers and orders — joined and then rolled up.

SELECT
  c.id,
  c.name,
  COUNT(o.id)                     AS order_count,
  COALESCE(SUM(o.amount), 0)      AS lifetime_value
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'paid'
GROUP BY c.id, c.name
HAVING COALESCE(SUM(o.amount), 0) >= 0
ORDER BY lifetime_value DESC;

Three things make this correct. First, it is a LEFT JOIN so zero-spend customers stay in the result. Second — and this is the trap — the o.status = 'paid' filter lives in the ON clause, not in WHERE. If you move it to WHERE, every customer with no paid order gets a NULL o.status, that NULL fails the = 'paid' test, the row is discarded, and your LEFT JOIN silently degrades into an INNER JOIN. Third, COUNT(o.id) counts only non-NULL order ids, so customers with no orders correctly show zero instead of one. COUNT(*) would have counted the single NULL-filled row as one — a classic off-by-one.

The Mistakes That Cost Real Money

The first time I shipped a "users with no orders" report, finance came back saying it was missing eight hundred rows. The bug was exactly the one above: a status filter sitting in WHERE instead of ON. It took me longer to believe the query was wrong than to fix it. Here are the traps worth burning into memory.

NULL is not equal to NULL. WHERE col = NULL is always false, even when the column is full of nulls. Use IS NULL and IS NOT NULL. This extends to NOT IN (SELECT ...): if the subquery returns even one NULL, the entire NOT IN returns zero rows. Use NOT EXISTS instead, which is null-safe and usually faster.

GROUP BY rules. Every non-aggregated column in your SELECT must appear in GROUP BY. PostgreSQL rejects a violation outright; older MySQL silently returned an arbitrary value from each group, which is worse because it looks like it worked. If you select name alongside COUNT(*), group by name (or group by the primary key and let functional dependency cover the rest in Postgres).

DELETE and UPDATE without WHERE. DELETE FROM orders with no WHERE empties the table. Type the WHERE clause first, or wrap the statement in a transaction and check the affected row count before you COMMIT.

Implicit casts kill indexes. Pass a string to an indexed timestamp column and the database casts every row to compare, ignoring the index and falling back to a full scan. Match the literal's type to the column's type.

Quick Reference

Filter rows ............ WHERE col = val / IN (...) / BETWEEN a AND b
Filter groups .......... HAVING COUNT(*) > n
Find nulls ............. WHERE col IS NULL          (never = NULL)
All left rows .......... LEFT JOIN ... ON ...
Anti-join .............. LEFT JOIN ... WHERE right.id IS NULL
Count non-nulls ........ COUNT(col)   vs  COUNT(*) counts every row
Dedupe ................. SELECT DISTINCT col
Top N per group ........ ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC)
Running total .......... SUM(x) OVER (ORDER BY d)
Safe upsert (PG) ....... INSERT ... ON CONFLICT (id) DO UPDATE SET ...
Null fallback .......... COALESCE(col, default)

When the syntax forks between MySQL, PostgreSQL, and SQLite — auto-increment, string concatenation, LIMIT versus OFFSET FETCH, JSON handling — keep the searchable SQL Cheatsheet open in a tab. Every entry carries the pitfall line, a copy-ready example, and a dialect tag, and you can search statement, description, and example text at once. And since so much daily SQL work is really pattern matching on text — LIKE, splitting log lines, validating inputs before they hit the database — the Regex Cheatsheet pairs with it well.

Master the daily skeleton, respect NULL, keep your filters on the right side of ON versus WHERE, and most "mysterious" SQL bugs stop happening before they reach a report.

---

Made by Toolora · Updated 2026-06-13