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.
INNER JOIN
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:
SELECT Customer_name
,Order_number
,Order_total (FORMAT '$$$,$$9.99)
FROM Customer_table AS cust INNER JOIN Order_table AS ord
ON cust.customer_number = ord.customer_number
WHERE Customer_name LIKE 'Billy%
ORDER BY 3 DESC;
Or
SELECT Customer_name
,Order_number
,Order_total (FORMAT '$$$,$$9.99)
FROM Customer_table AS cust INNER JOIN Order_table AS ord
ON cust.customer_number = ord.customer_number
AND Customer_name LIKE
ORDER BY 3 DESC;
2 Rows Returned
Customer_name
|
Order_number
|
Order_total
|
|---|---|---|
Billy's Best Choice
|
123456
|
$12,347.53
|
Billy's Best Choice
|
123512
|
$8,005.91
|
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.
