Write Smarter SQL: 5 Tips to Level Up Your Queries

SQL (Structured Query Language) is the foundation of interacting with relational databases. Whether you’re building a side project or working in a production environment, writing efficient and readable SQL can save you hours of debugging and optimization.

In this post, I’ll share 5 practical tips to help you write smarter SQL—whether you’re using PostgreSQL, MySQL, or SQLite.

1. Be Specific with SELECT
Avoid using SELECT * unless you're in a quick-and-dirty debugging session. Selecting all columns is not only inefficient (especially with joins), but it also makes your queries harder to maintain.

Bad:

SELECT * FROM users;

Better:

SELECT id, name, email FROM users;
This reduces memory usage and improves performance—especially over large datasets.

2.Use Table Aliases for Clarity
When working with joins, use meaningful aliases to make your queries more readable.

Bad:

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

Cleaner:

SELECT o.id AS order_id, c.name AS customer_name
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id;

Good aliases make your queries easier to scan, especially in team environments or long queries.

3. Index Strategically
Indexes speed up lookups, but they come with write-time costs. So, use them thoughtfully.

Example:

CREATE INDEX idx_users_email ON users(email);
This helps if you're frequently filtering by email:

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

4. Keep It DRY with Common Table Expressions (CTEs)
CTEs are your best friend for simplifying complex queries.

Instead of nesting subqueries:

SELECT name FROM (
SELECT name, COUNT(*) as count FROM users GROUP BY name
) AS user_counts
WHERE count > 1;

Use a CTE:

WITH user_counts AS (
SELECT name, COUNT(*) as count FROM users GROUP BY name
)
SELECT name FROM user_counts WHERE count > 1;
It’s easier to read, easier to test, and easier to reuse.

5. Use CASE for Conditional Logic
SQL has its own version of if/else: the CASE statement.

SELECT name,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age >= 18 AND age < 65 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM users;

This makes your logic explicit without post-processing in application code.

N/B:
SQL is simple on the surface, but powerful underneath. The more intentional you are with your queries, the better performance and maintainability you’ll get.