7 SQL Best Practices Every Developer Should Know

SQL’s not hard to write; it’s hard to write well.

Queries that work fine in development start to drag in production. A missing ORDER BY returns inconsistent results, a quick SELECT * breaks when schemas change, and filters that look harmless on a small dataset can grind to a halt at scale.

Over time you start to see patterns that hold up and patterns that don’t.

So here are seven SQL best practices, brought to you by the team behind Beekeeper Studio, the modern, open source, cross-platform database GUI.

Download Beekeeper Studio

1. Be specific — avoid SELECT *

Fetching every column with SELECT * can feel quick and flexible. You don’t have to decide which columns to return and which to exclude and it works fine when you’re still exploring a dataset.

That flexibility comes at a cost. As schemas evolve or joins multiply, SELECT * can pull in duplicate or unexpected columns, slow your queries, and make debugging harder.

Choosing specific columns keeps intent clear and shields your code from silent breakage.

-- Risky
SELECT * FROM user_profiles WHERE user_id = 42;

-- Better
SELECT user_id, display_name, avatar_url
FROM user_profiles
WHERE user_id = 42;

2. Use CTEs to simplify complex logic

Nested subqueries work, but they quickly turn unreadable. It’s hard to see what’s being filtered where, and debugging usually means scrolling back and forth through layers of parentheses.

Common Table Expressions (WITH clauses) let you break a query into named steps. Each step can be run and tested in isolation, making intent clearer and errors easier to trace. Just remember that some databases materialize CTEs, so inline subqueries may be faster in tight loops.

-- Hard to follow
SELECT customer_id, COUNT(*)
FROM (
  SELECT customer_id, created_at
  FROM orders
  WHERE created_at > now() - interval '7 days'
) o
GROUP BY customer_id;

-- Clearer
WITH recent_orders AS (
  SELECT customer_id, created_at
  FROM orders
  WHERE created_at > now() - interval '7 days'
)
SELECT customer_id, COUNT(*)
FROM recent_orders
GROUP BY customer_id;

3. Comment your intent, not your syntax

Inline comments that restate what the SQL already shows don’t add much value. The real context you’ll forget later isn’t what the query does but why it exists.

A short note about purpose or usage makes it easier to revisit or hand off the code later. Write for your future self or the next developer trying to understand why this query matters.

-- Find users who signed up but never activated
-- Used in the weekly retention cohort report
SELECT u.id, u.email
FROM users u
LEFT JOIN events e
  ON u.id = e.user_id
  AND e.name = 'account_activated'
WHERE e.id IS NULL
  AND u.created_at > now() - interval '7 days';

4. Make your filters index-friendly

Indexes only help when your filters are sargable (short for Search ARGument ABLE). In practice, that means the database can use the index directly instead of scanning every row.

If you wrap a column in a function — for example LOWER(email) or DATE(created_at) — the optimizer can’t use the index. Even type casts can have the same effect. The query still works, but it gets slower as your data grows.

-- Not sargable: disables index
WHERE LOWER(email) = 'test@example.com';

-- Sargable: uses existing index
WHERE email = 'test@example.com';

To keep filters efficient, write them to match how data is stored. If you need case-insensitive matching, store values in a consistent format or add a functional index. For dates, filter by range instead of transforming the column — it’s clearer and keeps the index in play.

-- Not sargable
WHERE DATE(created_at) = '2025-08-04';

-- Sargable
WHERE created_at >= '2025-08-04'
  AND created_at <  '2025-08-05';

5. Filter before you join

It’s tempting to join everything first and filter later, especially when exploring or prototyping. But every join multiplies rows and every unnecessary row makes your query slower and harder for the optimizer to plan.

Instead, narrow each table before you combine them. That way, the database only joins the data that actually matters — reducing memory use, improving cache efficiency, and keeping the execution plan predictable.

-- Joins every user, even inactive ones
SELECT e.*
FROM users u
JOIN events e ON e.user_id = u.id;

-- Filter first: only active, recently seen users
WITH active_users AS (
  SELECT id
  FROM users
  WHERE active = true
    AND last_seen > now() - interval '90 days'
)
SELECT e.*
FROM active_users u
JOIN events e ON e.user_id = u.id;

6. Prefer EXISTS over IN for relationship checks

When you’re checking whether related rows exist, IN will get the job done — until it doesn’t. It builds a full list of values, compares each one, and treats NULLs in a way that can hide valid matches.

EXISTS works differently. It returns true the moment it finds a match and isn’t affected by NULLs in the subquery. The result is both faster and more reliable, especially on large datasets.

-- Works, but can be slower and NULL-prone
SELECT *
FROM users
WHERE id IN (SELECT user_id FROM orders);

-- Clear and efficient: stops at first match
SELECT *
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

7. Always specify ORDER BY

SQL doesn’t guarantee row order unless you ask for it.

Without an ORDER BY, the database is free to return rows in whatever sequence is most convenient and that can change between runs, environments, or even minor updates.

It might look stable in development, but once indexes, execution plans, or data volume shift, you’ll start seeing unpredictable pagination or inconsistent API results.

-- Looks fine until it isn't
SELECT id, name
FROM users
WHERE active = true
LIMIT 10;

-- Predictable and reproducible
SELECT id, name
FROM users
WHERE active = true
ORDER BY created_at DESC
LIMIT 10;

Bringing it all together

The best queries are the ones you understand a year later. They have clear intent, predictable behavior, and no hidden surprises.

That’s the spirit behind these habits. They’re practices we use every day in Beekeeper Studio to keep queries readable, consistent, and fast across every database we work with.

You can grab a free PDF version of this guide to keep handy or share with your team:

Download 7 SQL Habits for Busy Developers

With Beekeeper Studio you can put these habits into everyday practice, using a modern, cross-platform open-source SQL editor built to keep you in flow.

• 20+ databases supported: Postgres, MySQL, SQLite, SQL Server, MongoDB, Redis, and more.
• Fast, responsive editor: Schema-aware autocomplete, tabbed queries, and syntax highlighting.
• Visual data tools: Browse tables, edit rows, and inspect JSON without breaking context.
• AI assistant (privacy-first): The missing AI coding tool for your database — schema-aware, works with your choice of LLM, and helps you explore and refine queries faster.

Download Beekeeper Studio