Elgin,
The first way is preferrable especially if you plan to port to SQL Server. The first way will be better if you have individual indexes on each field.
If you have a compound index with CustID + UpsiNo as its expression, then the second variation will be better from Rushmore optimization point of the view.
>Basic question(s):
>
>Two examples: the two fields I'm joining on are of the same data type:
>
>Example 1:
>SELECT v.*
>FROM visual v
>INNER JOIN nameplate n
>ON v.custid = n.custid AND v.upsino = n.upsino
>WHERE v.custid = lcCustid
>ORDER BY v.custid, v.upsino
>
>Example2:
>SELECT v.*
>FROM visual v
>INNER JOIN nameplate n
>ON (v.custid + v.upsino) = (n.custid + n.upsino)
>WHERE v.custid = lcCustid
>ORDER BY v.cusid, v.upsino
>
>Are both join conditions acceptable? Which is more efficient?
>
>Will I get into trouble using either one? Is there a standard?
>
>What if the join condition involves more that two fields of the same data type? Is it still OK to concatenate?
>
>Thank You for your help.
If it's not broken, fix it until it is.
My Blog