Five Common SQL Syntax Errors

SQL is a powerful language for managing databases, but even small syntax errors can lead to big headaches. As you work with SQL, understanding and avoiding common mistakes can save you time and frustration. In this article, we'll highlight five frequent SQL syntax errors

You've written your SQL code and you're ready to query your database. You input the code, but instead of getting the data you expected, you get an error message. It's frustrating, but don't worry—this is a normal part of learning any programming language.

Mistakes happen in SQL just like they do in any other language. Whether you're new to SQL or have been using it for a while, errors are inevitable. The good news is that each mistake is an opportunity to learn and improve your skills. By understanding these common errors, you can save yourself a lot of time and frustration.

In this post, we'll go over five common SQL syntax errors that people often make. We'll explain why these mistakes happen and how you can avoid them. By the end, you'll have a better understanding of how to write cleaner, error-free SQL code.

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 mistakes 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 SQL syntax errors. 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 syntax errors in SQL we will look at are:

1. Misspelling Commands
2. Forgetting Brackets and Quotes
3. Specifying an Invalid Statement Order
4. Omitting Table Aliases
5. Using Case-Sensitive Names

Ready? Let’s start.

Syntax Error 1: Misspelling Commands

This is the most common SQL syntax error 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 dish
WHERE NAME = 'Prawn Salad';

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

Syntax error in SQL statement "SELECT * FORM[*] dish WHERE NAME = 'Prawn Salad';"; 
SQL statement: SELECT * FORM dish WHERE NAME = 'Prawn Salad'; [42000-176]

Each database version will tell you the exact word or phrase it doesn’t understand, although the error message may be slightly different.

What is wrong here? You misspelled FROM as FORM. Misspellings are commonly found in keywords (like SELECT, FROM, and WHERE), or in table and column names.

Most common SQL spelling errors are due to:

• “Chubby fingers” where you hit a letter near the right one: SELEVT or FTOM or WJIRE
• “Reckless typing” where you type the right letters in the wrong order: SELETC or FORM or WHEER

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 SQL syntax error. (In our example, FORM is black).

So if we correct our statement we get:

SELECT *
FROM dish
WHERE NAME = 'Prawn Salad'

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

Syntax 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 artist
WHERE first_name = 'Vincent' and last_name = 'Monet' or last_name = 'Da Vinci';

… is not the same as:

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

Can you figure out why?

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

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

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

ERROR: syntax error at or near ";" Position: 102

Remember: brackets always come in pairs.

The same is true with single quotes ( ‘ ‘ ) or double quotes ( ” ” ). There is no situation in SQL where we would find a quote (either a single quote or a double quote) without its mate. Column text values can contain one quote ( e.g. exp.last_name = "O'Reilly") and in these situations we must mix two types of quotes or use escape characters. ( In SQL, using escape characters simply means placing another quote near the character you want to deactivate – e.g. exp.last_name = 'O'’Reilly.)

Solution:

Practice, practice, practice. Writing more SQL code will give you the experience you need to avoid these syntax errors. 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!

Syntax Error 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 name
FROM dish
WHERE name = 'Prawn Salad'
GROUP BY name
HAVING count(*) = 1
ORDER 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 syntax error of placing ORDER BY before GROUP BY:

SELECT name
FROM dish
WHERE name = 'Prawn Salad'
ORDER BY name
GROUP BY name
HAVING 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.

Syntax Error 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.name
FROM 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.

Syntax Error 5: Using Case-Sensitive Names

This SQL syntax 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 syntax error, 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 Syntax Errors

Those are the five most common syntax 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 syntax errors quicker.

To help avoid these and other mistakes, check out the Ultimate SQL Cheat Sheet. If you want to dive deeper into SQL syntax, our guide on SQL Syntax is a great resource.