SQL Cheat Sheet: The Most Common Commands

SQL (Structured Query Language) is the universal language for interacting with relational databases. Whether you use PostgreSQL, MySQL, SQLite, or SQL Server, the core commands remain largely the same. This cheat sheet covers the most essential SQL commands you will reach for in day-to-day work.

Understanding SQL is a foundational skill for any developer or data analyst. Relational databases power most web applications, from small side projects to enterprise systems handling millions of transactions. Mastery of SELECT queries, JOINs, aggregations, and schema modifications will let you extract insights, build APIs, and maintain data integrity.

SELECT

The SELECT statement retrieves data from one or more tables. It is the command you will use most frequently.

-- Basic query
SELECT column1, column2 FROM table_name;

-- All columns
SELECT * FROM table_name;

-- Distinct values
SELECT DISTINCT column_name FROM table_name;

-- Aliases
SELECT column_name AS alias FROM table_name;

-- Calculated column
SELECT price * quantity AS total FROM orders;

Always prefer listing specific columns over SELECT * in production code. Explicit columns make your queries self-documenting and prevent breakage when table schemas change. Using aliases (the AS keyword) makes calculated columns and joined results more readable.

WHERE

The WHERE clause filters rows before they are returned. Combining filters with logical operators AND and OR lets you build precise conditions.

-- Comparison operators
SELECT * FROM users WHERE age > 18;
SELECT * FROM products WHERE price BETWEEN 10 AND 50;

-- Text matching
SELECT * FROM users WHERE name LIKE 'J%';     -- starts with J
SELECT * FROM users WHERE name LIKE '%son%';  -- contains 'son'

-- IN clause
SELECT * FROM users WHERE country IN ('US', 'CA', 'UK');

-- NULL checks
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

Beware that NULL comparisons require IS NULL or IS NOT NULL — using = NULL will never return results because NULL is not equal to anything, including another NULL. The LIKE operator supports two wildcards: % (any sequence of characters) and _ (a single character). For large text searches, consider full-text search features (like PostgreSQL’s tsvector) instead of LIKE for better performance.

ORDER BY

Sort results with ORDER BY. Combine multiple columns for secondary sorting.

SELECT * FROM products ORDER BY price ASC;     -- low to high
SELECT * FROM products ORDER BY price DESC;    -- high to low
SELECT * FROM users ORDER BY last_name, first_name;  -- multiple columns
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;  -- latest 10

Sorting is performed after filtering, so ORDER BY does not affect which rows are returned — only their presentation order. For large datasets, ensure the sorted column is indexed to avoid expensive sequential scans.

INSERT

Add new rows to a table. Modern SQL supports inserting multiple rows in a single statement for efficiency.

-- Single row
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

When inserting many rows at once, batching them in a single INSERT (typically 100–1000 rows at a time) is significantly faster than individual inserts. Most databases also support RETURNING (PostgreSQL) or OUTPUT (SQL Server) to get back auto-generated IDs or default values.

UPDATE

Modify existing rows. The WHERE clause is critical — omitting it updates every row in the table.

UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Always use WHERE — without it, all rows are updated!

A common pattern is to update rows based on a join:

UPDATE orders o
SET status = 'shipped'
FROM shipments s
WHERE o.id = s.order_id AND s.delivered = true;

Always run a SELECT with the same WHERE first to verify you are targeting the correct rows before executing an UPDATE in production.

DELETE

Remove rows from a table. Like UPDATE, the WHERE clause is essential unless you intend to clear the entire table.

DELETE FROM users WHERE id = 1;

-- Delete all rows (but keep the table)
DELETE FROM users;

-- Truncate (faster, removes all rows)
TRUNCATE TABLE users;

TRUNCATE is faster than DELETE without a WHERE because it does not scan individual rows and cannot be rolled back in most databases. Use it when you need to quickly reset a table. For selective removal, DELETE with a precise WHERE clause is the standard approach.

JOINs

JOINs combine rows from two or more tables based on a related column. Understanding JOIN types is the single most important SQL skill for working with normalized databases.

-- INNER JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Using aliases is standard practice

An INNER JOIN returns only rows where the join condition matches in both tables. If a user has no orders, they are excluded from the results. A LEFT JOIN keeps all rows from the left table, filling in NULLs on the right side when no match exists. This is essential for reports where you need to see users with and without orders.

Less common but equally useful are RIGHT JOIN (the mirror of LEFT JOIN) and FULL OUTER JOIN (returns all rows from both tables, with NULLs where no match exists). In practice, most developers use LEFT JOIN almost exclusively and restructure queries to keep the driving table on the left side.

Aggregate Functions

Aggregate functions summarize data across multiple rows. They are almost always paired with GROUP BY to define the grouping dimension.

SELECT
  COUNT(*) AS total_users,
  AVG(age) AS average_age,
  MAX(price) AS most_expensive,
  MIN(price) AS cheapest,
  SUM(quantity) AS total_items
FROM orders;

-- Group By
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING user_count > 10;

The HAVING clause filters grouped rows after aggregation — it is essentially a WHERE clause for GROUP BY results. WHERE filters individual rows before grouping; HAVING filters groups after. Understanding this distinction prevents a common source of SQL bugs.

Performance tip: aggregate functions scan all matching rows. Ensure your WHERE clause is as selective as possible to reduce the data the aggregation must process. For very large tables, consider materialized views or summary tables that precompute aggregates.

Subqueries

Subqueries are queries nested inside another query. They appear in WHERE, FROM, and SELECT clauses.

-- WHERE subquery
SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE active = true
);

-- FROM subquery
SELECT avg_order.total
FROM (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id) AS avg_order;

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

The EXISTS keyword is often more efficient than IN when the subquery can return many rows, because EXISTS stops scanning as soon as it finds a single match. Correlated subqueries (ones that reference the outer query) can be slow on large datasets; in many cases, a JOIN or a window function will perform better.

CREATE TABLE

Define a new table and its columns along with constraints.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INTEGER CHECK (age >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Constraints enforce data integrity at the database level. Always define NOT NULL, UNIQUE, CHECK, and foreign key constraints in the schema rather than relying solely on application-level validation. Database-enforced constraints are the last line of defense against corrupt data.

ALTER TABLE

Modify existing table structure without losing data.

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users RENAME COLUMN phone TO phone_number;
ALTER TABLE users RENAME TO customers;

Schema migrations are a routine part of application development. Use a migration tool (like Alembic for Python, Flyway for Java, or Prisma Migrate for Node.js) to version-control these changes. Running ALTER TABLE on large tables in production can lock the table for minutes or hours — always test migrations on a staging environment first.

Common Date Functions

Date and time manipulation varies across databases. Here are the most useful patterns:

-- PostgreSQL
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days';
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) FROM orders GROUP BY month;

-- MySQL
SELECT * FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

Always store timestamps in UTC and convert to local time in the application layer. This avoids ambiguity with daylight saving time and makes your application work correctly across time zones.

Advanced Concepts to Learn Next

Once you have mastered the commands above, explore these topics to level up your SQL skills:

• Window functions — ROW_NUMBER(), RANK(), LAG(), and LEAD() let you perform calculations across related rows without collapsing them into groups.
• Common Table Expressions (CTEs) — WITH clauses make complex queries more readable by breaking them into named subqueries.
• Indexing strategies — Understanding B-tree, hash, and GIN indexes will dramatically improve query performance.
• Transactions — BEGIN, COMMIT, and ROLLBACK ensure atomic, consistent operations.
• Query execution plans — Using EXPLAIN ANALYZE to understand how the database executes your queries is the best way to find performance bottlenecks.

FAQ

What is the difference between SQL and NoSQL? SQL databases use structured schemas with tables, relationships, and ACID transactions. NoSQL databases offer flexible schemas, horizontal scaling, and various data models (document, key-value, graph, column-family). Choose based on data structure and access patterns.

When should I use an index? Index queries filtering on the indexed column(s), particularly for WHERE clauses, JOINs, and ORDER BY. Avoid over-indexing — each index slows writes and consumes storage. Monitor slow queries to identify indexing opportunities.

What is database normalization? Normalization organizes data to reduce redundancy and improve integrity. First Normal Form (1NF) eliminates duplicate columns. Second Normal Form (2NF) removes partial dependencies. Third Normal Form (3NF) removes transitive dependencies.

How do I choose between PostgreSQL and MySQL? PostgreSQL offers more advanced features (JSONB, full-text search, custom types, better concurrency). MySQL is simpler to configure and widely available. Both are excellent choices; PostgreSQL is often preferred for complex applications.

What is a deadlock in databases? A deadlock occurs when two transactions each hold locks the other needs. The database detects deadlocks and kills one transaction automatically. Reduce deadlocks by accessing tables in the same order across transactions and keeping transactions short.

Related: See the full SQL JOINs explained guide and PostgreSQL vs MySQL comparison.