Defensive SQL Query Writing

Derived from defensive programming, defencive query writing is a practice which tries to make sure that a query run doesn't fail. Just like in application development, try to remove the scope for silly mistakes and control unforseen circumstances. Sometimes the decision comes down to whether you want your query to fail or you want it to run even if with some incorrect data. I'll share a couple of simple examples where we can employ these practices while writing queries.

Checking If Objects Exist

Take the very basic example of creating and dropping database objects. Rather than using a CREATE TABLE xyz, use CREATE TABLE IF NOT EXISTS xyz (id int) or if you want to recreate the table losing all the data you can run DROP TABLE IF EXISTS xyz and then CREATE TABLE IF NOT EXISTS xyz (id int).

The same practice can be used with the creation and deletion of databases, views, indexes, triggers, procedures, functions and more. I have come to realize that in most cases, using this is helpful.

Using database and column aliases

Prevent yourself from getting ambiguous column errors. See in the example below, the column city might be present both in TABLE_1 and TABLE_2. How do you expect the database to know which field you want it to pick up.

It's generally a very good practice to create aliases for database objects and then access those database objects and their child objects using the alias rather than the complete name. Obviously for doing this efficiently, you'd need to follow a SQL Style Sheet.

Using LIMIT

No, I'm not talking about using LIMIT to restrict the number of records in your final query. Rather, I am talking about queries like this.

The LIMIT clase in the subquery returning a column is important because it prevents the query from failing if the subquery returns more than one row, that is, if there is more than one record in TABLE_2 for every record in TABLE_1. This is a really useful trick to write better queries.

These are three of the most common scenarios which, if not taken care of, can prevent your query from running at all. Obviously, all of these come with an asterix. More on that later.

Please feel free to share other practices that you have followed!

Comments

[Evaldas Buinauskas]

The second query might prevent query from failing, but subquery returning more than a single row might be a bug in the data model and using LIMIT could be hiding that.

[Kovid Rathee]

@Evaldas - You're right. I mentioned that. That's why it must be used carefully!