365 Days of Daily Coding: Day 22

Order of operation in SQL is one of the topics that is often not covered in many of the Introductory SQL online courses out there in the market but in my opinion is an equally important concept to know as a beginner. I was intrigued by the variance in the amount of time taken to execute queries that give the same result.

You could arrive at the same result but the logic used determines the speed of the queries. To produce efficient and faster queries, one need to understand the order of operation in Oracle. I really didn’t think there would be such things but was wrong about it.

Order	Clause	Function
1	from	Choose and join tables to get the base data
2	where	Filters the base data
3	group by	Aggregates the base data
4	having	Filters the aggregated data
5	select	Returns the final data
6	order by	Sorts the final data
7	limit	Limits the returned data with a row count

What to me as a beginner was the most interesting was the difference between ‘WHERE’ and ‘HAVING’. ‘WHERE’ filters the table and gives multi-rows based on the conditions set where as ‘HAVING’ filters the table after the rows have been grouped by the field that is set in ‘GROUP BY’.

If you were to filter a table to return rows for a particular group, you cannot use ‘WHERE’ instead a ‘GROUP BY’ has to be used.

Id	Name	Department	Salary
1	Jake	BI	4000
2	Jacobs	BI	5000
3	Jenny	Maths	6000

#The following code returns error that says "not a single-group group function"
SELECT department, SUM(salary) 
FROM table1 
WHERE department = 'BI';

#The correct way to query is as follows
SELECT department, SUM(salary) 
FROM table1 
GROUP BY department 
HAVING department = 'BI';

I found three blogs that explain the concepts of the order of operations really well:

1. Sisense
2. Learnsql
3. Sqlbolt

• Facebook
• Instagram
• LinkedIn
• GitHub