30 SQL Queries Every Backend Developer Should Know - DEV Community

As a backend developer, I found myself repeatedly writing the same SQL queries for common operations like joins, aggregations, and pagination. To streamline my workflow, I created a concise cheat sheet with 30 essential SQL queries that every backend developer should know.

This collection covers the most frequently used patterns in backend development:

JOIN Queries

• INNER JOIN for matching records between tables
• LEFT JOIN for including all records from the left table
• RIGHT JOIN for including all records from the right table
• CROSS JOIN for Cartesian products
• Self-joins for hierarchical data

Aggregations

• COUNT for counting rows
• SUM for calculating totals
• AVG for computing averages
• MIN and MAX for finding extremes
• GROUP BY with HAVING for filtered aggregations

Filtering Techniques

• WHERE clauses with various operators
• IN and NOT IN for multiple values
• BETWEEN for range queries
• LIKE for pattern matching
• EXISTS and NOT EXISTS for subqueries

Pagination

• LIMIT and OFFSET for basic pagination
• ROW_NUMBER() for numbered results
• CTE-based pagination for complex scenarios
• Keyset pagination for better performance

Duplicate Detection

• Finding duplicate rows
• Identifying duplicate values in specific columns
• Removing duplicates with DISTINCT
• Using GROUP BY to find duplicates
• Window functions for duplicate analysis

I compiled these queries into a small PDF for quick reference, which you can download here: https://shrugumroad.gumroad.com/l/madesqleasy

Whether you're a junior developer building your SQL toolkit or a senior engineer looking for a quick reference, these 30 queries cover the essential patterns you'll encounter daily in backend development.

Feedback Welcome!

If you find this cheat sheet useful or have suggestions for additional queries to include, I'd love to hear your thoughts. The goal is to create a practical resource that helps developers write SQL more efficiently.