What is the processing order of logical parts of the following query?
SELECT ClientId, SUM(t2.Amount) total FROM cliens t1 JOIN orders t2 ON t1.ClientId = t2.ClientId WHERE ClientName Like 'John%' GROUP BY ClientId HAVING t2.Amount > 100 ORDER BY total DESC LIMIT 1
Experience Level: Medior
Tags: DatabasesSQL
Answer
The database engine processes the query in the following order:
- FROM and JOINs - determines and filters the rows using criteria in ON clause
- WHERE - applies additional filters on the rows
- GROUP BY - groups the rows into groups by column values
- HAVING - filters groups
- ORDER BY - orders the remaining rows/groups
- LIMIT - filters on the remaining rows/groups
- SELECT - returns the selected columnd
Related Databases job interview questions
Can you use different column names for tables merged using UNION ALL? What will be the column names in the result?
DatabasesSQL MediorWhat is the difference between UNION and UNION ALL?
DatabasesSQL MediorWhen you use SQL Server, what is a difference between local and global temporary table?
DatabasesMS SQL ServerSQL SeniorWhat is a result of the query below for table with 10 rows when there are 2 clients with the headcount column having a NULL value, there are 7 customers with 1 head and 1 customer with 2 heads?
DatabasesSQL MediorWhat is a result of the following query for table with 10 rows when there are 2 clients with the name column having a NULL value?
DatabasesSQL Medior