Are your SQL queries really Structured?

SQL or Structured Query Language (often pronounced as ‘Se-quel’) is a popular programming language used by developers, data scientists, data analysts and business analysts to insert, retrieve, and update data from or into large databases.

SQL enables data professional to communicate with huge databases and investigate, search strings, numbers, patterns and filter for the exact sort of data they require, much quicker!

SQL is great, but are your SQL queries really in the structure that they supposed to be?

Syntax

Every programming language has its own structure and set of rules for writing statements and expressions. For SQL it is no different, For all the database systems (with Relation Database Management System) SQL syntaxes are almost similar except few modifications particular to that database system or software.

SQL queries have a basic common syntax that follows:

SELECT: To choose the columns to display from the result set.

FROM: To select the database table from which query will fetch the results

WHERE: To filter out the data or apply the specific condition.

Example: You want to fetch the name of an employee whose employee ID is GM17912 from the employee_details table.

SELECT
      first_name,
      last_name
FROM
      employee_details
WHERE
      id = 'GM17912';

Capitalization

It is recommended to capitalize keywords like SELECT, FROM, WHERE, AND, ORDER BY etc to clearly distinguish them from query parameters.

SELECT 
      id,
      category
FROM
      product_info
WHERE
      price < 3000;

Indentation for Multiple fields

It is advised to write statement blocks at every new line and to maintain indentation for different fields in the query. It helps to clearly understand what all columns have been used to display result or what filtering conditions have been used in the query .

SELECT 
      show_id,
      title,
      type
FROM
      netflix_shows
WHERE
      country = 'India' 
      AND release_year = '2020'
      AND duration_min <= '180'

Comments

Comments play an integral role in determining the readability of piece of code. Comments should be concise but should explain the purpose of statements or functions.

In SQL, comments can be added in two ways,

1. Single line comments, Adding ‘--’ before text and SQL will ignore the content after --

SELECT
      id,   -- Display volunteer's id in result
      age   -- Display volunteer's age in result
FROM
     vaccination
WHERE 
     date IS LIKE '%-MAR-21'

2. Multi line comments, Write comments in /* ..*/ and SQL will not execute content in block /* — */

/* Gets count of all customers
whose connection type 
is OFC in Zone 21*/
SELECT 
      count(*) 
FROM
     isp.zone21
WHERE
     type = 'OFC'

Optimized Queries

Writing queries in structured format is a good practice but it is just a first step towards writing efficient queries. However, to improve the performance of query some best practices need to be followed.

1. Avoid using SELECT *
   Selecting only required columns to fetch data helps database system to avoid searching into the whole table. Always refer to the table structure in case you are unaware about column details.

/* Ineffiecient way of           |  /* Efficient approach */
writing SELECT query */          | 
SELECT                           | SELECT
      *                          |       chassis_id
FROM                             | FROM
      vehicle                    |       vehicle

2. Use LIMIT to narrow display of result set

LIMIT returns only number records specified in the query statement and avoid taxing the whole database

SELECT
      name,
      title
FROM
      imdb.movies
WHERE 
      genre = 'Comedy'
LIMIT 5;

3. Avoid usage of SELECT DISTINCT

DISTINCT clause is used to remove duplicate entries, DISTINCT works by gathering all the fields in query statements and then removing duplicates. This process takes a lot compute power and time hence is advised minimize usage of DISTINCT and instead efficiently write filtering conditions

SELECT DISTINCT
       product_type
FROM  
       inventory

I hope you find this article helpful, please check out my other writings also on my publication, Thank you for reading!