CODEWITHSUNDEEP
oracle-databasejoinwhere-clausehaving
Victor
Victor

Reputation: 17107

Is WHERE clause applied before JOIN

Is it fair to say that a WHERE clause will be applied on the row before any joins are applied, whereas a HAVING clause will be applied on the final result set after all joins and all aggregate functions have been applied?

Upvotes: 2

Views: 4331

Answers (2)

Ronnis
Ronnis

Reputation: 12843

WHERE vs HAVING makes sense only for SQL statements including a GROUP BY clause. Filter predicates in the WHERE clause are applied first, then aggregation, and then filter predicates in the HAVING clause.

Example:

create table t(
   a varchar2(10) not null
  ,b varchar2(10) not null
  ,n number       not null
  ,primary key(a,b) 
);

insert into t values('A', '1', 10);
insert into t values('A', '2', 20);

insert into t values('B', '1', -10);
insert into t values('B', '2', 10);

insert into t values('C', '1', 0);
insert into t values('C', '2', 10);
insert into t values('C', '3', 20);

In the following query, {C,1} is removed before aggregation. See the count(*). 

select a, sum(n), count(*)
  from t
 where n <> 0
 group by a;

A  SUM(N) COUNT(*)
== ====== =====
A  30     2
B  0      2
C  30     2

In this query, both {B,1} and {B,2} are removed since their sum is 0. But notice that {C,1} is included. 

select a, sum(n), count(*)
  from t
 group by a
 having sum(n) <> 0; 

A  SUM(N) COUNT(*)
== ====== =====
A  30     2
C  30     3

One final note: Filter predicates in the WHERE clause are performed before join operations. At least "logically" . However, I can imagine some cases involving query rewrites against Materialized Views such as a pre-joined tables with indexes when this works a bit different "under the hood". Still, the result is the same.

Upvotes: 0

zerkms
zerkms

Reputation: 255025

Is it fair to say that a WHERE clause will be applied on the row data before any joins are applied

Nope, optimizer may change the order of joined tables. So the joined table may technically go before the table you've specified in FROM. So technically a condition from WHERE may become a part of a JOIN condition.

HAVING clause will be applied on the final result set after all joins and all aggregate functions have been applied

That's right

Upvotes: 2

Related Questions