Most of the examples in this book have included all rows from the tables being joined. However, in the world of Teradata with millions of rows being stored in a single table, additional comparisons are probably needed to reduce the number of rows returned. There are two ways to code residual conditions. They are: the use of a compound condition using the ON, or a WHERE clause may be used in the new JOIN. These residual conditions are in addition to the join equality in the ON clause.
Consideration should be given to the type of join when including the WHERE clause. The following paragraphs discuss the operational aspects of mixing an ON with a WHERE for INNER and OUTER JOIN operations.
The WHERE clause works exactly the same when used with the INNER JOIN as it does on all other forms of the SELECT. It eliminates rows at read time based on the condition being checked and any index columns involved in the comparison.
Normally, as fewer rows are read, the faster the SQL will run. It is more efficient because fewer resources such as disk, I/O, cache space, spool space, and CPU are needed. Therefore, whenever possible, it is best to eliminate unneeded rows using a WHERE condition with an INNER JOIN. I like the use of WHERE because all residual conditions are located in one place.
The following samples are the same join that was performed earlier in this chapter. Here, one uses a WHERE clause and the other a compound comparison via the ON:
The output is exactly the same with both coding methods. This can be verified using the EXPLAIN. We recommend using the WHERE clause with an inner join because it consolidates all residual conditions in a single location that is easy to find when changes are needed. Although there are multiple ON comparisons, there is only one WHERE clause.
Like the INNER JOIN, the WHERE clause can also be used with the OUTER JOIN. However, its processing is the opposite of the technique used with an INNER JOIN and other SQL constructs. If you remember, with the INNER JOIN the intent of the WHERE clause was to eliminate rows from one or all tables referenced by the SELECT.
When the WHERE clause is coded with an OUTER JOIN, it is executed last, instead of first. Remember, the OUTER JOIN returns exceptions. The exceptions must be determined using the join (matching and non-matching rows) and therefore rows cannot be eliminated at read time. Instead, they go into the join and into spool. Then, just before the rows are returned to the client, the WHERE checks to see if rows can be eliminated from the spooled join rows.
The following demonstrates the difference when using the same two techniques in the OUTER JOIN. Notice that the results are different:
Notice that only courses with SQL as part of the name are returned. Whereas the next SELECT using the same condition as a compound comparison has a different result:11 Rows Returned
The reason for the difference makes sense after you think about the functionality of the OUTER JOIN. Remember that an OUTER JOIN retains all rows from the outer table, those that match and those that do not match the ON comparison. Therefore, the row shows up, but as a non-matching row instead of as a matching row.
There is one last consideration when using a WHERE clause with an OUTER JOIN. Always use columns from the outer table in the WHERE. The reason: if columns of the inner table are referenced in a WHERE, the optimizer will perform an INNER JOIN and not an OUTER JOIN, as coded. It does this since no rows will be returned except those of the inner table. Therefore, an INNER JOIN is more efficient. The phrase "merge join" can found be in the EXPLAIN output instead of "outer join" to verify this event.
The next SELECT was executed earlier as an inner join and returned 2 rows. Here it has been converted to an outer join. However, the output from the EXPLAIN shows in step 5 that an inner (merge) join will be used because customer name is a column from the inner table (Customer table):
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.