Five Common SQL Mistakes to Avoid: A Beginner’s Guide

By /
FIve Common Sql Errors

For new SQL developers, especially at the beginning, knowing what faults to look for is crucial. When you write a query, an error message appears instead of the expected output. Don’t Worry it’s completely normal! Any computer language, including SQL, is prone to coding errors. New programmers frequently make more errors and spend a long time hunting for them.

There are a variety of ways to run SQL queries, but it’s crucial to understand and use the best approach possible to avoid errors and having to wait for hours for your code to run. In this article, we’ll talk about five frequent mistakes people make in SQL. You just have to take care of these common mistakes and start practising writing queries more to avoid future errors.

Five Common SQL Mistakes

Let’s look at five different types of SQL errors, which we have to take care of and avoid to have a clean-run code!

1. Typing/Syntax Errors

This is the SQL error that both inexperienced and seasoned developers make the most frequently. A syntax error is the most typical SQL mistake. Syntax basically refers to a predetermined order of words and instructions. The database cannot understand what you are attempting to tell it if you use incorrect syntax. It’s important to take care of the syntax while you write the code. Let’s have a look at it.

Identify any issues with the straightforward SELECT statement below by carefully examining it:

SELECT *
FORM employee
WHERE NAME = 'Peter';Code language: SQL (Structured Query Language) (sql)

Running this query will result in an error message that reads something like this:

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

What’s the problem here? Here FROM is misspelt as FORM. Most commonly misspelt words include “SELECT,” “FROM,” and “WHERE,” as well as table and column names. What’s the solution for this? You can use a SQL editor having syntax highlighting that highlights the correct keywords and not the misspelt ones.

2. Unnecessarily using SELECT *

Select * is an expensive operation that lengthens the query’s execution time by printing every column in a data table. Selecting only the necessary columns from your output table or subquery is the best course of action.

Consider in the below example that rather than picking all the columns with SELECT *, we should just select the ProductID column if we want to retrieve the product id from the Products table.

#Bad practice
SELECT * FROM Products

# Optimal method 
SELECT ProductID FROM ProductsCode language: SQL (Structured Query Language) (sql)

So it is important to use the SELECT * statement in the required queries only!

3. Missing Brackets or Quotes

Operation grouping and execution sequence are governed by brackets. Forgetting to close the bracket is a relatively common SQL error. So let’s examine the following false statement:

SELECT *
FROM employee
WHERE first_name = 'Peter' and (last_name = 'Quill' or last_name = 'Ross';Code language: SQL (Structured Query Language) (sql)

Here we will get an error reading something as:

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

The same holds true for single (‘ ‘) and double (‘ “); quotations. In SQL, it is impossible to come across a quotation (either a single or double quote) without its mate.

So what’s the solution for this? You will get the knowledge necessary to avoid these errors by writing more SQL code. Additionally, keep in mind that most individuals overlook the closing quotation mark or bracket. Rarely do they omit the first one. Examine all of your concluding punctuation carefully if you’re having issues.

4. Incorrect Statement Order

Remember that there is a predefined keyword order that must be followed when crafting SELECT statements so that the statement will work as intended. There is no room for error here.

The correct keyword order for the SELECT statement is as follows:

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

Let’s see an illustration of a statement that is properly arranged:

SELECT name
FROM employee
WHERE name = 'Peter Quill'
GROUP BY name
HAVING count(*) = 1
ORDER BY name;Code language: SQL (Structured Query Language) (sql)

WHERE and ORDER BY cannot be used before FROM and HAVING, respectively. It would be invalid. Let’s see what happens when you mess with the order:

SELECT name
FROM employee
WHERE name = 'Peter Quill'
ORDER BY name
GROUP BY name
HAVING count(*) = 1Code language: SQL (Structured Query Language) (sql)

Obviously, on running this query you will get an error message which can be quite frightening! So what’s the solution for this? Just go through the keyword order for the SELECT statement when you run into errors and keep practising!

5. Avoid using WHERE for Joining

Another common technical mistake is using the WHERE clause for joining tables. Some individuals carry out the inner join using the where clause rather than the INNER JOIN clause.

Although both syntaxes perform similarly (thanks to query optimisation), joining with the “WHERE” clause is not advised for the following two reasons:

  • Readability and comprehension are impacted when the “where” clause is used for filtering as well as joining.
  • The ‘where’ clause can only be used for the inner join, which severely restricts its application in joining.

Incorrect way

SELECT  Products.ProductID, Prodcuts.CustomerID, Customers.CustomerId
FROM Products, Customers
WHERE Products.CustomerID = Customers.CustomerIDCode language: SQL (Structured Query Language) (sql)

Correct way

SELECT Products.ProductID, Products.CustomerID, Customers.CustomerId
FROM Products
INNER JOIN Customers
ON Products.CustomerID = Customers.CustomerID;Code language: SQL (Structured Query Language) (sql)

Conclusion

These are the five most common mistakes in SQL. As you master this language, you’ll probably make a lot. Always keep in mind that programming errors happen to everyone. Making errors is, in fact, a common and expected aspect of the software development process. Therefore, try not to give up. Try to arrange your code analysis when you make mistakes in the future. You can locate and correct your faults more quickly with a methodical analysis.

You can also check out: Write Efficient SQL Queries: 8 Tips for Absolute Beginners – MySQLCode

Reference

https://stackoverflow.com/questions/26320413/sql-query-errors-and-mistakes