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.
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.
Prevent yourself from getting ambiguous column errors. See in the example below, the column
city might be present both in
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 —
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. Don't miss this helpful article.
Data Science courses online taught by best data scientists & IBM experts will masters you in data analytics. Get IBM Data Scientist certification course training!
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...
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.