SQL Errors: Five Common SQL Mistakes (2024)

As you learn SQL, watch out for these common coding mistakes

You’ve written some SQL code and you’re ready to query your database. You input the code and …. no data is returned. Instead, you get an error message.

Don’t despair! Coding errors are common in any programming language, and SQL is no exception. In this article, we’ll discuss five common mistakes people make when writing SQL.

The best way to prevent mistakes in SQL is practice. LearnSQL.com offers over 30 interactive SQL courses. Try out our SQL Practice track with 5 courses and over 600 hands-on exercises.

Watch Your Language (and Syntax)

The most common SQL error is a syntax error. What does syntax mean? Basically, it means a set arrangement of words and commands. If you use improper syntax, the database does not know what you’re trying to tell it.

To understand how syntax works, we can think of a spoken language. Imagine saying to a person “Nice dof” when you mean “Nice dog”. The person does not know what “dof” means. So when you tell your database to find a TABEL instead of a TABLE, the database does not know what it needs to do.

People tend to make the same kinds of syntax mistakes, so their errors are usually easy to spot and very much the same. After you read this article, you should be able to remember and avoid (or fix) these common mistakes. Knowing what errors to look for is very important for novice SQL coders, especially early on. New coders tend to make more mistakes and spend more time looking for them.

The types of SQL errors we will look at are:

Misspelling Commands
Forgetting Brackets and Quotes
Specifying an Invalid Statement Order
Omitting Table Aliases
Using Case-Sensitive Names

Ready? Let’s start.

SQL Errors:

1. Misspelling Commands

This is the most common type of SQL mistake among rookie and experienced developers alike. Let’s see what it looks like. Examine the simple SELECT statement below and see if you can spot a problem:

SELECT *FORM dishWHERE NAME = 'Prawn Salad';

If you run this query, you’ll get an error which states:

Solution:

Use an SQL editor that has syntax highlighting: the SELECT and WHERE keywords will be highlighted, but the misspelled FORM will not get highlighted.

If you’re learning with interactive SQL courses in LearnSQL.com, the code editor puts every SELECT statement keyword in light purple. If the keyword is black, as it is with any other argument, you know there’s a problem. (In our example, FORM is black).

So if we correct our statement we get:

SELECT *FROM dishWHERE NAME = 'Prawn Salad'

The keyword is now the right color and the statement executes without an error.

2. Forgetting Brackets and Quotes

Brackets group operations together and guide the execution order. In SQL (and in all of the programming languages I use), the following order of operations …

SELECT *FROM artistWHERE first_name = 'Vincent' and last_name = 'Monet' or last_name = 'Da Vinci';

… is not the same as:

SELECT *FROM artistWHERE first_name = 'Vincent' and (last_name = 'Monet' or last_name = 'Da Vinci');

Can you figure out why?

A very common SQL mistake is to forget the closing bracket. So if we look at this erroneous statement :

SELECT *FROM artistWHERE first_name = 'Vincent' and (last_name = 'Monet' or last_name = 'Da Vinci';

We get an error code with the position of the error (the 102nd character from the beginning):

Solution:

Practice, practice, practice. Writing more SQL code will give you the experience you need to avoid these mistakes. And remember people usually forget the closing bracket or quotation mark. They rarely leave out the opening one. If you’re running into problems, take a close look at all your closing punctuation!

3. Invalid statement order

When writing SELECT statements, keep in mind that there is a predefined keyword order needed for the statement to execute properly. There is no leeway here.

Let’s look at an example of a correctly-ordered statement:

SELECT nameFROM dishWHERE name = 'Prawn Salad'GROUP BY nameHAVING count(*) = 1ORDER BY name;

There’s no shortcut here; you simply have to remember the correct keyword order for the SELECT statement:

SELECT identifies column names and functions
FROM specifies table name or names (and JOIN conditions if you’re using multiple tables)
WHERE defines filtering statements
GROUP BY shows how to group columns
HAVING filters the grouped values
ORDER BY sets the order in which the results will be displayed

You cannot write a WHERE keyword before a FROM, and you can’t put a HAVING before a GROUP BY. The statement would be invalid.

Let’s look at what happens when you mix up the statement order. In this instance, we’ll use the common SQL error of placing ORDER BY before GROUP BY:

SELECT nameFROM dishWHERE name = 'Prawn Salad'ORDER BY nameGROUP BY nameHAVING count(*) = 1

The error message we see is pretty intimidating!

Syntax error in SQL statement "SELECT name FROM dish WHERE name = 'Prawn Salad' ORDER BY name GROUP[*] BY name HAVING count(*) = 1;"; SQL statement: SELECT name FROM dish WHERE name = 'Prawn Salad' ORDER BY name GROUP BY name HAVING count(*) = 1; [42000-176]

Solution:

Don’t be discouraged! You can see that all of the keywords are highlighted correctly and all the quotations and brackets are closed. So now you should check the statement order. When you’re just beginning your SQL studies, I suggest using a SELECT order checklist. If you run into a problem, refer to your list for the correct order.

4. Omitting Table Aliases

When joining tables, creating table aliases is a popular practice. These aliases distinguish among columns with the same name across tables; thus the database will know which column values to return. This is not mandatory when we’re joining different tables, since we can use the full table names. But it is mandatory if we join a table to itself.

Suppose we’re writing an SQL statement to find an exhibition’s current location and the location from the previous year:

SELECT *FROM exhibit JOIN exhibit ON (id = previous_id);

The database would return an error:

Ambiguous column name "id"; SQL statement: SELECT * FROM exhibit JOIN exhibit ON (id = previous_id); [90059-176]

Note: Whenever you encounter “ambiguous column name” in your error message, you surely need table aliases.

The correct statement (with aliases) would be:

SELECT ex.* , exp.nameFROM exhibit JOIN exhibit ON (ex.id = exp.previous_id);

Solution:

Practice using table aliases for single-table SELECT statements. Use aliases often – they make your SQL more readable.

5. Using Case-Sensitive Names

This error only occurs when you need to write non-standard names for tables or database objects.

Let’s say that you need to have a table named LargeClient and for some reason you add another table called LARGECLIENT. As you already know, object names in databases are usually case-insensitive. So when you write a query for the LargeClient table, the database will actually query LARGECLIENT.

To avoid this, you must put double quotes around the table name. For example:

SELECT * FROM"LargeClient" WHERE cust_name = 'Mijona';

When creating a table, you will need to use double quotes if:

The table will have a case-sensitive name.
The table name will contain special characters. This includes using a blank space, like “Large Client”.

Solution:

Avoid using these names if you can. If not, remember your double quotes!

Everybody Makes SQL Mistakes

Those are the five most common errors in SQL code. You’ll probably make them many times as you learn this language. Remember, everybody makes mistakes writing code. In fact, making mistakes is a normal and predictable part of software development.

So don’t be discouraged. When you make mistakes in the future, try to analyze your code in a structured way. With a structured analysis, you can find and correct your errors quicker.

If you would like to learn about some other syntactic mistakes that I’ve not included here, please let me know. In an upcoming article, we’ll look at non-syntactic errors. These return or modify data and are therefore much more dangerous. Subscribe to our blog so you won’t miss it!