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 |
Can we Suggest as how is the process to get a job in teradata in current market??Is it easier???
write lock is compatiable with which type of lock?
Explain the term 'primary key' related to relational database management system?
What is bteq script in teradata?
What are the various etl tools in the market?
Aborted in Phase 1 data Acquistion Incomplete in fastload?
What is the command in bteq to check for session settings ?
What is the use of having index on table?
What are the functions performed by bynet?
How a Referential integrity is handled in Teradata?
What is bteq utility in teradata?
What are the scenarios in which full table scans occurs?