Joining Columns
All the previous hints on WHERE clause construction also apply to joins. Watch out for joins with functions or math on one or both sides. Avoid datatype mismatches, even subtle ones.
In addition, experiment with redundant joins. For example, if you join three tables, two joins are adequate. Adding a third may give the optimizer more options. In this example, backorder is a new table, similar to orderdetail, but it has one more column.
Adaptive Server Anywhere create table backorder ( ordnum int not null, prodnum int not null, unit smallint not null, shipdate date null, backnotedate date not null ) [table created] create unique index bkopix on backorder(ordnum, prodnum) [index created] insert into backorder select ordnum, prodnum, unit, shipdate, '1999-09-14' from orderdetail where shipdate is null [15 rows]
The standard join between three tables looks like this:
Adaptive Server Anywhere select om.ordnum from ordermaster om, orderdetail od, backorder bo where om.ordnum = od.ordnum and od.ordnum = bo.ordnum and om.ordnum = 81
Adding one more loop to complete the circle may help some optimizers by providing more choices. The results of the two queries are the same.
Adaptive Server Anywhere
select om.ordnum
from ordermaster om, orderdetail od, backorder bo
where om.ordnum = od.ordnum
and od.ordnum = bo.ordnum
and bo.ordnum = om.ordnum
and om.ordnum = 81
ordnum
===========
81
81
81
[3 rows]