Back to articles list December 19, 2016 - 8 minutes read 8 Tips Absolute Beginners Can Use to Fix SQL Queries Marcos Pierri Tags: how to how to in sql Code errors are common – and frustrating. And when you're just learning SQL, it can be very challenging to find and fix your mistakes. In this post, we'll show you eight ways to solve or eliminate common SQL coding errors. Today, we'll talk about some tips the SQL beginner can use to avoid several common errors. These tips work in any database environment. As we go along, we'll be showing some error messages. The exact wording of your error messages may be a little different, but don't worry; each database engine has their own variants, but the meaning is the same. 6 Common Syntax Mistakes All SQL Learners Makehttps://t.co/owxdMv9UNT#sql #LearnSQL #Database — Vertabelo (@Vertabelo) January 3, 2017 That being said, we won't be focusing on error messages. Instead, we'll pinpoint the actual cause of the problem (such as forgetting a parenthesis or a quotation mark). This way, you'll learn how to bypass the issue in the first place. Ready? Let's begin! 1. Place Open and Close Parentheses First Remembering the closing character is key to eliminating unbalanced parentheses, quotes, double quotes, or square brackets. Best practices suggest typing both characters first (open and close) and then typing whatever goes inside. The following example shows an error when parentheses are not balanced: SELECT lastname, firstname FROM employee WHERE salary >= (select AVG(salary) from employee ; ERROR: syntax error at or near ";" LINE 2: WHERE salary >= (select AVG(salary) from employee ; Example 1: Unbalanced parentheses 2. Don't Put a Comma at the End of a Column or Table Sequence Commas act as a separator in SQL. There should not be any commas between FROM and the first table name or after the final table name. The same idea applies to column definition: when you create a table, be sure not to type an extra comma after the final column name. This is a really common error. SELECT * FROM employee, department, ERROR: syntax error at or near "," LINE 1: SELECT * FROM employee, department Example 2: An extra comma 3. Use Partial Query Evaluation to Debug Long SQL Queries Many SQL clients like Navicat or pgAdmin allow the partial execution of a query. You can do this by using your mouse to highlight part of the code. Using this divide-and-conquer technique, you can easily isolate and fix errors. Obviously, the marked part must be valid SQL. The upcoming query has two errors. If we execute the opening line of the query, we can see the first problem. (Hint: the "llastname" column doesn't exist.) SELECT llastname, firstname FROM employee WHERE salary >= (select MAXI(salary) from employee ; ERROR: column "llastname" does not exist LINE 1: SELECT llastname, firstname FROM employee Example 3: An incorrect column name However if we execute the complete SQL statement, we get an error related to unbalanced parentheses: SELECT llastname, firstname FROM employee WHERE salary >= (select MAXI(salary) from employee; ERROR: syntax error at or near ";" LINE 2: WHERE salary >= (select MAX(salary) from employee ; Example 4: Subquery with wrong parentheses We can also mark a subquery and execute it individually, as in the next example: SELECT llastname, firstname FROM employee WHERE salary >= (select MAXI(salary) from employee; ERROR: function maxi(numeric) does not exist LINE 1: select MAXI(salary) from employee Example 5: Incorrect function name 4. Pay Attention to Column and Table Names Pay very close attention when typing column names or table names. If possible, try to copy and paste the name from a command you know is correct – preferably one that's already executed properly. Best practices suggest copying and pasting names even if you think it's more time-consuming than typing. Having a misspelled column name or referring to a column in a table not in the FROM clause is very common indeed. Always look for typos in the column name, make sure the table in the FROM has this column, and make sure that the table is mentioned in FROM. SELECT llastname, firstname FROM employees ERROR: table "employees" does not exist LÍNEA 1: SELECT llastname, firstname FROM employees Example 6: Incorrect table name Another good practice is to use a table alias or a table name as a column prefix. This is doubly important when you have two or more tables in the <>FROM clause. The following error can appear if you refer to two identically-named columns in different tables: SELECT lastname, name FROM department, employee WHERE depto_id = depto_id ERROR: column reference "depto_id" is ambiguous LINE 3: WHERE depto_id = depto_id Example 7: Ambiguous column names SELECT lastname, name FROM department, employee WHERE department.depto_id = employee.depto_id Example 8: Prefixed column names To fix these errors, add the table name before the column name. (In the above example, that would be employee.depto_id and department.depto_id instead of just depto_id.) 5. Compare Compatible Data Types Only When you write comparison conditions in the WHERE clause, make sure both data types are compatible with the comparison operator and with each other. If this is not possible, you may have to cast one of the data types. The general rule is to compare numbers against numbers, character strings against character strings, etc. Some database systems automatically convert data types where possible; others provide enhanced data type conversions (i.e. a TIMESTAMP value can be automatically converted to a DATE before comparisons). Still other database services don't offer conversions at all. So it is best to look out for these potential issues yourself. Anyway, the following SQL code gets a data mismatch error because a CHAR string is being compared with an integer value: SELECT lastname, salary FROM employee WHERE depto_id = firstname ERROR: operator does not exist: integer = character varying LINE 3: WHERE depto_id = firstname Example 9: Mismatched data types No operator matches the given name and argument type(s). You might need to add explicit type casts to solve this one. 6. Use IS NULL When Comparing NULL Values If you only need to verify whether a column has a NULL value, pay special attention to which expressions you use. One common mistake is to use = NULL or <> NULL, but these expressions are not syntactically valid. Use IS NULL and IS NOT NULL clauses instead. Let's see the incorrect and correct samples: SELECT firstname, lastname FROM employee WHERE depto_id = NULL Example 10: Incorrect NULL comparison SELECT firstname, lastname FROM employee WHERE depto_id is NULL Example 11: Correct NULL comparison 7. Always Include the JOIN Condition There is more than one valid way to do a join in SQL. The traditional way is to list all the tables to be joined in the FROM clause and put the join conditions in the WHERE clause to build pairs of records. The other (more declarative) way is to use the JOIN clause and list the join conditions after the ON clause. Both are syntactically equivalent, but you should know how to identify the join condition for both. Here we have two valid joins: SELECT lastname, name FROM department, employee WHERE department.depto_id = employee.depto_id SELECT lastname, name FROM department JOIN employee ON department.depto_id = employee.depto_id Example 12: Two equivalent joins However, the tip is: Don't forget the join condition! Every time you join two or more tables, you must write a join condition to link both tables. If you don't specify this, you won't get an error message; you'll just get incorrect results. These will be wrong because every record from the first table will be joined with all records of the second table. This type of result set is called a Cartesian product of two tables, and usually is not an expected result. SELECT lastname, name FROM department, employee Example 13: A Cartesian product – usually not the result you want 8. Include Non-Aggregated Columns from the SELECT List in GROUP BY Columns When using aggregate functions, there are some restrictions on what columns can be included in the SELECT list (i.e. the column names after the SELECT clause). You can only include the columns specified in the GROUP BY clause, plus aggregate functions and constants. If you select only aggregate columns, any other column will generate an error. You can see this in the following example. SELECT department.depto_id, name , count(*) employees FROM department, employee WHERE department.depto_id = employee.depto_id GROUP BY department.depto_id ERROR: column "department.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT department.depto_id, name , count(*) employees Example 14: Extra columns in the SELECT list SELECT department.depto_id, name , count(*) employees FROM department, employee WHERE department.depto_id = employee.depto_id GROUP BY department.depto_id Example 15: No extra columns in the SELECT list Sometimes we need to filter data by using an aggregation function. One of the most common mistakes is to put a condition using the aggregate function in the WHERE clause. This is shown below: SELECT department.depto_id, name , count(*) employees FROM department, employee WHERE department.depto_id = employee.depto_id AND count(*) > 1 GROUP BY department.depto_id ERROR: aggregate functions are not allowed in WHERE LÍNEA 4: AND count(*) > 1 Example 16: Incorrect aggregate function in the WHERE Remember, if you need to filter using an aggregate function, the correct way is to put the condition using the aggregate in the HAVING clause, as in the following example: SELECT department.depto_id, name , count(*) employees FROM department, employee WHERE department.depto_id = employee.depto_id HAVING count(*) > 1 GROUP BY department.depto_id Example 17: The aggregate function is in the HAVING clause Try It Yourself! LearnSQL is a great place to get started with SQL. Several courses are geared for beginning learners. You can test the tips in this article on the exercises in this SQL course. Tags: how to how to in sql You may also like Preventing Common SQL Mistakes This article will discuss some of the common SQL mistakes you’ll face and will help you correct them easily Read more Five Common SQL Syntax Errors 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. Read more How to Organize SQL Queries When They Get Long Long queries are very hard for beginners to structure and understand. Learn the best practices for writing and formatting complex SQL code! Read more SQL Errors: Five Common SQL Mistakes 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. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.