SQL Queries Every Developer Should Know

Introduction to SQL
If you are a web developer, chances are you have worked with a database at some point. It might be PostgreSQL, MySQL, SQLite, or something else. No matter the stack, SQL is always there behind the scenes.And let’s be honest, few things feel better than writing a single SQL query that returns exactly the data you need. Extra points if it runs fast.In this article, we’ll walk through essential SQL queries every developer should know. Whether you are building APIs, dashboards, or debugging a production issue at 2 AM, these queries are your daily tools.

1. SELECT & The Foundation If SQL were a movie, SELECT would be the origin story. Every meaningful query starts here.

Use SELECT to fetch only the data you actually need. While SELECT * looks convenient, it can slow down queries and waste bandwidth, especially on large tables.

The real power of SELECT comes when you combine it with WHERE, JOIN, and ORDER BY.

SELECT id, name, email
FROM users;

1. WHERE Filtering the Noise Databases can store millions of rows. WHERE helps you narrow things down to what actually matters.

You can filter using:

Comparison operators: =, !=, <, >, <=, >=
Logical operators: AND, OR, NOT
Without WHERE, things can get dangerous. A query like DELETE FROM users; without a condition will delete everything. Always double-check.

SELECT *
FROM orders
WHERE status = 'completed';

1. INSERT Adding New Data Whenever a user signs up, places an order, or uploads something, you’ll need INSERT.

Best practices:

Always specify column names
Use bulk inserts when possible for better performance
Never trust user input. Use parameterized queries or an ORM to prevent SQL injection
INSERT INTO users (name, email)
VALUES ('Alice', '[e-mail]');

1. UPDATE Changing Existing Data Emails change, addresses change, and bugs happen. That’s where UPDATE comes in.

The most common and most dangerous mistake is forgetting the WHERE clause. Without it, every row gets updated.

Tips:

You can update multiple columns at once
Use transactions in production to stay safe
UPDATE users
SET email = '[e-mail]'
WHERE id = 42;

1. DELETE Removing Data When it’s time to clean up old or unused data, DELETE does the job.

Use cases:

Removing expired sessions
Cleaning test data
Deleting old logs
Many production apps use soft deletes by adding a deleted_at column instead of permanently removing records.

DELETE FROM sessions
WHERE last_active < NOW() - INTERVAL '30 days';

1. JOIN Combining Tables Real applications don’t live in a single table. JOIN lets you stitch data together.

Become a member
Common types:

INNER JOIN: Only matching rows
LEFT JOIN: All rows from the left table, even if there is no match
RIGHT JOIN: Opposite of left join
FULL JOIN: Everything from both tables
If your results suddenly double, chances are your join condition is wrong.

SELECT users.name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;

1. GROUP BY and Aggregate Functions Perfect for analytics and reports.

Common aggregates:

COUNT() – number of rows
SUM() – total
AVG() – average
MAX() / MIN() – highest or lowest values
Whenever you use aggregates with non-aggregated columns, GROUP BY is required.

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status;

1. ORDER BY and LIMIT Sometimes you only need the latest or top results.

Use cases:

Latest blog posts
Top users by score
Recent transactions
This combo is also great for pagination when used with OFFSET.

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;

1. LIKE and Pattern Matching For basic search functionality, LIKE is simple and effective.

Patterns:

% matches any number of characters
_ matches a single character
Note: Case sensitivity depends on the database. PostgreSQL uses ILIKE for case-insensitive searches.

SELECT *
FROM users
WHERE email LIKE '[e-mail]';

1. Subqueries Sometimes one query needs the result of another. That’s a subquery.

They are great for readability, but joins are often faster. Use subqueries when clarity matters more than raw performance.

SELECT name, email
FROM users
WHERE id IN (
SELECT user_id
FROM orders
WHERE amount > 100
);
Final Thoughts: Mastering SQL
This article is part of the “SQL Queries Every Developer Should Know” series.

Mastering these queries will help you:

Debug faster by querying the database directly
Build better and faster APIs
Write cleaner backend logic
Impress teammates during code reviews
SQL is not scary. It’s a superpower. And like any skill, the more you practice it, the more confident and unstoppable you become.