how does a SQL query work?

sql execution order:

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT .

SQL Query mainly works in three phases .

1) Row filtering – Phase 1: Row filtering – phase 1 are done by FROM, WHERE , GROUP BY , HAVING clause.

2) Column filtering: Columns are filtered by SELECT clause.

3) Row filtering – Phase 2: Row filtering – phase 2 are done by DISTINCT , ORDER BY , LIMIT clause.

In here i will explain with an example . Suppose we have a students table as follows:

Now we run the following sql query:

select section_,sum(marks) from students where id_<10 GROUP BY section_ having sum(marks)>100 order by section_ LIMIT 2;

Output of the query is:

But how we got this output ?

I have explained the query step by step . Please read bellow:

1. FROM , WHERE clause execution

Hence from clause works first therefore from students where id_<10 query will eliminate rows which has id_ greater than or equal to 10 . So the following rows remains after executing from students where id_<10 .

2. GROUP BY clause execution

now GROUP BY clause will come , that’s why after executing GROUP BY section_ rows will make group like bellow:

3. HAVING clause execution

having sum(marks)>100 will eliminates groups . sum(marks) of D group is 185 , sum(marks) of A groupd is 247 , sum(marks) of B group is 131 , sum(marks) of C group is 35 . So we can see tha C groups’s sum is not greater than 100 . So group C will be eliminated . So the table looks like this:

4. SELECT clause execution

select section_,sum(marks) query will only decides which columns to prints . It is decided to print section_ and sum(marks) column .

5. ORDER BY clause execution

order by section_ query will sort the rows ascending order.

6. LIMIT clause execution

LIMIT 2; will only prints first 2 rows.

This is how we got our final output .