Home > Articles > Data > SQL

  • Print
  • + Share This

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]
  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.