sel a.t1,b.t1 from emp a left outer join dept b ON
a.id=b.id where b.deptno=10;
sel a.t1,b.t1 from emp a left outer join dept b ON
a.id=b.id and b.deptno=10;
what is the difference on the above 2 queries?




Answers were Sorted based on User's Feedback



sel a.t1,b.t1 from emp a left outer join dept b ON a.id=b.id where b.deptno=10; sel a.t1,b.t1 from..

Answer / tdguy

This is a good question. If we look at the explain plans,we
would be able to understand the difference.
1. In the first query, the condition given in where clause
is applied after the left outer join process on the tables.
This means that it is a plain left outer jon on id column
between the tables and the where condition is applied after
the join process. This can be seen in the explain plan as
"by way of a RowHash match scan with a condition".
2. In the second query, the condition given in and
statement clause is applied along with the left outer join
process on the tables. This means that it is a left outer
jon on id column between the tables with table b with only
one row. This can be seen in the explain plan as
"by way of a RowHash match scan with no residual conditions
".

Is This Answer Correct ?    8 Yes 0 No

sel a.t1,b.t1 from emp a left outer join dept b ON a.id=b.id where b.deptno=10; sel a.t1,b.t1 from..

Answer / rkraju

Hi..,guys.,
joins concepts we are not use where conditions on queries
>>so in that above query we are using where instead of and.
so that is the difference b/w this two queries......,,,,,,,,
>>if any ones knows better result send me..,,

Is This Answer Correct ?    0 Yes 0 No

sel a.t1,b.t1 from emp a left outer join dept b ON a.id=b.id where b.deptno=10; sel a.t1,b.t1 from..

Answer / aarsh dave

The first query will only show the records that are matching
on id and deptno = 10.

The second query will show all the records from table A with
B.t1 as NULL wherever deptno <> 10.

Is This Answer Correct ?    4 Yes 5 No



Post New Answer



More Teradata Interview Questions

What's the difference between timestamp (0) and timestamp (6)?

0 Answers  


Fast Load Script is failed and error tables are available then how do you restart?

3 Answers   TCS,


Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?

9 Answers   IBM,


what is differences between Fastload and Multiload as per DBA aspect ?

1 Answers   Wipro,


While creating table my dba has fallback or no fallback in his ddl. What is that?

0 Answers  






what is a multi statement request in TD? what is the diif b/w V2R5 & TD12? what is the diff b/w MACRO & STORED PROCEDURE?

3 Answers   HCL,


Can we have two time dimensions in a schema(either star or snow flake)? For ex if we want joining date of employee and if we want today's sales with time whether can we have two time dimensions for accommodating above tasks?

0 Answers   IBM,


What are the newly developed features of Teradata?

0 Answers  


What do high confidence, low confidence and no confidence mean in explain plan?

0 Answers  


what is single value and Multivalue ?

1 Answers   IBM, Wipro,


How do you set the session mode parameters in bteq?

0 Answers  


How to Skip or Get first and Last Record from Flat File through MultiLoad and TPUMP Utility?

4 Answers   IBM, Comsys,






Categories