Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why are aggregate functions not allowed in where clause

I am looking for clarification on this. I am writing two queries below:

We have a table of employee name with columns ID , name , salary

  1.  Select name from employee      where sum(salary) > 1000 ;    2.  Select name from employee      where substring_index(name,' ',1) = 'nishant' ; 

Query 1 doesn't work but Query 2 does work. From my development experience, I feel the possible explanation to this is:

The sum() works on a set of values specified in the argument. Here 'salary' column is passed , so it must add up all the values of this column. But inside where clause, the records are checked one by one , like first record 1 is checked for the test and so on. Thus sum(salary) will not be computed as it needs access to all the column values and then only it will return a value.

Query 2 works as substring_index() works on a single value and hence here it works on the value supplied to it.

Can you please validate my understanding.

like image 479
Nishant_Singh Avatar asked Feb 26 '17 16:02

Nishant_Singh


People also ask

Why aggregate functions Cannot be used in WHERE clause?

We cannot use the WHERE clause with aggregate functions because it works for filtering individual rows. In contrast, HAVING can works with aggregate functions because it is used to filter groups.

Can we use aggregate function in WHERE clause in Oracle?

Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups.

Can aggregate functions be used with HAVING clause?

The having clause can contain aggregate functions. It cannot contain aggregate functions.

Which clause is similar to WHERE clause and used with aggregate functions?

Explanation: “HAVING” clause are worked similar as “WHERE” clause.


1 Answers

The reason you can't use SUM() in the WHERE clause is the order of evaluation of clauses.

FROM tells you where to read rows from. Right as rows are read from disk to memory, they are checked for the WHERE conditions. (Actually in many cases rows that fail the WHERE clause will not even be read from disk. "Conditions" are formally known as predicates and some predicates are used - by the query execution engine - to decide which rows are read from the base tables. These are called access predicates.) As you can see, the WHERE clause is applied to each row as it is presented to the engine.

On the other hand, aggregation is done only after all rows (that verify all the predicates) have been read.

Think about this: SUM() applies ONLY to the rows that satisfy the WHERE conditions. If you put SUM() in the WHERE clause, you are asking for circular logic. Does a new row pass the WHERE clause? How would I know? If it will pass, then I must include it in the SUM, but if not, it should not be included in the SUM. So how do I even evaluate the SUM condition?

like image 51
mathguy Avatar answered Sep 21 '22 01:09

mathguy