Archives of the TeradataForum

Message Posted: Fri, 20 Feb 2004 @ 09:56:30 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Richard,

you should not get the same results from the two different queries.

Conditions in the 'on-clause' are being applied when the join is performed.

So query one

> sel a.c1,a.c2,a.c3
> from tbl1 a left outer join tbl2 b
> on a.col1 = b.col1 and b.col1 is null;

reads:

Find all records in table b where col1 is null. (As the table has a UPI this will max be one row) Left join table a with the (max) one record you have extracted from table b. You will get all entries for table a back, but they won't have anything for table b. (comparison on null is invalid) Select * from table a really should accomplish the same.

The 2nd query is different though:

> sel a.c1,a.c2,a.c3
> from tbl1 a left outer join tbl2 b
> on a.col1 = b.col1
> where b.col1 is null;

You will do a left join between table a and table b.

All entries from table a will exist before the where clause is applied. The where clause is applied on the results of the join. By looking for b.col1 is null you will only return records from table a where the col1 value does not exist in table b. (Which also indicates that you might not need a left join in this particular case. You could use not exists or not in instead - which is easier to understand...)

Thanks,

Mirjam

	<Prev	Next>		<<First	<Prev	Next>	Last>>