Remove OUTER JOINS

This can easier said than done and depends on how much influence you have in changing table content. One solution is to remove OUTER JOINS by placing placeholder rows in both tables. Say you have the following tables with an OUTER JOIN defined to ensure all data is returned:

CUSTOMER_ID CUSTOMER_NAME 1 John Doe 2 Mary Jane 3 Peter Pan 4 Joe Soap

CUSTOMER_ID SALES_PERSON NULL New bee Smith 2 Oldie Jones 1 Another Oldie NULL Greenhorn

The solution is to add a placeholder row in the customer table and update all NULL values in the sales table to the placeholder key.

CUSTOMER_ID CUSTOMER_NAME 0 NO CUSTOMER 1 John Doe 2 Mary Jane 3 Peter Pan 4 Joe Soap

CUSTOMER_ID SALES_PERSON 0 Newbee Smith 2 Oldie Jones 1 Another Oldie 0 Greenhorn

Not only have you removed the need for an OUTER JOIN you have also standardised how sales people with no customers are represented. Other developers will not have to write statements such as ISNULL(customer_id, “No customer yet”).

Remove Calculated Fields in JOIN and WHEREClauses

This is another one of those that may at times be easier said than done depending on your permissions to make changes to the schema. This can be done by creating a field with the calculated values used in the join on the table. Given the following SQL statement:

FROM sales a

JOIN budget b ON    ((year(a.sale_date)* 100) + month(a.sale_date)) = b.budget_year_month

Performance can be improved by adding a column with the year and month in the sales table. The updated SQL statement would be as follows:

SELECT * FROM PRODUCTSFROM sales a

JOIN budget b ON    a.sale_year_month = b.budget_year_month