SQLPad Forum question: quetion 35 order of execution

quetion 35 order of execution

I am trying to solve question 35,

I Solved it using this code

with cte1 as (

SELECT *,

case when length<60 then 'short'

when length >=60 and length <100 then 'medium'

else 'long'

end as Cat

FROM film

)

select cat, count(film_id)

from cte1

group by cat

but the solution provided is different where the case statement was directly in select statement

SELECT
  CASE WHEN length < 60 THEN 'short'
  	   WHEN length < 100 THEN 'medium'
  	   WHEN length >= 100 THEN 'long'
  	   ELSE NULL
  	   END AS film_category,
  COUNT(*)
FROM film
GROUP BY film_category;

Just have a question about order of execution of statements i know from statement is executed first then group by then select , but we dont have column film_category when execution is done right?

Answers

Comment

Hi Swetha,

Your question about the order of execution in SQL queries, particularly in relation to the CASE statement and the GROUP BY clause, is a great one.

In SQL, the actual order of execution is as follows:

FROM clause
WHERE clause (if present)
GROUP BY clause
Aggregate functions (COUNT, SUM, etc.)
HAVING clause (if present)
SELECT clause
ORDER BY clause (if present)

Given this order, it might seem counterintuitive that you can use an alias defined in the SELECT clause (like film_category in your example) in the GROUP BY clause. However, SQL allows this because, by the time the GROUP BY is actually processed, all column expressions and aliases in the SELECT clause are known to the query processor.

In your first query, you used a common table expression (CTE) to first define the Cat column and then used it in the outer query. This is a perfectly valid approach and often makes the query more readable, especially in complex scenarios.

The solution provided takes a more direct approach by defining the film_category in the SELECT clause and immediately using it in the GROUP BY. This works because SQL databases typically allow the use of column aliases in the GROUP BY clause since the alias definitions are processed before the grouping is applied.

Both methods are correct, and the choice between using a CTE or directly placing the CASE statement in the SELECT clause often depends on the complexity of the query and personal or organizational preferences for readability and maintainability.

- Leon

Leon (949)

Dec. 16, 2023, 12:28 p.m.