Eliminating Errors in SQL
Standard Query Language (SQL) is a language to manipulate data and allow you to view your data in a particular way; one that is helpful to the work you may be doing. Like any language, SQL has many quirks in the style and syntax, and if you’re not careful, you might land yourself viewing an error or worse: unexpected results.
While learning SQL and writing many queries, I’ve run into several errors that are small but can easily make your queries produce errors or unexpected results.
Know the order clauses go in
You might find solace in the fact that relational algebra is the primary application for the theoretical foundation of relational databases. Like algebra, SQL has a set order of operations for the format of the query: [SELECT] [FROM] [WHERE] [GROUP BY] [HAVING] [ORDER BY]. If you’re a fan of pneumonic devices, you may find this pneumonic to help you: So Few Workers Go Home On Time. I bet you nobody will know the SQL order of operations by heart forever so the above pneumonic will forever be beneficial.
Try not to nest aggregate functions
If you’re familiar with HTML or MS Excel, then I’m assuming you’re familiar with the concept of nesting. However, if you aren’t familiar with nesting, nesting is simply the technique of placing a function within another function. Depending on the Relational Database Management System (RDBMS) you use, you’ll get an error when executing a query with nested aggregate functions. To work around this, you can either use a subquery or use the
WITH statement that precedes the
SELECTstatement to execute your query.
Balance parentheses and other characters
One of the keys to eliminating errors in your SQL statement is going through your statement and making sure every open character you have has a closing counterpart. Programs such as pgAdmin 4 will automatically do this for you as this is considered best practice; however, it’s still something to watch out for when troubleshooting queries. SQL seems to be one of those languages that will not run if you have a single error; therefore, something as small as balancing characters can have a huge impact on whether or not you see your data retrieved or not.
Remember your comma
When selecting multiple columns, it is imperative to use a comma to tell the engine you’re selecting multiple columns. SQL is a very human-readable language meaning if you had no knowledge of the language and were asked to interpret a bare-bones statement such as
SELECT * FROM home; , you could tell me the statement is saying go out into the database and give me all the data from a table called
home. When forming your
SELECT statement, think of it as a list of items you would hand to your English teacher for evaluation (only don’t include ‘and.’)
Pay attention to what you’re typing
Similar to writing, it’s easy to know what you’re going to type, but at the same time, it’s relatively simple to make a typographical error. Many times when querying using a SQL engine on my local machine or when taking an online course, I run my query only to find it will not execute due to a simple typographical error such as misspelling the name of a clause. Simple typos such as
DISTINT instead of
DISTINCT as well as
GROUP instead of
GROUP BY can easily be overlooked and cause your query not to run at all.
Remember your semicolon
Ah, yes! The semicolon, the one character in the English language that hardly anyone knows how to use. Thankfully, it has one spot in your query, and that is at the end. Using a semicolon is the standard way of separating each SQL statement in database systems. It’s also necessary when executing more than one statement in the same call to a statement. If you’re using an online course to learn SQL such as CodecAcademy, your statement will be deemed incomplete if you forget a semicolon. Depending on your RDBMS, it may not be mandatory and therefore fine to leave off the semicolon at the end; however, it is considered a good practice to include it.
Logical Comparison of
NULL is a special marker used within SQL to denote a data value does not exist within a database. Since the value does not exist within the database, it is important to understand
NULL does not mean the field is either zero or it contains spaces, and it simply means it has been intentionally left blank during record creation. With the lack of value of the record, it is impossible to do a logical comparison with a
NULL field using
!=, or any combination of those. If you want to see if a field contains a
NULL value, use either
IS NULL or
IS NOT NULL and you’ll get your desired results.
Having ambiguous column names
This error is pretty self-explanatory: you have a column in your
SELECT statement coming from two or more tables in your
FROM statement and the RDBMS isn’t sure which column you mean. Normally in SQL, you don’t have to specify which column you mean because it’s not ambiguous. However, you’re telling SQL what to return. In other words, SQL is not a mind reader; it has to have crystal clear instructions on what specifically to return. This often happens when you join two tables together without giving the table an alias. With that said, it’s best practice to give tables aliases with joining.
JOIN using the wrong column
May not be an error everyone experiences but it is certainly something I encountered while completing the Vertabelo Academy SQL Advent Challange this past holiday (Winter 2017.) This whole basis of that challenge was to help Santa organize his Christmas deliveries as well as help him deliver gifts to children. While completing the challenge, I caught myself performing an
INNER JOIN two tables on a column that had the same name but did not have any relation to one another. Making this error led me to an answer that did not make sense nor did it fit in the crossword. Recognizing this error, later on, made me realize human errors are natural, and it’s important to question what is written and think “Does this make sense?” (Also because of the challenge I know Szczęśliwego Nowgo Roku is Polish for Happy New Year.)
Forgetting to specify the type of
Remembering to specify the type of
JOIN can lead you to your desired results. Without specifying, your RDBMS will interpret the
JOIN to mean
INNER JOIN, which produces all the common results that occur in both tables. This might not mean much if you do, in fact, want to use an
INNER JOIN but if you do run into a situation where you want to have the option of
RIGHT JOIN, or
FULL OUTER JOIN, you will have to specify the type of
JOIN in order to get your desired results. Also, if you’re having someone else look at your statement, they won’t have to guess what is going to happen during execution.
Ater reading this list, hopefully you feel comfortable writing queries and able to identify where things begin take an unexpected turn. By no means is this a list of every single thing that can go wrong with your queries. This is simply a compiled list based on my own experiences with query writing.
SQL is a language and like with every language; there is a learning curve associated with it. No matter how many years you spend doing something, it’s easy to forget the basics. Even the expert query writers may have difficulty sometimes. You know … the people who do this for a living. Take Ph.D. students and web developers for example:
One Last Thing…
If you liked this article, click the 👏 below so other people will see it here on Medium. It would mean a lot to me if other people could read my story :)
If you have any comments on my writing style or suggestions how to improve, please comment those below. I am always looking for ways to improve!!