Inner join to product join

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Enthusiast

Inner join to product join

Please explain when an inner join can be converted into a product join. A small example will be very much appreciated. Thanks in advance.

1 REPLY 1
Ambassador

Re: Inner join to product join

Hi,

 

Firstly, are you talking about the code being changed (from inner to product join) or the optimizer making this change?

 

If you mean the optimizer then "anytime the optimizer can generate the correct answer (i.e. meet your logic as coded) and it thinks a product join is the fastest way to do the work"

 

If you mean when can the code be changed I think rarely - as you are changing the question that the code is asking, an inner join is different functionality to a product join.

 

Where it can be done is if one side of the join only has one row and every row in the 'large' table needs to be compared to that one row. An example of this is where you have a control row in one table (perhaps containing start and end dates) and you want all rows of the other table where a date column fits between these values.

 

How about this example:

CREATE TABLE t_control
(start_date DATE
,end_date DATE
);

CREATE TABLE t_large
(txn_date DATE
,other_cols...
);

-- Uses INNER JOIN syntax
SELECT t1.*
FROM t_large AS t1
INNER JOIN t_control AS t2
 ON t1.txn_date BETWEEN t2.start_date AND t2.end_date;

-- Use CROSS (product) JOIN syntax SELECT t1.* FROM t_large AS t1 CROSS JOIN t_control AS t2 WHERE t1.txn_date BETWEEN t2.start_date AND t2.end_date;

There may be other situations, but that is the most common that I've seen.

 

HTH

Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com