If you’re a web developer, you’ve probably touched a database at some point - whether it’s PostgreSQL, MySQL, or SQLite. And let’s be real: nothing makes you feel more powerful than writing a single SQL query that fetches exactly the data you need (bonus points if it runs fast 🏎️).
In this post, we’ll walk through 10 SQL queries every web dev should know. Whether you’re building APIs, dashboards, or debugging production issues at 2AM, these queries are your bread and butter.
1. – The Foundation
If SQL were a superhero movie, SELECT would be the origin story. You use it to fetch data from a table.
SELECT id, name, email
FROM users;
- Pick only what you need → Fetching every column with SELECT * feels easy, but it can slow down queries and waste bandwidth. Be intentional about which fields you need.
- The real power is in combination → SELECT by itself is simple, but combined with WHERE, JOIN, and ORDER BY, it becomes the backbone of pretty much every query you’ll ever write.
2. – Filtering the Noise
Databases can hold millions of rows. WHERE is how you zoom in on what matters.
SELECT *
FROM orders
WHERE status = 'completed';
- Comparison operators → Use =, <, >, != to find exactly what you want. Example: find all users registered after a certain date.
- Logical operators → Combine conditions with AND, OR, and NOT. Example: get users who signed up this year and verified their email.
- Without WHERE you risk chaos → A query like DELETE FROM users; without WHERE will clear your entire table. Don’t be that person.
3. – Adding New Data
When a user signs up, makes a purchase, or uploads a file, you’ll need INSERT.
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');
- Always specify columns → If you rely on defaults and someone later changes the schema, your query might break or insert the wrong data.
- Bulk inserts → You can insert multiple rows in one query, which is more efficient than doing it one row at a time.
- Security first → Always use parameterized queries or ORM methods to avoid SQL injection.
4. – Changing Existing Data
Users move, emails change, and bugs happen. That’s when you UPDATE.
UPDATE users
SET email = 'alice@newdomain.com'
WHERE id = 42;
- The infamous missing WHERE → Without it, you’ll update every row. If you’ve never accidentally updated your whole database, you haven’t lived (or panicked) as a dev.
- Update multiple fields → You can change more than one column at once (SET name = 'Alice B', email = 'alice@new.com').
- Use transactions for safety → In production, wrap updates in a transaction so you can roll back if something goes wrong.
5. – Removing Data
When it’s time to clean up, DELETE comes in.
DELETE FROM sessions
WHERE last_active < NOW() - INTERVAL '30 days';
- Good for clean-up jobs → Great for removing expired sessions, logs, or test data.
- Dangerous without a filter → Just like UPDATE, missing WHERE means you nuke the entire table.
- Soft deletes → Instead of permanently removing data, many apps add a deleted_at column. That way, you can “delete” records without losing them forever.
6. – Combining Tables Like a Pro
Your data is rarely in one table. JOIN is how you stitch it together.
SELECT users.name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;
- INNER JOIN → Returns only rows with matching records in both tables.
- LEFT JOIN → Returns all rows from the left table, plus matches from the right. Useful when you want users even if they have no orders.
- RIGHT JOIN → The opposite of left join, but less common.
- FULL JOIN → Returns everything from both sides.
- Tip: If you’ve ever wondered why your results doubled, you probably joined without proper conditions.
7. + Aggregates
Perfect for reports, dashboards, or anytime your boss asks for “stats.”
SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status;
- Aggregates summarize data → COUNT tells you how many rows, SUM totals up numbers, AVG gives you averages, and MAX/MIN find extremes.
- Always pair with GROUP BY → Otherwise, SQL won’t know how to aggregate across multiple rows.
- Great for analytics → Think: “How many active users per day?” or “Which product has the highest revenue?”
8. +
Sometimes you just need the latest or top N results.
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;
- Sort in any direction → ASC (default) is smallest → largest; DESC is largest → smallest.
- Great for dashboards → Show the 5 newest blog posts, or top 10 users by score.
- Pagination made easy → Combine ORDER BY with LIMIT and OFFSET to fetch results page by page.
9. & Pattern Matching
When search functionality is needed, LIKE is your friend.
SELECT *
FROM users
WHERE email LIKE '%@gmail.com';
- % is the wildcard → It matches any number of characters. %@gmail.com finds all Gmail users.
- _ matches one character → e.g. A_ice matches Alice or Arice.
- Case sensitivity varies → PostgreSQL treats LIKE as case-sensitive, but you can use ILIKE for case-insensitive searches.
- When to upgrade → For more advanced searches (like full text), you’ll want to look into tsvector or Elasticsearch.
10. Subqueries – Queries Inside Queries
Sometimes, you need a query to power another query. That’s a subquery.
SELECT name, email
FROM users
WHERE id IN (
SELECT user_id
FROM orders
WHERE amount > 100
);
- Useful for filtering → Example: find users who’ve made big purchases without writing a complex join.
- Can be used in SELECT too → Example: get the average order amount alongside each user.
- Performance note → Subqueries are convenient, but joins are usually faster. Use them when readability > raw performance.
That’s it - 10 SQL queries every web developer should have in their toolkit.
Master these, and you’ll:
- Debug faster (because you can peek into the DB without waiting on a teammate)
- Build better APIs (clean queries = faster endpoints)
- Impress your teammates during code reviews (anad maybe even your DBA 😉)
SQL isn’t scary - it’s your database superpower. And like any superpower, the more you practice, the more unstoppable you’ll feel. +
