ON and WHERE clause for Joins

Database
Enthusiast

ON and WHERE clause for Joins

Hi, I have a doubt when coding ON and WHERE clause for Joins (Inner Vs Outer).

I think, for an Inner Join its does not make a difference whether we apply the condition with ON cluase or WHERE cluase.

Please refer the 2 queries belows-

sel customer_id,customer_name,order_amount

from Customer C

Inner Join Order O

on C.customer_id = O.customer_id

Where O.order_location = 'India';

The above query can also be writen as-

sel customer_id,customer_name,order_amount

from Customer C

Inner Join Order O

on C.customer_id = O.customer_id

AND O.order_location = 'India';

As per my understaning the above 2 queries will produce the same output, Correct me if i am wrong.

Now for Outer Join, I think coding a search condition with ON cluase or with WHERE cluase makes a huge difference. So please let me know what approach to use in case of Outer Joins.

Please let me know where to refer in TD documentaion to get better understading of this.

Tags (3)
4 REPLIES
Enthusiast

Re: ON and WHERE clause for Joins

Hi, 

I was trying to find some good source of information to check this syntax, finally I found the below document which describes that this syntax can only be used with inner joins, See the line

"converts ANSI‑style inner join syntax to comma‑style syntax if the entire query is based on inner joins"

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1142_111A/ch02...

Khurram
Enthusiast

Re: ON and WHERE clause for Joins

Hi Gaurav,

a) Yes in the case of Inner Joins-->

AND and  WHERE clause are same

b) In the case of Outer Joins

AND works with the JOINs ,however WHERE just does the filtering of the output result set.

Hope this Helps!

cheers!

Nishant

Junior Contributor

Re: ON and WHERE clause for Joins

In the SQL DML manual there's a lot about placing Outer Join conditions in ON vs. WHERE.

This also includes a rewrite of an old article from the Teradata Review magazine as a case study. I remember the original title:  "A lesson on outer joins learned the hard way"  :-)

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/ch02...

Enthusiast

Re: ON and WHERE clause for Joins

Thanks you guys, thanks for quick response.