SQL Query Examples: Complete Cheat Sheet for Developers

SQL Query Examples: Complete Cheat Sheet for Developers

SQL (Structured Query Language) is the universal language of databases. Whether you are building web apps, performing data analysis, or working on backend microservices, SQL is an absolutely essential skill.

This cheat sheet provides the most common and useful SQL query examples that you will use in real-world projects.

What is SQL? (Quick Answer)

SQL is a standardized language for managing and querying relational databases. It lets you create, read, update, and delete data stored in tables using simple English-like commands. SQL is supported by every major database engine — including PostgreSQL, MySQL, SQLite, and SQL Server — making it one of the most transferable skills in software development.

The Core SQL Operations (CRUD)

SQL databases store data in tables (similar to spreadsheets with rows and columns). The core operations you perform on these tables are known as CRUD:

1. Create (INSERT)
2. Read (SELECT)
3. Update (UPDATE)
4. Delete (DELETE)

1. SELECT: Retrieving Data

The SELECT statement is the most frequent command you will use. It is how you ask the database a question.

2. WHERE: Filtering Data

Without a WHERE clause, you are querying every single row in the table, which is extremely slow. WHERE lets you filter the data based on conditions.

3. JOIN: Combining Tables

In a well-designed relational database, data is split across multiple tables. JOINs act as the glue to bring that data back together.

4. GROUP BY & Aggregates

Sometimes you don't need individual rows; you need a summary. Aggregate functions (COUNT, SUM, AVG) calculate totals, and GROUP BY organizes them into categories.

5. Modifying Data (INSERT, UPDATE, DELETE)

These commands physically alter the data stored in the database.

6. Subqueries

A subquery is a query nested inside another query. They let you use the result of one query as a condition or data source for another.

7. Window Functions

Window functions are one of the most powerful — and underused — features in SQL. They perform calculations across a set of rows related to the current row, without collapsing them into a single aggregate result.

Window functions are supported in PostgreSQL, MySQL 8+, and SQL Server. They are referenced in the official PostgreSQL documentation.

8. Common Table Expressions (CTEs)

A CTE (using the WITH keyword) makes complex queries more readable by breaking them into named, reusable building blocks.

CTEs are far more readable than deeply nested subqueries. They also allow recursive queries (useful for tree structures like organizational hierarchies).

SQL Performance Tips

Writing correct SQL is one thing — writing fast SQL is another. Here are the most impactful optimizations:

• Index frequently queried columns. Columns used in WHERE, JOIN ON, and ORDER BY benefit enormously from an index.
• Avoid SELECT * in production. Only fetch the columns you actually need.
• Use EXPLAIN or EXPLAIN ANALYZE. Before running a heavy query on production data, check the query execution plan.
• Paginate with LIMIT and OFFSET rather than loading all rows into memory.
• Avoid functions on indexed columns in WHERE. WHERE YEAR(created_at) = 2026 prevents index use — prefer a date range instead.

For further reading on how SQL integrates with Go-based backends, see our Go database guide with GORM. Also check out Use The Index, Luke — the most practical free reference on SQL indexing strategies.

For how SQL relates to broader data handling in Python data analysis, the pandas CSV guide is a great companion.

Conclusion

Mastering SQL is a superpower. While Object-Relational Mappers (ORMs) like Prisma or TypeORM are great, understanding the underlying SQL queries they generate is what separates junior developers from senior engineers.

Check out the W3Schools SQL reference for a quick keyword lookup. And don't miss our REST API tutorial — most REST APIs are backed by exactly the SQL patterns you've learned here.

Bookmark this cheat sheet and refer to it the next time you need to write a complex database query!

Common SQL Query Mistakes

1. Using SELECT * in production queries SELECT * retrieves every column in a table, including large text and binary columns you may not need. This increases network transfer, memory usage, and makes queries brittle to schema changes. Always list specific columns: SELECT id, name, created_at FROM users. This also documents which fields the query depends on.

2. Missing WHERE clause on UPDATE and DELETE UPDATE users SET active = false without a WHERE clause updates every row. DELETE FROM orders without WHERE deletes every order. Always test UPDATE and DELETE statements as SELECT queries first to verify the affected rows, then convert to the modifying statement. Run in a transaction so you can roll back if the result is unexpected.

3. N+1 query problem Fetching a list of records and then querying for related records one at a time produces N+1 queries (1 to get the list, N to get each related item). Use JOIN or IN clauses to fetch related data in a single query: SELECT orders.*, users.name FROM orders JOIN users ON orders.user_id = users.id.

4. Not using indexes on columns in WHERE, JOIN, and ORDER BY clauses A query filtering on an unindexed column performs a full table scan — every row is read. On large tables this is catastrophically slow. Use EXPLAIN (PostgreSQL) or EXPLAIN ANALYZE to see whether queries are using indexes. Add indexes on frequently filtered or joined columns with CREATE INDEX.

5. Treating NULL incorrectly in comparisons WHERE column = NULL never matches any rows because NULL is not equal to anything, including itself. Use WHERE column IS NULL or WHERE column IS NOT NULL. Similarly, NULL in arithmetic always produces NULL: 5 + NULL = NULL. Use COALESCE(column, 0) to substitute a default value.

Frequently Asked Questions

What is the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN? INNER JOIN returns only rows where there is a match in both tables. LEFT JOIN returns all rows from the left table, with NULL values for columns from the right table when no match exists. RIGHT JOIN is the mirror of LEFT JOIN. FULL OUTER JOIN returns all rows from both tables, with NULLs where no match exists on either side. The PostgreSQL JOIN documentation illustrates each with examples.

What is the difference between WHERE and HAVING in SQL? WHERE filters rows before grouping — it applies to individual rows. HAVING filters after grouping — it applies to aggregate results. SELECT department, COUNT(*) FROM employees WHERE active = true GROUP BY department HAVING COUNT(*) > 5 first filters to active employees, then groups by department, then filters to departments with more than 5 active employees. You cannot use aggregate functions in a WHERE clause — that is what HAVING is for.

When should I use a subquery vs a JOIN? JOINs are generally faster because the query planner can optimise them with indexes more effectively than correlated subqueries. Use subqueries when the logic is clearer (filtering with WHERE id IN (SELECT ...)) or when you need a derived table (FROM (SELECT ...) AS sub). Modern query planners often rewrite subqueries as joins automatically, but explicit JOINs are more predictable in their performance characteristics. The PostgreSQL query planner documentation explains how optimisation decisions are made.