pavankalyan
How an SQL query executes ?

How an SQL query executes ?

Pavan Kalyan's photo
Pavan Kalyan
·

3 min read

Hey there How is it going ? Hope everything is Fine . . .

While performing SQL queries , Most of us even me just write the query and execute it and check whether it is giving desired output or not . if not we will make some changes and again throw a query but its not a good thing to do in real world production .We make mistakes due to lack of intuition of order of execution of an SQL query. Lets understand how an SQL query executes and what is the order of execition of an SQL query

STEP 1 : PARSING

Parsing of a query is the process by which this decision making is done that for a given query, calculating how many different ways there are in which the query can run. Every query must be parsed at least once.

  • SYNTAX CHECK

    In this process , Query will be checked whether the syntax of the query is correct or not. If something is wrong then the execution gets terminated

    Here is an example :

         SELECT name FRO students;  //FRO is not a keyword in SQL
  • SEMANTIC CHECK

    The semantic check confirms that whether the field names ,table names , . . . are valid or not Lets we have a table students of columns name , regdno. Here is an example :

          SELECT nae FRO students; //nae is not a column on our DB

So if everything goes fine with syntax and semantics then it moves to execution part

STEP 2 : EXECUTION

Lets understand the execution using a query.

          SELECT DEPARTMENT
          FROM EMPLOYEES
          WHERE SALARY > 80000
          GROUP BY DEPARTMENT
          ORDER BY COUNT(*) DESC;

  • The orders of execution will remain like this :

    1 . FROM 

       i e, FROM EMPLOYEES
  This will retreive all the data from the employees table

    2 . WHERE

       i e , WHERE SALARY > 80000
   This will filter all the data from the retreived data 
   based on condition

    3 . GROUP BY 

        i e , GROUP BY DEPARTMENT
   This will group all the rows having same department
   that means there will be only one row for a department

    4 . SELECT 

       i e , SELECT DEPARTMENT
  This will select all the rows from the above achieved data

    5 . ORDER BY

      i e , ORDER BY COUNT(*) DESC
  Orders the resultant data in decreasing order

The execution order remains same for almost all the queries those having the above keywords which are frequently used .So from next while performing a query operation keep these things and analyze how query is executing . It will help you in long term as DBMS is a necessary core subject for cs guy. Hope you enjoyed it

SQLMySQLDatabases