Derived from defensive programming, defensive 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 unforeseen 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 Database 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.

SELECT city, 
	  FROM TABLE_1 AS t1 
	  LEFT JOIN TABLE_2 as t2
	    ON t1.id = t2.id;
view raw
defensive_coding_sql_alias.sql hosted with ❤ by GitHub

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. I have written more about it here —

#security #towards-data-science #programming #data #data analysis #data analysis