This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Sep 20, 2013 12:49 AM by MarkCooper

# Difference-  in conditions (Join and Where Clause)

Currently Being Moderated

Hi Folks,

I need to be clear about what exactly difference when we put any condition in INNER JOIN and WHERE Clause.

I tried both way and found same results. Even in Statistics Plan not much differences.  All Help would be appreciated.

Like:

1. Here I am using location filter in Inner join condition -

"SELECT I.*, Gl * From Sc1.Item I

Inner Join Sc1.Part P

On P.Part_Id = I.Part_Id

Inner Join Sc1.Location Gl

On Gl.Location_Id = I.Location_Id

And Gl.Location_Id In ( 1767, 1747,202,1625)

Inner Join Sc1.Condition C

On C.Condtion_Id = Gl.Condition_Id

Where  I.Inactive_Ind = 0

And I.Condition_Id != 325

2. Here I am using location filter in Where clause -

SELECT I.*, Gl * From Sc1.Item I

Inner Join Sc1.Part P

On P.Part_Id = I.Part_Id

Inner Join Sc1.Location Gl

On Gl.Location_Id = I.Location_Id

Inner Join Sc1.Condition C

On C.Condtion_Id = Gl.Condition_Id

Where  I.Inactive_Ind = 0

and I.LOCATION_ID in ( 1767, 1747,202,1625)

And I.Condition_Id != 325

Thanks,

Mark

• ###### 1. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

personally, I find that the ansi syntax is easier to read and manipulate.

This part of the documentation might prove useful:

Joins

• ###### 2. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

AFAIK (though not 100% ure), as per the ANSI syntax : the 'ON' clause holds all the joining conditions whereas the 'WHERE' clause holds the filtering conditions.

Read this for more - http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm

• ###### 3. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

Always analyze plan . If you don't see any diff. it means that there is no difference for ORACLE.

• ###### 4. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

Hi, Mark,

If you have an inner join and a CONNECT BY clause in the same query, then the conditions in the ON clause(s) are applied before the CONNECT BY is done, but the conditions in the WHERE clause are applied afterwards.  Try this query both ways, commenting out one of the "'job != 'PRESIDENT'" conditions:

SELECT  e.ename, e.job

,       d.dname

,       LEVEL   AS lvl

FROM    scott.emp   e

JOIN    scott.dept  d   ON    e.deptno = d.deptno

--                      AND   job     != 'PRESIDENT' -- excludes all 14 rows

WHERE                         job     != 'PRESIDENT' -- excludes 1 row

CONNECT BY  mgr     = PRIOR empno

;

Right now, I can't think of any other inner join  examples where it matters to the system.

It matters to people.  Programmers are used to seeing conditions that reference 2 (or more) tables in ON clauses, and conditions that reference 1 (or fewer) tables in the WHERE clause.  If you put conditions in unexpected places, you only confuse and irritate whoever has to debug and maintain the code in future, and that could be you.

Outer joins are a different story.

• ###### 5. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

Thanks Frank,

But my question is still there.

lets say,

Location_id has both table ( the base table and inner join) , The right approach is to filter on location_id in where clause. but what if I want to filter the location id in Inner join condition.

Will it make any difference ? or what is the right approach to work?

( Please see my query eg if needed)

Thanks,

• ###### 6. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

Hi Ramin,

I have seen sometimes that there is no difference in PLAN but query execution time is different.

• ###### 7. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

MarkCooper wrote:

Hi Ramin,

I have seen sometimes that there is no difference in PLAN but query execution time is different.

When there are so many other factors that can affect execution time (load on the system, caching etc.) and if the plan is the same

why do you assume it is the placement of the predicate that is affecting it?

• ###### 8. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

There should be no difference in the result set for inner joins, and (in principle) no difference in the execution plans. It is only on outer joins that you will see that the results and plans may be difference.  A simplistic way of viewing it (though it's probably not entirely true) is that any FILTER conditions in the ON clause take place before the join, while FILTER conditions in the WHERE clause take place after the join.

Regards

Jonathan Lewis

• ###### 9. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

MarkCooper wrote:

But my question isto understand Inner Inner join condition. Even if you replace the left outer join in your query to Inner join , it will give same records.

Don't confuse rules and implementation. Ansi join clearly separates join conditions from filters. And this is good comparing to Oracle's native syntax. Why? Let's go back to logical design. That's where we show how entities are related. So when we write SQL joining multiple tables using ANSI joins we can specify such relations in ON clause and rest of requirements as filters in WHERE clause. If we follow such rules, reading such SQL gives a better understanding to a person who is not familiar with logical model. And, which is even more important, because of such separation ANSI syntax allows outer joining to multiple tables while Oracle native join syntax doesn't (well, it was relaxed in 12C). So yes, you will get same inner join results, but personally I prefer separating join conditions from filters.

SY.

• ###### 10. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

MarkCooper wrote:

Hi Ramin,

I have seen sometimes that there is no difference in PLAN but query execution time is different.

Query Execution Time depends many factors, if you have same execution plan for two queries  ORACLE will execute them with same rule.

• ###### 11. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

Hi, Mark,

MarkCooper wrote:

Thanks Frank,

But my question is still there.

lets say,

Location_id has both table ( the base table and inner join) , The right approach is to filter on location_id in where clause. but what if I want to filter the location id in Inner join condition.

Will it make any difference ? or what is the right approach to work?

( Please see my query eg if needed)

Thanks,

As most of the replies have already said:There is no difference in results or performance in most cases.

I suggest putting conditions like

Gl.Location_Id In ( 1767, 1747,202,1625)

(which refereence only 1 table) in the WHERE clause, just to reduce confusion for people reading the code.

• ###### 12. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

Frank,

Interesting example - I can't decide whether or not it's a bug in Oracle's internal conversion or whether it is the correct behaviour. In favour of being a bug is the thought that the query ought (?) to give identical plans and results as:

SELECT  ename, job
,       dname
,       LEVEL   AS lvl
FROM (
select e.*, d.*
from
scott.emp   e
JOIN    scott.dept  d   ON    e.deptno = d.deptno
--      AND     job     != 'PRESIDENT'
WHERE   job     != 'PRESIDENT'
)
CONNECT BY  mgr     = PRIOR empno
;

This rewrite, though, gives the same result in both cases (viz: no rows returned)

Regards

Jonathan Lewis

• ###### 13. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

Hi Jonathan,

May be I'm speaking off-track but commenting this : START WITH mgr IS NULL

gives some result.

• ###### 14. Re: Difference-  in conditions (Join and Where Clause)
Currently Being Moderated

Jonathan,

Why do you think it is a bug? I'd say it is correct behavior. According to hierarchical query rules (as Frank already noted) WHERE clause is applied afterwards. So by expanding in-line view we would clearly violate it.

SY.

1 2 Previous Next

#### Legend

• Correct Answers - 10 points