They run, they return data. So what? Some SQL queries just waste your time and resources. Here’s what to stop writing – and the much smarter alternatives.

Sure, your SQL query might run. It might even spit out some data. But is it actually doing anything worthwhile? Plenty of SQL out there looks perfectly fine, seems harmless, yet it’s utterly pointless. These queries chew up resources, obscure valuable insights, or simply clutter your workflow.

I’m going to show you real-world examples of these SQL duds. For each one, I’ll break down precisely what’s gone wrong and then lay out a much sharper way to get the information you actually need.

If you’re itching to put this into practice immediately, I found the SQL Practice track incredibly helpful; it’s packed with interactive exercises, over a thousand of them across a dozen courses, really helping to bridge that gap between just knowing the theory and actually building solid skills.

So, let’s ditch the digital deadwood and start crafting SQL that pulls its weight.

1. The ‘Give Me Everything’ Gamble (SELECT *)

The Culprit:

SELECT *
FROM orders;

Why It’s a Time Waster: This command yanks every single piece of data from the orders table, much of which you probably don’t even need. This habit drags down query speed and puts an unnecessary load on your database. Think of it like asking for the entire library when you only need one specific book.

The Smarter Play: Be specific. Tell the database exactly which columns you need. Your queries will thank you with better performance.

The Fix:

SELECT order_id, customer_id, total_amount
FROM orders;

2. The Unfiltered COUNT(*): A Number Full of Noise

The Culprit:

SELECT COUNT(*)
FROM orders;

Why It’s Misleading: Sure, you get a total count. But a count of what, exactly? Without any filters, this number often tells you very little of actual value and can easily point you in the wrong direction. It’s like counting all the animals in a zoo to understand just the lion population.

The Smarter Play: Get specific with your counts. Use WHERE conditions or GROUP BY to make sure you’re counting what truly matters.

The Fix:

SELECT COUNT(*)
FROM orders
WHERE status = 'completed';

3. The Pointless ORDER BY: Sorting for No Reason

The Culprit:

SELECT total_amount
FROM orders
ORDER BY customer_id;

Why It’s a Drag: Sorting data takes effort, and if that order serves no purpose for your current analysis, you’re just adding unnecessary processing time. Why make the database sort if you don’t care about the sequence?

The Smarter Play: Use ORDER BY with clear intent. It’s crucial for reports or when preparing data for visualization, but if the order is irrelevant, skip it.

The Fix (when order truly doesn’t matter):

SELECT total_amount
FROM orders;

4. The Endless Sort: ORDER BY Without LIMIT

This blunder echoes the last one, but with a slightly different flavor of inefficiency.

The Culprit:

SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY created_at DESC;

Why It’s a Resource Hog: Asking the database to sort an entire, potentially massive, dataset without telling it when to stop is a recipe for slow performance. It meticulously arranges everything, even if you only glance at the first few results.

The Smarter Play: If you’re sorting, especially large tables, always ask yourself if you need all the sorted data. Often, you’re just interested in the top or bottom entries. That’s where LIMIT (or TOP in some SQL dialects) becomes your best friend.

The Fix:

SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY created_at DESC
LIMIT 10;

5. The Cryptic GROUP BY: Who Are These IDs Anyway?

The Culprit:

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;

Why It’s Unhelpful: You get a list of customer IDs and their order counts. Great. But who is customer 103? Or 437? Without more context, like a name or email, these results are difficult to understand and nearly impossible to act upon. It’s data, sure, but not information.

The Smarter Play: Always pair those IDs with meaningful labels. Join with other tables (like a customers table) to pull in names, emails, or any other identifier that makes the data instantly usable.

The Fix:

SELECT o.customer_id, c.email, COUNT(*) AS total_orders
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY o.customer_id, c.email;

6. The HAVING Misstep: Filtering Rows Like Groups

The Culprit:

SELECT order_id, customer_id, total_amount
FROM orders
HAVING total_amount > 100;

Why It’s Confusing (and Inefficient): Yes, this query might run and give you what looks like the right result. However, HAVING is designed to filter groups after an aggregation (like COUNT, SUM, AVG) has been performed. Using it to filter individual rows, a job meant for WHERE, is not just unconventional; it’s misleading for anyone reading your SQL and often less performant. The database might process more data than necessary before filtering.

The Smarter Play: Stick to the SQL script. Use WHERE to filter rows before any grouping happens. Keep HAVING for its intended purpose: filtering based on aggregate function results.

The Fix:

SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 100;

7. The Index Nullifier: Functions on Filtered Columns

The Culprit:

SELECT order_id, customer_id, total_amount
FROM orders
WHERE YEAR(created_at) = 2023;

Why It Cripples Performance: When you apply a function (like YEAR()) to a column in your WHERE clause, you often prevent the database from using any indexes that exist on that created_at column. Without the index, the database has to perform a full table scan, reading every single row to check the condition. This can be incredibly slow on large tables.

The Smarter Play: Rewrite your condition to allow index usage. For date/time columns, this typically means using range-based comparisons on the raw, unadulterated column.

The Fix:

SELECT order_id, customer_id, total_amount
FROM orders
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

8. The Subquery Slowdown: When a JOIN Is Just Better

The Culprit:

SELECT order_id, customer_id, total_amount
FROM orders
WHERE customer_id IN (SELECT customer_id FROM blacklisted_customers);

Why It Can Be a Bottleneck: While subqueries using IN have their place and can be perfectly fine (especially for short lists of values or when well-optimized by the database), they can often be less performant than a good old JOIN. This is particularly true with larger datasets or more complex subquery logic. The database optimizer might not handle the subquery as efficiently as a direct join.

The Smarter Play: In many cases, especially when you’re checking for existence in another table based on a common key, rewriting the subquery as an INNER JOIN (or LEFT JOIN if your logic requires checking for non-matches too) will give the database a clearer path to optimize and often results in faster execution.

The Fix:

SELECT o.order_id, o.customer_id, o.total_amount
FROM orders o
JOIN blacklisted_customers b ON o.customer_id = b.customer_id;

9. The Convoluted Self-Join: Window Functions to the Rescue!

The Culprit: (Imagine you’re trying to number orders for each customer chronologically)

SELECT
    o1.customer_id,
    o1.order_id,
    COUNT(*) AS row_num
FROM orders o1
JOIN orders o2
    ON o1.customer_id = o2.customer_id
    AND o1.order_date >= o2.order_date -- Assuming 'order_date' column exists
GROUP BY o1.customer_id, o1.order_id;

Why It’s Overkill and Error-Prone: Trying to achieve tasks like ranking or row numbering within groups using self-joins is a classic SQL brain-teaser that often leads to overly complex, hard-to-read, and surprisingly inefficient queries. You’re essentially forcing the database to compare many rows with many other rows within their respective groups – that’s a lot of work and a lot of room for subtle errors in your join conditions or grouping.

The Smarter Play: Embrace window functions! SQL introduced these powerful tools like ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), and LEAD() for precisely these kinds of analytical tasks. They are more concise, generally much faster, and far easier to understand once you grasp the OVER() clause.

The Fix:

SELECT
    customer_id,
    order_id,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num
FROM orders;

Craft SQL That Works Smarter, Not Just Harder

Listen, getting a query to run without errors is just the first step; it doesn’t automatically mean it’s a good query. Sidestepping these common blunders I’ve outlined will seriously upgrade your SQL game, making your code quicker, more readable, and genuinely impactful.

If you’re keen on really honing these skills and practicing the right way to approach query writing, I can’t recommend the SQL Practice track enough. It’s loaded with practical, hands-on exercises that helped me a ton in actually applying these concepts, way beyond just reading about them. You learn by doing, and that makes all the difference.

The post SQL Blunders: Stop Writing These Pointless Queries & Write Smarter! appeared first on RealSQLGuy.