Easy ANSI Join Conversion
Like many MS SQL users way back when I learned the standard syntax for select
statements:
SELECT customer.custId , customer.custName , address.addrType , address.addrAddress1 , address.addrSddress2 , csz.cszCity , csz.cszState , csz.cszZip , order.ordOrderNo , order.ordOrderDate , order.partId , part.partDesc FROM customer , address , csz , order , part WHERE address.custId = customer.custId AND csz.cszId = addr.cszId AND order.custId =* customer.custId AND part.partId = order.partId
Which is fairly standard stuff except for perhaps the join operator between order.custId and customer.custId.
I felt reasonalbly comfortable with this type of syntax for several years until I took a job which led me eventually into the depths of Powerbuilder and MS SQLServer. This, coupled with working with someone who really knew SQL well, brought me on board with the ANSI JOIN syntax. Since then I have never used the NON-ANSI syntax.
So the pervious query is re-written as:
SELECT customer.custId , customer.custName , address.addrType , address.addrAddress1 , address.addrSddress2 , csz.cszCity , csz.cszState , csz.cszZip , order.ordOrderNo , order.ordOrderDate , order.partId , part.partDesc FROM customer JOIN address ON customer.custId = address.custId JOIN csz ON address.cszId = csz.cszId LEFT JOIN order on customer.custId = order.custId JOIN part on order.partId = part.partId
As a reminder on how the joins change the results:
* JOIN: Return rows when there is at least one match in both tables
* LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
* RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
* FULL JOIN: Return rows when there is a match in one of the tables
I tend to arrange my SQL so I always use LEFT JOIN instead of RIGHT JOIN as a matter of personal preference. Using this type of syntax also makes it easier to identify the ‘business rules’ of any query since they are generally placed in the WHERE clause. I also like to list my output fields with the comma preceding the value; this makes it easier to debug the statement by removing any field by placing a comment indicator (‘–‘) at the start of the line (except the first field).
Converting from the ‘old’ way to the ‘new’ way really is not that difficult. Generally you take the first table name from the existing list of tables in the old sql (what I call the ‘main’ table) and join in all the rest of the tables. Each of the subsequent tables in the original list is JOINed to the preceding table list usually with the exact same syntax as in the existing WHERE clause. The only ‘tricky’ part is if there are more than one join condition for a table (say in the case of a multi part key). In these cases the multiple JOIN conditions are separated by an ‘AND’. Example:
... FROM customer JOIN address ON customer.custId = address.custId AND customer.companyId = address.companyId
You may also use the JOIN to limit results to specified conditions such as:
... FROM customer LEFT JOIN address ON customer.custId = address.custId AND address.activeFlag = 'Y'
Once you have used this syntax for a while you will appreciate how easier to understand multi table selects are and your conversion will be complete. You might be tempted to update an existing statement by adding a JOIN and leaving the ‘old’ syntax intact; don’t do it. Mixing the syntax can lead to unexpected results. Do the right thing – convert it all to ANSI standard.
Updated March 2012
