Logical Processing of SELECT Statement

Part 1

Click below for you tube video

we tend to think the order of the SELECT Statement starts from SELECT Clause but which is not the case. Below is the logical order of processing of a SELECT statement.

Below is the Logical processing of SELECT Statement:

(8)

7 SELECT DISTINCT  D.DeptName AS Department, COUNT(E.Empid) AS                                   Count_Of_Employees

1 FROM [dbo].[tblEmployee] E

3 JOIN dbo.tblDept D

2 ON E.Dept_id = D.Deptid

4 WHERE E.EmpLocation <> 'London'

5 GROUP BY D.DeptName

-- WITH CUBE or WITH ROLLUP [Supergroups (groups of groups)]

6 HAVING COUNT(E.Empid) > 1

9 ORDER BY D.DeptName DESC

10 OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY; -- /TOP Clause

The Query Processor will evaluate the FROM Clause first, followed by ON and JOIN Clauses. 

Then the data will be fed to the WHERE Clause there the filtering of rows will be done based on the condition specified. 

Then the GROUP BY Clause will be executed by grouping the data based on grouping condition. If there are any Super Groups used in the query i.e. WITH CUBE or WITH ROLLUP then those will be executed.

The grouped data then will be filtered by HAVING Clause. Then the SELECT Clause will be executed retriving the D.DeptName column from tblDept table and then aliasing it as Department, also it will retrieve count of E.Empid count upon grouping and alias it as Count_Of_Employees

Then these aliased columns will be available for the ORDER BY Clause to use. Finally the OFFSET or TOP Clause will be executed.

To Demonstrate the same, lets create 2 tables tblEmployee  and tblDept and try it out.

Script to Create tables: 

CREATE TABLE [dbo].[tblEmployee](

[Empid] [int] NOT NULL,

[EmpName] [varchar](50) NOT NULL,

[EmpLocation] [varchar](50) NOT NULL,

[DOJ] [date] NOT NULL,

[Dept_id] [int] NULL,

[Gender] [varchar](10) NOT NULL,

[DOB] [date] NOT NULL,

[Salary] [int] NULL) 

GO

CREATE TABLE [dbo].[tblDept](

[Deptid] [int] NULL,

[DeptName] [varchar](15) NULL

)

GO

INSERT INTO [dbo].[tblEmployee]

           ([Empid],[EmpName],[EmpLocation],[DOJ],[Dept_id],[Gender],[DOB],[Salary])

     VALUES

           (1,'smith','Newyork','2012-01-30',4,'Male','1979-03-13',8000)

  ,(2,'Allen','Dallas','2013-04-21',1,'Male','1979-03-13',7000)

  ,(3,'Jones','Newyork','2015-06-22',4,'Male','1979-03-13',9000)

  ,(4,'Scott','Dallas', '2014-02-14',4,'FeMale','1979-03-13',7000)

  ,(5,'Adams','London', '2019-08-06',3,'Male','1979-03-13',4000)

  ,(6,'Martin','Chicago','2020-10-24',2,'Male','1979-03-13',7500)

  ,(7,'Ward','Scotland','2013-03-18',2, 'FeMale','1979-03-13',8500)

  ,(8,'Peter','Newyork','2010-09-28',1, 'Male', '1979-03-13',9500)

GO

-- Inserting data in to tblDept table

INSERT INTO [dbo].[tblDept]

           ([Deptid],[DeptName])

     VALUES

           (1,'HR')

         ,(2,'Finance')

         ,(3,'IT')

 ,(4,'Sales')

SELECT * FROM [dbo].[tblEmployee]

SELECT * FROM [dbo].[tblDept]

To Demonstrate of the query works in the same way, lets tweak the query as below and check for each of the Clauses.

-- WHERE executes before SELECT clause

SELECT D.DeptName AS Department, COUNT(E.Empid) AS Count_Of_Employees

FROM [dbo].[tblEmployee] E

JOIN dbo.tblDept D

ON E.Dept_id = D.Deptid

WHERE D.Department <> 'London' --> Red squiggly line implies that WHERE executes Before SELECT clause

GROUP BY D.DeptName

HAVING COUNT(E.Empid) > 1

ORDER BY D.DeptName DESC

OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;

As seen in the above screenshot the column in the WHERE clause is not evaluated. i.e the SELECT has not been executed hence it shows the red squiggly line

-- GROUP BY executes before SELECT clause

SELECT D.DeptName AS Department, COUNT(E.Empid) AS Count_Of_Employees

FROM [dbo].[tblEmployee] E

JOIN dbo.tblDept D

ON E.Dept_id = D.Deptid

WHERE E.EmpLocation <> 'London'

GROUP BY Department  --> Red squiggly line implies that GROUP BY executes Before SELECT clause

HAVING COUNT(E.Empid) > 1

ORDER BY D.DeptName DESC

OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;

As seen in the above screenshot the column in the GROUP BY clause is not evaluated. i.e the SELECT has not been executed hence it shows the red squiggly line

-- HAVING executes before SELECT clause

SELECT D.DeptName AS Department, COUNT(E.Empid) AS Count_Of_Employees

FROM [dbo].[tblEmployee] E

JOIN dbo.tblDept D

ON E.Dept_id = D.Deptid

WHERE E.EmpLocation <> 'London'

GROUP BY D.DeptName  

HAVING Count_Of_Employees > 1 --> Red squiggly line implies that HAVING executes before SELECT clause

ORDER BY D.DeptName DESC

OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;

As seen in the above screenshot the column in the HAVING clause is not evaluated. i.e the SELECT has not been executed hence it shows the red squiggly line

-- ORDER BY executes after SELECT clause

SELECT D.DeptName AS Department, COUNT(E.Empid) AS Count_Of_Employees

FROM [dbo].[tblEmployee] E

JOIN dbo.tblDept D

ON E.Dept_id = D.Deptid

WHERE E.EmpLocation <> 'London'

GROUP BY D.DeptName  

HAVING COUNT(E.Empid) > 1 

ORDER BY Department DESC --> implies that ORDER BY executes After SELECT clause

OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;

As seen in the above screenshot the column in the ORDER BY clause is evaluated. i.e the SELECT has been executed hence it does not shows the red squiggly line.

Summary:

Logical processing of SELECT statement is as below:

1. FROM

2. ON

3. JOIN

4. WHERE

5. GROUP BY

6. SUPER GROUPS (WITH CUBE or WITH ROLLUP) 

7. HAVING

8. SELECT 

9. DISTINCT

10. TOP / OFFSET

In Many cases we may not be using all the Clauses in the same select statement unless we are addressing a complex business scenario.

The Interviewer may provide you a query and will ask you if the query will execute successfully or not? If not, whats the reason for it?

You have to check for order of SELECT to figure out if there are any SELECT aliases used in WHERE, GROUP BY, HAVING Clauses. 

However, there may be other reasons as well, why the query given by interviewer may not work.

Please do subscribe to my youtube channel and like the facebook page. Drop an email to sql.ravimartha@gmail.com if you need any help :)

Youtube Channel:
https://www.youtube.com/channel/UCSmWjwGkxhgHTUBvQEA3l4Q/

Video Link: https://youtu.be/YZB8p2Anhr4

Facebook Page:
https://www.facebook.com/SQL-With-RaviMartha-109497027391239/
https://www.facebook.com/ETL-DWH-Testing-100494175057349/