Coding Tips for SQL: Write Queries Like a Pro

Most people think writing SQL is just typing SELECT * and hoping for the best. But if you’ve ever waited 30 seconds for a query to finish-only to realize it scanned 2 million rows-you know SQL isn’t magic. It’s a language that rewards precision. The difference between a junior dev and a pro isn’t how many joins they know. It’s how they think about data.

Start with the smallest result possible

Every SQL query starts with a question. What do you actually need? Too often, people grab everything and filter later. That’s like ordering a whole pizza just to eat one slice.

Instead, ask: What’s the minimum number of columns and rows I need right now?

Example: You need the names and emails of customers who bought a product in the last 7 days. Don’t write:

SELECT * FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.created_at > '2025-10-23'

Write this:

SELECT c.name, c.email FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.created_at > '2025-10-23'

Removing * cuts down memory use, network traffic, and query time. Even if you think you’ll need more fields later, get the answer first. Then expand if needed.

Use indexes like a tool, not a crutch

Indexes speed up lookups. But adding one to every column? That’s like putting locks on every drawer in your house. It slows everything down.

Focus on columns used in WHERE, JOIN, and ORDER BY clauses. If you’re filtering by status = 'active' and sorting by created_at, create a composite index:

CREATE INDEX idx_status_created ON orders (status, created_at);

PostgreSQL and MySQL will use this for both filtering and sorting. Don’t just index the first column in your query-think about the whole pattern.

Pro tip: Use EXPLAIN ANALYZE before and after adding an index. You’ll see exactly how many rows it skips and how much time it saves. I’ve seen queries drop from 4.2 seconds to 0.12 seconds with one smart index.

Never use SELECT * in production

This isn’t just a style thing. It’s a performance killer-and a security risk.

Imagine your app pulls user data with SELECT * from a table that has 15 columns, including password hashes, API keys, and internal IDs. Now imagine a bug accidentally exposes that data to a public endpoint. That’s not hypothetical. It’s happened to companies you’ve heard of.

Even if you’re not storing secrets, SELECT * makes your queries brittle. Add a new column? Suddenly your app breaks because it expects 10 fields and gets 11. Explicit columns are self-documenting. They’re safer. They’re faster.

Write joins like a conversation, not a maze

Joins are powerful. But chaining 5 tables together with no structure? That’s unreadable-and slow.

Always join in a logical order. Start with the smallest table that filters the most. Then add larger tables one at a time.

Example: You need orders from active customers in Sydney who bought a premium plan.

Bad:

SELECT o.id, c.name, p.name FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN cities ci ON c.city_id = ci.id
JOIN plans p ON o.plan_id = p.id
JOIN regions r ON ci.region_id = r.id
WHERE r.name = 'Australia' AND ci.name = 'Sydney' AND c.status = 'active' AND p.type = 'premium'

Good:

SELECT o.id, c.name, p.name FROM plans p
JOIN orders o ON o.plan_id = p.id
JOIN customers c ON o.customer_id = c.id
JOIN cities ci ON c.city_id = ci.id
WHERE p.type = 'premium'
  AND c.status = 'active'
  AND ci.name = 'Sydney'

Why? You start with plans because only 3% of records are premium. That shrinks the dataset fast. Then you join to orders (which links to plans), then customers, then cities. Each step reduces the number of rows. The database doesn’t have to scan millions of inactive users.

Use CTEs to break down complexity

Complex queries are easier to read when you split them into parts. Common Table Expressions (CTEs) let you do that cleanly.

Instead of nesting subqueries inside subqueries, write:

WITH recent_orders AS (
  SELECT customer_id, MAX(order_date) as last_order
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
),
high_value_customers AS (
  SELECT customer_id, SUM(amount) as total_spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 1000
)
SELECT c.name, ro.last_order, hvc.total_spent
FROM customers c
JOIN recent_orders ro ON c.id = ro.customer_id
JOIN high_value_customers hvc ON c.id = hvc.customer_id
WHERE c.status = 'active';

Each part has a clear purpose. You can test them individually. You can reuse them later. And anyone reading it doesn’t need to hold 5 levels of nesting in their head.

Prefer EXISTS over IN for subqueries

Here’s a common mistake:

SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'shipped');

If the subquery returns 10,000 IDs, the database has to load them all into memory, then check each customer against that list. That’s slow.

Use EXISTS instead:

SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'shipped'
);

EXISTS stops as soon as it finds one match. It doesn’t care how many orders exist-just if at least one does. It’s faster. It’s clearer. And it handles NULLs better.

Don’t use functions on indexed columns in WHERE clauses

This looks innocent:

SELECT * FROM users WHERE DATE(created_at) = '2025-10-25';

But wrapping created_at in DATE() makes the database ignore the index on that column. It has to scan every row, convert the timestamp, then compare.

Instead, use a range:

SELECT * FROM users 
WHERE created_at >= '2025-10-25 00:00:00' 
  AND created_at < '2025-10-26 00:00:00';

Now the index works. The query runs 10x faster. This trick saves time in every production system with more than 10,000 records.

Test with real data, not sample data

Writing SQL on a table with 100 rows? You’ll never see the real bottlenecks.

Copy production data into a staging environment-even if it’s just 10% of the size. Run your queries there. Watch the execution plan. Measure the time.

I once optimized a query that ran in 0.02 seconds on a test database. In production? 18 seconds. Why? The test data had no duplicates. Production had 3 million. The query was doing a full table scan because the index wasn’t selective enough.

Always test with data that looks like the real thing. Otherwise, you’re just guessing.

Comment your logic, not your syntax

Don’t write:

-- select name and email from customers
SELECT name, email FROM customers WHERE active = true;

That’s useless. Anyone can read SQL.

Write:

-- Get active customers who haven't logged in for 90+ days (churn risk)
-- Used in weekly retention report
SELECT name, email FROM customers 
WHERE active = true 
  AND last_login < NOW() - INTERVAL '90 days';

Now someone reading this next month knows why it exists. They know what business problem it solves. That’s the difference between code that works and code that lasts.

Learn to read execution plans

PostgreSQL: Use EXPLAIN (ANALYZE, BUFFERS)

MySQL: Use EXPLAIN FORMAT=JSON

Look for these red flags:

• Seq Scan on a large table-means no index or index isn’t used
• Hash Join with millions of rows-could be better with a nested loop + index
• Sort with high cost-check if you can avoid it with an index
• Filter after a join-means the join didn’t filter enough

Don’t just run the query. Run it with EXPLAIN. Then fix what you see. This habit separates pros from everyone else.

Write SQL like you’re teaching someone

SQL isn’t just for computers. It’s for your future self. And your teammates.

Proper formatting matters. Use line breaks. Align keywords. Group related conditions. Be consistent.

Bad:

select c.name,o.total from customers c,orders o where c.id=o.customer_id and o.status='paid' and c.country='AU'

Good:

SELECT 
  c.name, 
  o.total 
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND c.country = 'AU';

It takes 10 extra seconds to format it right. But it saves hours for the person who has to fix it later.

SQL is a language of precision. The more you treat it like one, the more power you get from it.