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.
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.
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.
The having clause can contain aggregate functions. It cannot contain aggregate functions.
Explanation: “HAVING” clause are worked similar as “WHERE” clause.
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With