You are here

SQL: Logical Query Processing Phases

logical query processing phases

Logical Query Processing Phases

In this post we will discuss logical query processing phases in SQL server. Logical query processing means in which order the sql query clause is evaluated.

  • The below table represent the order of clause evaluation in SQL:

Evaluation No

SQL clause

1

From (join, pivot, unpivot, apply)

2

Where (predicate)

3

Group by (grouping sets, rollup, cube)

4

Having (group predicate)
5

Select (select_list, DISTINCT )

6

Order by (order by_list)

7

TOP, OFFSET-FETCH

Example:

Table: tbllogical

Id

name country hiredate
1 Riya India

2017-07-28

2

Khyati India 2017-04-16
3 Ankit U.K

2016-09-12

4

Amisha U.K 2016-07-23
5 Anamika U.S.A

2015-04-17

6

Sunami Canada

2014-01-01

Query:

select country, YEAR(hiredate)as yearhired, COUNT(*) as noofemploy from tbllogical
where hiredate>’20140101′
group by country, YEAR(hiredate)
having COUNT(*)>1
order by country, yearhired DESC;
output:

Name

country hiredate

India

2017

2

U.K 2016

2

Note: in this post we used above example for understanding the logical query processing.

1. FROM clause:

  • In the first phase, the FROM clause is evaluated. This clause indicate the place where you defined table name you want to query and table operators like joins.

Example:
Select * from tbllogical;
Output:

Id

name Country hiredate
1 Riya India

2017-07-28

2

Khyati India 2017-04-16
3 Ankit U.K

2016-09-12

4

Amisha U.K 2016-07-23
5 Anamika U.S.A

2015-04-17

6

Sunami Canada

2014-01-01

2. WHERE clause:

  • The second phase filters rows based on the predicate in the WHERE clause.
  • The WHERE clause is evaluated after the FROM clause.
  • This clause returns only rows for which predicate evaluate to true.
  • We can not used alias defined in the select clause in the WHERE clause because this clause is evaluated after the SELECT clause.

Example:
select id, country, hiredate
from tbllogical where hiredate>’20140101′;
Output:

Id

country hiredate
1 India

2017-07-28

2

India 2017-04-16
3 U.K

2016-09-12

4

U.K 2016-07-23
5 U.S.A

2015-04-17

3. GROUP BY clause:

  • This phase defines a group for each distinct combination of values in the grouped elements from the input table.
  • It associate each input row to its respective group. If we have to use group by clause then we have to use at least one aggregate function like SUM(), MUL(), COUNT() etc.
  • There is difference between WHERE and HAVING clause in SQL. The WHERE clause evaluated before rows are grouped and it is operate per row. The HAVING clause is evaluated after rows are grouped, and it is operator per group.

Example:
select country, YEAR(hiredate)as yearhired, COUNT(*) as noofemploy from tbllogical where hiredate>’20140101′
group by country, YEAR(hiredate);
output:

Country

yearhired noofemploy
U.S.A 2015

1

U.K

2016

2

India

2017

2

4. HAVING clause:

  • This phase is also responsible for filtering data based on a predicate, but it is evaluated after the data been grouped; it is evaluated per group and filter groups as a whole.
  • Only grouped for which the predicate is true is returned from this phase.

Example:
select country, YEAR(hiredate)as yearhired, COUNT(*) as noofemploy from tbllogical where hiredate>’20140101′
group by country, YEAR(hiredate)
having COUNT(*)>1;
output:

Country

yearhired noofemploy
U.K 2016

2

India

2017

2

5. SELECT clause:

  • This phase is responsible for output of the query.
  • It has two main step: first step is evaluating the expression in the SELECT and producing the result attribute. Second step is in this phase is applicable if you indicate the DISTINCT clause, this remove duplicates.
  • Example is same as Having clause.

6. ORDER BY clause:

  • This phase is applicable if the query has order by clause. It is evaluated after the select clause.
  • This phase is responsible for returning the result in a specific presentation order according to the expressions that appear in the ORDER BY list.
  • Default order is ascending in the order by clause.

Example:
select country, YEAR(hiredate)as yearhired, COUNT(*) as noofemploy from tbllogical
where hiredate>’20140101′
group by country, YEAR(hiredate)
having COUNT(*)>1
order by country, yearhired desc;
output:

Country yearhired noofemploy
India 2017 2
U.K 2016 2

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my new blog Mag4info. and for kotlin tutorial also see my blog kotlin category.

You can also by SQL 70-761 book write by me from Amazon.

Leave a Reply

Top