Errors Contain Useful Information
Errors in SQL are not just problems; they give us clues about what went wrong. Error messages tell you the type of mistake, where it happened, and sometimes how to fix it. Understanding these messages will help you become a better SQL user.
Level 1: Basic Errors (“Syntax Errors”)
1. Spelling Mistakes
Just like in regular writing, spelling matters in SQL. If you spell a command incorrectly, your query won’t work.
Example of an error: Incorrect spelling of SELECT
Correct version:
SELECT * FROM users;
2. Misplaced or Incorrect Punctuation
Punctuation marks, such as commas and parentheses, play an important role in SQL syntax. Misplacing them or forgetting to include necessary symbols can cause errors. Similarly, using incorrect quotation marks or missing essential keywords like FROM or WHERE can lead to issues.
Incorrect: -- Missing closing parenthesis
In the example above, the error message may indicate an issue near FROM, but the actual problem is the missing closing parenthesis before it. When debugging, don't just check the exact spot mentioned in the error—look at the surrounding syntax as well.
Correct:
SELECT (cost + retail_price) * 1.2 FROM products;
3. Mismatched Names
The names you use in your query must exactly match the names in your database. If you spell a table or column name incorrectly, your query won’t work.
Incorrect: Column name is actually 'order_item_id'
Correct:
SELECT order_item_id FROM order_items WHERE returned_at BETWEEN '2023-06-01' AND '2023-06-30';
Level 2: Logical Errors (“Query Structure Mistakes”)
Even if your query is spelled correctly, logical mistakes can still cause errors.
1. Missing `` in Aggregations
If you use functions like SUM(), COUNT(), or AVG(), any non-aggregated column must be in the GROUP BY clause.
Incorrect:
Correct:
SELECT order_id, count(order_item_id) FROM order_items GROUP BY order_id
2. Dividing by Zero
A division by zero will cause an error. Always check for zero before dividing.
Correct:
SELECT price / NULLIF(discount, 0) FROM products;