Wednesday, 25 November 2015

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