How to Write Better SQL Queries: 10 Practical Tips

The difference between a slow query and a fast one often isn't complexity — it's discipline. Here are 10 practical tips that will make your SQL queries faster, more readable, and easier to maintain.

1. Never Use SELECT *

SELECT * pulls every column, even ones you don't need. This wastes network bandwidth, memory, and CPU.

❌ Bad:

SELECT * FROM users;

✅ Good:

SELECT user_id, name, email FROM users;

Bonus: Specifying columns makes your query self-documenting. Future readers know exactly what data you need.

2. Use Meaningful Table Aliases

Single-letter aliases (u, o) are cryptic. Use abbreviations that make sense.

❌ Bad:

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

✅ Good:

SELECT usr.id, usr.name, ord.total
FROM users usr
JOIN orders ord ON usr.id = ord.user_id;

3. Put WHERE Conditions on Indexed Columns

Filtering on indexed columns is orders of magnitude faster. If you filter on a non-indexed column, the database scans every row.

❌ Slow (if status isn't indexed):

SELECT * FROM orders WHERE UPPER(status) = 'COMPLETED';

✅ Fast (if status is indexed):

SELECT * FROM orders WHERE status = 'completed';

Wrapping a column in a function (like UPPER()) prevents the database from using the index.

4. Use JOINs Instead of Subqueries

JOINs are almost always faster than correlated subqueries. The database optimizer handles JOINs better.

❌ Slow (correlated subquery):

SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

✅ Fast (JOIN):

SELECT usr.name, COUNT(ord.id) AS order_count
FROM users usr
LEFT JOIN orders ord ON usr.id = ord.user_id
GROUP BY usr.id, usr.name;

5. Avoid Functions on WHERE Columns

Functions prevent index usage. If you need to transform data, do it in the application layer or use computed columns.

❌ Bad (can't use index on created_at):

SELECT * FROM orders WHERE YEAR(created_at) = 2026;

✅ Good (uses index):

SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

6. Use LIMIT When Exploring Data

Always add LIMIT when you're testing a query. Prevents accidentally pulling millions of rows.

✅ Good:

SELECT * FROM large_table WHERE status = 'pending' LIMIT 100;

7. Use Explicit JOINs (Not Comma Joins)

Comma joins are outdated and harder to read. Use explicit INNER JOIN,LEFT JOIN, etc.

❌ Old style (comma join):

SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id;

✅ Modern style (explicit JOIN):

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

8. Use CTEs for Readability

Common Table Expressions (CTEs) make complex queries readable. Break them into logical steps.

✅ Good (readable):

WITH recent_orders AS (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  GROUP BY user_id
)
SELECT u.name, ro.total_spent
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id
WHERE ro.total_spent > 1000;

9. Handle NULLs Explicitly

NULL comparisons behave unexpectedly. Use IS NULL or COALESCE()explicitly.

❌ Wrong (returns no rows):

SELECT * FROM users WHERE email = NULL;

✅ Right:

SELECT * FROM users WHERE email IS NULL;

10. Use EXPLAIN to Understand Query Performance

Before optimizing, understand what the database is actually doing. Use EXPLAINto see the execution plan.

✅ Good:

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

Look for: sequential scans (slow), index scans (fast), and join types. This tells you where to add indexes or restructure your query.

The Bottom Line

Better SQL isn't about knowing obscure syntax. It's about discipline: specify columns, use indexes, avoid functions on WHERE clauses, and think about performance from the start.

Apply these 10 tips and your queries will be faster, more readable, and easier to maintain. Your database (and your teammates) will thank you.