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
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 |
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 ? | 5 Yes | 6 No |
Why Fload doesn’t support multiset table?
How to copy 1 million records from one DB to another, Which is effective interms of performance?
Hi frnds this question is asked at IBM One query is there when we dont collect stastics on it,exlapin will show us LOW CONFIDENCE,When we collect stastics it shows HIGH CONFIDENCE.WHAT IS THE INTERNAL ARCHITECTURE ACTUALLY GOING ON ?????ASKED ON 23-08-2012 THANKS IN ADVANCE
How do you load Multiple files to a table by using fastload scripts?
Write a single SQL to delete duplicate records from the a single table based on a column value. I need only Unique records at the end of the Query.
What are the updated features of teradata?
What are the different softwares used with their functions in teradata?
Is PK concept available in Teradata. If it is how can we create Primary Key for a table in TD
What is meant by a Channel Driver?
What is teradata and why it is used?
how to improve the query perfoemance in teradata.with example?and how explain this in interview?please forward answer
Explain the teradata primary index mechanics in detail with a diagram.