Defensive Query Writing

Defensive Query Writing

Using defensive coding principles to write queries in SQL. Derived from defensive programming, defensive query writing is a practice which tries to make sure that a query run doesn’t fail.

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

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

How To Build A Data Science Career In 2021

In Conversation With Dr Suman Sanyal, NIIT University,he shares his insights on how universities can contribute to this highly promising sector and what aspirants can do to build a successful data science career.

Your Data Architecture: Simple Best Practices for Your Data Strategy

Your Data Architecture: Simple Best Practices for Your Data Strategy. Don't miss this helpful article.

Data Science Course Online - Top Data Scientist Training Program

Data Science courses online taught by best data scientists & IBM experts will masters you in data analytics. Get IBM Data Scientist certification course training!

Data Science Course in Dallas

Become a data analysis expert using the R programming language in this [data science](https://360digitmg.com/usa/data-science-using-python-and-r-programming-in-dallas "data science") certification training in Dallas, TX. You will master data...

What Are The Advantages and Disadvantages of Data Science?

Online Data Science Training in Noida at CETPA, best institute in India for Data Science Online Course and Certification. Call now at 9911417779 to avail 50% discount.