Overcome common SQL mistakes

When working with SQL there are a few common mistakes that it's easy to make. SQL is not a very forgiving language if you don't have the syntax just right. And the errors that you'll get back, can be cryptic at best. So, if you're getting an error message, be sure to read it and see if it points you in the right direction. If you have a syntax error that you just can't find, start breaking down the statement into pieces that do work. And then put the statement together again, keeping an eye on when an error happens. It's easy to miss a set of parentheses or misspell a field name and when you're learning, it can be tricky to remember the order of clauses or whether a keyword is missing an important part.

Keep in mind that if you're using a text string, you need to put it in single quotes for it to be treated as text. And if for some reason your database has spaces in field names, which is allowed, but isn't really best practice, you'll need to use backticks around the name of the field when you use it in a statement. Depending on the software you're using, it could interpret database and field names as case sensitive, too, so watch for that. Some development environments will give you hints when it can figure out what you're trying to do, but often there's not much in the way of support if you're writing statements outside of an environment designed to help you out.

For that reason it's usually a good idea to keep an SQL focused app around so you can connect to your database and just work with it directly rather than troubleshooting through another tool. When you get a statement working the way you want it to, copy and paste it into a note or some other documentation that you keep as you build and learn. When you're copying and pasting SQL statements, it's important to make sure you're doing so in plain text. Many text editors that aren't designed for software development will make changes to text that cause problems. One of the most irritating problems to me is when a text editor changes quotation characters into so called smart quotes.

They may look better when printed on an elegant invitation, but smart quotes and a single straight quote are different characters. And SQL interpreters don't know what to do with fancy punctuation. So if you've copied some SQL from somewhere, and it looks right, but doesn't work, check for smart quotes. If you're sharing SQL queries with team members or someone else, it's a good idea to use plain files to contain them rather than pasting them into an e-mail or chat message. Not only does attaching statements as files help keep your actual communication clean, it can also make them easier to find and share later on.

Treating null values incorrectly can cause errors or unexpected behavior. Remember that a null value is not the same as zero, or no, or false. So if you're looking for employees that don't have ID numbers, checking for those equal to zero isn't the same thing as asking for those that are null. You don't want to use an equality operator to look for null values, you want to use IS NULL or IS NOT NULL. If you're making a change to a table, be sure that you're using a condition that will scope your change how you intend. Turn it into a select statement to make sure you aren't going to have unexpected behavior due to a typo or a logic mistake.

Remember, too, that most software will run all of the commands in a console window. So, if you're writing more than one at a time, keep them in a scratchpad, and switch them in one by one, or be sure to select the one you want to run, and then run it.