05 Nov Common mistakes made when writing SQL queries
It is very easy to make mistakes when writing SQL queries, I’ve gotten frustrated a few times myself.
To avoid encountering some of the same issues that I have faced, here is a list of a few things you should avoid:
1. Misspelling SQL Commands
When it comes to spelling commands, SQL isn’t very forgiving. As an example, If you misspell SELECT as SELCT, the database won’t know what you’re talking about.
Luckily, most SQL editors color code queries, which is a helpful tip to note. The word SELECT will be written in blue. If it isn’t, it’s most likely due to a spelling error.
To avoid this common SQL mistake, pay attention to your typing. Get in the habit of capitalizing keywords such as SELECT and FROM. It makes your query easier to read, and separates the keywords, such as table and column names. Review your commands to make sure they’re color-coded and that you don’t see any red underlines.
2. Selecting the wrong database
When you connect to a server with multiple databases and open an editor to query a specific database, it is very easy to connect to the wrong one. This will result in multiple errors as the editor will not recognize the tables and columns you’re trying to query. Remember to always confirm that you’re connected to the right database.
3. Missing Bracket and Single Quotes
SQL commonly employs brackets (), single quotes (‘ ‘), and double quotes (” “). They come in pairs – an opening bracket must be followed by a closing bracket, the same goes for quotes that are used to enclose text values.
SELECT * FROM Store
WHERE product_name = ‘sugar AND product_id in (‘1003’, ‘1343
The query above will return an error because the quotes and the bracket were not closed.
The SQL editor has to know where your text starts and ends, so ensure you close a quote and bracket
This is an example of a correct query
SELECT * FROM Store
WHERE product_name = ‘sugar’ AND product_id in (‘1003’, ‘1343’)
4. Invalid Statement order
With SQL, you have to write your commands in a specified order. You cannot put the WHERE statement before FROM statement. Your query will definitely return errors. It is important to stick to the predefined order to avoid errors.
This is the order for most SQL statements
SELECT: helps you identify column names and functions
FROM: helps you specify table name or names (and JOIN conditions if you’re using multiple tables)
WHERE: defines filtering statements
GROUP BY: shows you how to group columns
HAVING: filters the grouped values
ORDER BY: sets the order in which the results will be displayed
5. Commas and Semi-Colons
In SQL, commas and semi-colons are also used. Semi-colons are used to denote the end of a statement, while commas are used to divide lists, such as columns being picked or sorted. It is best practice to use a semi-colon at the end of your sentence, even if it’s not necessary. It’s easy to overlook a comma, especially when editing sentences. Rather than placing the comma at the end of a column, try putting it on the front. That way, when you remove a column, especially the last one, it won’t cause an issue.
It is important to note that mistakes are extremely common and we all make them no matter how experienced we get. It is important to keep practicing and you’ll see that you’ll experience fewer mistakes.
Do you have any interesting mistakes you’ve made when writing SQL queries? Share them with me in the comment section or on Twitter @thesqlbabe.
Written by: Simileoluwa Afolabi-Jombo