What are SQL Query execution steps with example?

What is SQL Query execution steps with examples?

In This article I will explain about all the different SQL Statements which are DDL,DML an DCL. The main focus is on SQL Select Statement execution. Every SQL statement has its own format and parser will execute every sql statement with specific order. First we will check about different SQL statements then we will try to decipher the idea of SQL Statement Execution Order. There are specific steps for SQL Query execution steps using which one can check the order of execution of the statement to apply the logic of business requirements accordingly.

1.DDL – Data Definition Language:

2.DML – Data Manipulation Language:

3.DCL – Data Control Language:

4.DQL – Data Query Language:

• SQL Select Statement : SQL Query execution steps  :

Select statement in SQL is used to fetch the records from the table. Here the main point is how that SQL Statement Execution order is done.To select the data from the database table SQL select statements are used.The actual result is stored in to Result table which is called as result-set which is stored in cache not in the main memory.We can fetch the records from the table by using  types:

Select Syntax by using ‘*’:

Select * from Table_name;

Select Syntax by using column names:

Select column_name1,column_name2 from table_name;

As considering the performance matrices the column name syntax is better than select * syntax.

Real Life Scenarios for SQL Select Statement:

Select statement is most common and very widely used sql statement.So there should be millions of scenarios of Select statement.I am explaining the basic scenarios of select statement.

Consider following table named ‘Employee’:

1.Scenario 1:

List the all information about Employee?

Query used:

Select * from Employee;

Select Employee_num,Employee_name,Department_ID,Salary from Employee;

The above 2 statement fetches all records of Employee.

2.Scenario 2:

Select the Employees whos salary is greater than 500000?

Query used:

Select * from Employee where salary > 500000;

Select Employee_num,Employee_name,Department_ID,Salary from Employee where salary>500000;

Output:

3.Scenario 3:

Fetch the department-wise total salary.

Query:

Select Department ID,Sum(Salary) from Employee group by Department_ID;

Output:

4.Scenario 4:

Select Departmentwise sum of salary where department_id is 100;

Query:

Select Department ID,Sum(Salary) from Employee group by Department_ID

having department_id=100;

Output:

What is SQL Query order of execution with real industry scenarios :

In this section i will try to give you idea about the SQL Statement Execution Order. There are some steps in that particular order the sql statement gets executed. So I will take one example here and will try to give you that SQL Statement Execution Order in depth.

Select Department ID,Sum(Salary) from Employee group by Department_ID

having department_id=100;

Step 1: From Clause Execution:

In Every select statement first step is execution of the statements in from clause.In above statement there is only one table so it directly takes the table.So in above statement first step optimizer or compiler points to table name Employee.

Step 2:Where Clause:

The second step is where clause.When there is no where clause in Select statement optimizer directly fetches all the results from specified table.Where clause filters the data in database.In above query there is no where condition so Optimizer directly takes all the records from the table.

Step 3:Group By clause

The Step 3 is Group by clause.If the query contains group by clause then the specified result of Query is partitioned in to different groups.One group is every combination of values given in to group by clause.In above query we are doing grouping the department id wise.

Step 4:Having Clause

If your Query contains having clause then it will executes after the group by clause. After grouping if user wants to filter the records then having conditions comes in picture.In our query we are taking sum of records having department id=100.

Step 5:Aggregate functions and select columns:

After fetching all the records and executing the clauses the columns which are taken in select statement gets executed.The functions like max,min,count,sum is executed first and then all columns taken in select statement gets executed last.

Hope everyone gets idea about select statement and SQL Statement Execution Order. Please comment below if you have any queries or if you like article.