7 Deadly Mistakes You Make on Your SQL Queries and How to Fix them

Welcome back to another insightful journey into the world of SQL.

In this article, we'll explore common yet overlooked mistakes that can hinder your querying skills. Each mistake comes with an example and a fix to ensure you not only understand the issue but also know how to rectify it.

So, let's dive deep into the nuances of SQL querying. In this article, you will learn how to avoid these 7 common pitfalls and optimize your SQL queries for better performance.

In order to follow all the steps of this tutorial, let’s take a hypothetical database of 2 tables of products and orders.

Now that we have the tables, in the following sections you will see those SQL Common mistakes and how to fix them.

1. Counting NULL columns

When you need to count null column values, you should know how COUNT() works. Let's suppose you want to count the number of products, even when the key 'name' of the table 'product' is missing.

SELECT COUNT(name) FROM products;

# Result: 4

You might have been expecting 5 because you wanted to count the null value of column ‘name’ as well. But COUNT() doesn't count null values.

So to fix it, you should use COUNT(*), it counts the null values as well.

SELECT COUNT(*) FROM products;

# Result: 5

It seems simple, but many people forget it when writing queries.

2. Using reserved words as column names

Avoid using reserved words like RANK() as column names, especially when they coincide with SQL functions. If you encounter this issue, encapsulate the column name with backticks or quotes:

-- wrong
SELECT product_id, RANK() OVER (ORDER BY price DESC) AS Rank FROM products;


-- valid
SELECT product_id, RANK() OVER (ORDER BY price DESC) AS `Rank` FROM products;

3. Using comparison operations with NULL

-- wrong
SELECT name FROM products WHERE product_id = NULL;

This will throw an exception since you used the comparison operator '=' with NULL, and so will the comparison operator '!='. Comparing values with NULL using operators like '=' or '!=' is invalid in SQL.

Instead, use the IS NULL or IS NOT NULL syntax:

-- valid
SELECT name FROM products WHERE product_id IS NULL;

4. Filtering with the 'ON' vs the 'WHERE' clause

Understanding the difference between filtering in the 'ON' clause and the 'WHERE' clause during joins is crucial. This demonstration using a RIGHT JOIN illustrates the impact on the result set.

SELECT p.product_id, p.name, p.price, o.customer_name FROM products p
RIGHT JOIN orders o ON p.product_id = o.product_id
WHERE p.product_id > 1;

This query will return the following result

When you use the WHERE clause, the filter is applied after the tables are joined. So, the result has no rows where p.product_id > 1.

On the other hand, if you run the same query with the AND clause,

SELECT p.product_id, p.name, p.price, o.customer_name FROM products p
RIGHT JOIN orders o ON p.product_id = o.product_id AND p.product_id > 1;

You will get the following result set

The conditional statement 'AND' is examined before the join takes place. You could consider it to be a 'where' clause that only applies to one of the tables (the 'product' table). The result now has even rows where d.product_id > 1 because of the RIGHT JOIN.

5. Using columns created through Windows functions

Avoid using columns created through Windows functions or 'CASE WHEN' in the 'WHERE' clause of the same query. The following request will throw an exception.

-- wrong
SELECT name, price, RANK() OVER (ORDER BY price DESC) AS rk FROM products
WHERE rk = 2;

To fix this, you should use a Temporary table or a Subquery.

-- valid
WITH Ranking AS (
    SELECT product_id, name, price, RANK() OVER (ORDER BY price DESC) AS rk
    FROM products
)
SELECT price
FROM Ranking
WHERE rk = 2;

-- valid
SELECT price, rk
FROM (
    SELECT product_id, name, price, RANK() OVER (ORDER BY price DESC) AS rk
    FROM products
) as Subquery
WHERE Subquery.rk = 2;

The same idea applies to a column created using the ‘CASE WHEN’ clause.

6. Using BETWEEN incorrectly

Many people think that if you have the BETWEEN keyword, then when you build the query, you automatically have to set the two dates to the upper intervals to get the right date range.

Understanding the inclusivity of the range covered by 'BETWEEN' is crucial to avoid unexpected results. The statement ‘BETWEEN X AND Y’ includes both x and y in the required range.

You may have been expecting all the order dates between 2021-10-16 and 2021-10-21. AS BETWEEN is inclusive, you should have the following query.

SELECT * FROM orders WHERE date BETWEEN '2021-10-16' AND '2021-10-21';

7. Using the 'WHERE' clause after 'GROUP BY'

Be careful where you write WHERE when using GROUP BY.

The 'WHERE' clause should precede 'GROUP BY':

-- wrong
SELECT  p.category, AVG(p.price) FROM products p
INNER JOIN orders o ON p.product_id = o.product_id
GROUP BY p.category
WHERE o.customer_name LIKE '%a%';

This is wrong because you've used the "WHERE" clause after "GROUP BY" And, why is that?

‘WHERE’ clause is used to filter on the results, and you should logically do that before you group that, not after. Applying the ‘WHERE’ filter first would reduce the data and then ‘GROUP BY’ will group it according to the aggregation function used (AVG here).

-- valid
SELECT  p.category, AVG(p.price) FROM products p
INNER JOIN orders o ON p.product_id = o.product_id
WHERE o.customer_name LIKE '%a%'
GROUP BY p.category;

To round off this article, I just want you to pay attention to the execution order of the main SQL statements:

(1) FROM clause

(2) WHERE clause

(3) GROUP BY clause

(4) HAVING clause

(5) SELECT clause

(6) ORDER BY clause

Conclusion

This article covered some common SQL querying mistakes with practical fixes. Remember, mistakes are opportunities to learn. Keep refining your querying skills, and for more insights, have a look at my other articles on this blog.

If you liked this article, don’t forget to share it, see you on the next one :)