6 Common Mistakes for SQL Queries that "Should be Working"

We've all seen the dreaded error... SQL: Invalid syntax near ")". In this article, I cover some of common culprits I've seen for this error and others.

5 months ago   •   4 min read

By Shanika Wickramasinghe
Table of contents

Have you ever found yourself pulling out your hair trying to debug a SQL query that “should be working”?

These errors can be difficult to debug because they often involve a specific nuance in how a SQL command works. In this article, I’ll cover 7 of the most common mistakes that I’ve experienced and seen when writing SQL queries, and how to fix them.

If you're looking for a modern SQL editor to run your SQL queries, try Arctype today for free.

Arctype visualization example

1. Using NOT IN with NULL values

Let's create two example tables to track the color of different products:

Colors table:

color_id color_value
c001 Red
c002 Yellow
c003 Green
c004 Black
c005 White

Product table:

product_id product_name color_id
1000 smartwatch c001
1001 ballpoint pen c003
1002 wireless keyboard c004
1003 charging pad NULL

How would you write a query to retrieve every color that is not used by a current product?

At first glance, NOT IN seems to be a great solution, but let's see what happens:

SELECT col.color_value
FROM Colors AS col
WHERE col.color_id 
NOT IN 
(SELECT prod.color_id FROM Products AS prod);

The query ends up returning an empty set instead of yellow and white like we would expect.

This happens because SQL uses three-value logic, which means logical expressions can be true, false, or unknown.

In the example above, color_id NOT IN (c001, c003, c004, NULL) can be simplified to: NOT (color_id = c001 OR color_id = c003 OR color_id = c004 OR color_id = NULL).

In order for this to be true, every comparison inside the parentheses would have to be false, but color_id = NULL always returns unknown. Therefore, this expression is never evaluated to TRUE, and an empty set is returned.

We can fix this by using EXISTS instead of IN. EXISTS uses two-valued logic so there is no unknown value:

SELECT col.colour_value
FROM Colours AS col
WHERE NOT EXISTS
(SELECT prod.product_colour
 FROM Products AS prod
 WHERE col.color_id = prod.color_id); 

2. Incorrect order when writing queries

This is one of the more popular errors I see, especially among SQL beginners.

SQL has a predefined keyword order to properly execute queries. Can you spot the error in this SQL query?

SELECT empName 
FROM employees 
WHERE empCategory='DevOps' 
ORDER BY empName 
GROUP BY branchCode 
HAVING count(*) = 1;

To fix the query, we just have to move the order by command to the end:

SELECT empName 
FROM employees 
WHERE empCategory='DevOps' 
GROUP BY branchCode 
HAVING count(*) = 1
ORDER BY empName;

This is often one of the most common mistakes after missing quotes, brackets, commas, and semicolons, but it's also one of the easiest to catch and fix.

3. Using UNION when UNION ALL should have been used

Mixing up UNION and UNION ALL is common enough that I've seen it come up in several SQL interviews. UNION ALL and UNION both concatenate results from multiple SQL queries, but UNION eliminates duplicate results when concatenating results.

Let's say we had a table of all basketball players at a high school, basketball_players, and also a separate table for students in band, band_students. Which command would be use to find every student who played basketball OR was in band?

UNION would be the right choice here, because it would remove duplicates for students who played basketball and were in band.

4. Using BETWEEN for exclusive ranges

The BETWEEN operator is helpful for selecting data within a specified range.

What dates would this return?

SELECT *
FROM products
WHERE manuf_date BETWEEN ‘2020-04-01’  AND ‘2020-05-01’

If you said every day in April + the first day of May, you would be correct!

To select data truly between two dates, the most clear method is explicitly declaring the range using equality comparisons:

SELECT *
FROM products
WHERE manuf_date => ‘2020-04-01’
AND manuf_date < ‘2020-05-01’

5. Implicit field definition type conversion at runtime

Do you see anything wrong with this query?

SELECT *
FROM myAccount acc
WHERE acc.pin = 123654789286
AND acc.isPending IS null;

It depends on the data type of pin. If pin is of type varchar, this comparison would force a conversion for every value in the pin column. This would not give a runtime error, but it could severely impact the query performance.

SQL does an implicit conversion when comparing two values of different data types. The conversion of these values adds additional overhead and forces a full table scan.

To remove the implicit conversion, we can change the comparison to have matching data types: acc.pin = '123654789286'

6. Misusing loops in SQL / Cursors

This is another situation that won't throw an explicit error, but can have a negative impact on query performance.

Loops are one of the go to tools in a developer's tool belt, so it's fair to want to use this same technique when writing SQL queries.

Let’s see an example of using a cursor to update the prices of items put on sale:

DECLARE @prodID INT
DECLARE @prodPrice INT
 
DECLARE updPrices 
 
CURSOR LOCAL STATIC READ_ONLY
 
FOR SELECT prodID, prodPrice
FROM onSalePrices
 
OPEN updPrices
 
FETCH NEXT FROM updPrices
INTO @prodID, @prodPrice
WHILE @@FETCH_STATUS = 0
 
BEGIN
 
UPDATE products
SET price = @prodPrice
WHERE id = @prodID
 
FETCH NEXT FROM updPrices INTO @prodID, @prodPrice
END
CLOSE updPrices
DEALLOCATE updPrices

This solution works, but it is not very efficient and should generally be avoided. The reason for that is SQL is designed as a ‘set-based’ architecture. By following that set-based approach instead of cursors, you could accomplish the same task with a simpler and more efficient query.

Conclusion

Making mistakes is the first step to learning something new. After 5 years of software development, I still find myself scratching my head on certain SQL errors.

Having a good grasp of common "gotchas" with SQL will make it easier to avoid such possible issues. If you want a SQL editor that makes working with databases even easier, try out Arctype today:

The Collaborative SQL Editor

Spread the word

Keep reading