Tuesday, May 13, 2014

Sequence of SQL Query Statement

As a developer all of us write SQL queries for access and manipulate data in  the database. When retrieve data from database SQL query start with SELECT statement. In these SQL queries are create English-like manner. But its logical execution not process like that way. The SELECT statement logically execute in following order.

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING 
  5. SELECT
  6. ORDER BY 
Following SQL query retrieve employee detail from EmployeeLeave table. 


SELECT EmpId, FirstName, COUNT(*) AS NumLeaves
FROM EmployeeLeave
WHERE EmpId = 12
GROUP BY DepId, LeaveYear
HAVING COUNT(*) > 1
ORDER BY EmpId, DepId

But this query logically process in following manner. 

FROM EmployeeLeave
WHERE EmpId = 12
GROUP BY DepId, LeaveYear
HAVING COUNT(*) > 1
SELECT EmpId, FirstName, COUNT(*) AS NumLeaves
ORDER BY EmpId, DepId

Following shows the way of this query works,
  1. Queries the rows from EmployeeLeave table
  2. Filters only records where employee ID equal to 12
  3. Groups the records by department ID and leave year
  4. Filters only groups having more than one leave
  5. Selects for each group the employee ID, first name and number of leaves
  6. Order the rows in the output by employee ID and department ID
The knowing the way of logical execution of SQL query is very important when write the queries for our business scenarios. It give us more understand about how our query works and its a big help to structure the query as well.  

No comments :

Post a Comment