All Forums Database
gkatiyar 19 posts Joined 09/13
21 Dec 2013
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.

Regards, Gaurav Katiyar
M.Saeed Khurram 544 posts Joined 09/12
21 Dec 2013

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.124.008.html
 

Khurram

21 Dec 2013

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

dnoeth 4628 posts Joined 11/04
22 Dec 2013

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.033.060.html

Dieter

gkatiyar 19 posts Joined 09/13
22 Dec 2013

Thanks you guys, thanks for quick response.

Regards,
Gaurav Katiyar

You must sign in to leave a comment.