The Power of Coalesce

Cascading Style Sheets (CSS) are an HTML technique for formatting the display of html documents in a standard fashion. They are called Cascading because there is a hierarchy to how the styles are defined and implemented. Styles implemented in the HTML document itself override other styles. The concept I want to communicate here is that the same HTML tag may be defined multiple times on one or more style sheets. The HTML renderer knows how to follow the hierarchy of tag definitions contained in all of the stylesheets, and determine the ultimate winner used for the final display.

One common database pattern uses this same concept of a hierarchical enforcement of rules, using tables with outer joins or nulls, and the COALESCE SQL operator.

Lets define an example scenario where we have users, departments and a company. Like a CSS, we want to allow the definition in our database for a preference, to be stored in a preference table. Users can have a preference. Departments can have the same preference. The company may set the same preference. Perhaps we want to also have a default value for the same preference. So, now for the same preference we can set the value at four different levels. The order of precedence is, User, Department, Company, Default. You want to use the first preference that has been set, in the order of precedence. If the users has none, then use the department. If the department has none, then use the company. If the company has none then sue the default.

In SQL this could look something like the following query. This query returns all users with all preferences. The preference is determined by which one of the different sources of a preference are not null in the COALESCE statement. It is determined individually for each user.

SELECT
           Users.UserId,
           COALESCE(USERP.Preference, Department.Preference, 
           Company.Preference, Preference.Preference) AS Preference
FROM       Preference
CROSS JOIN Users
LEFT JOIN  UserPreference USERP 
           ON USERP.PreferenceID = User.PreferenceId
           AND Users.UserId = USERP.UserId
LEFT JOIN  DepartmentPreference Department 
           ON Preference.PreferenceId = Department.PreferenceId
           AND Users.DepartmentId = Department.DepartmentId
LEFT JOIN  CompanyPreference Company 
           ON Preference.PreferenceId = Company.PreferenceId
           AND Users.CompanyId = Company.CompanyId

If you didn’t catch it, this is one use of a Cartesian product by using a CROSS JOIN. This join associates all users with all preferences. So, I will get a record for every user, with every possible preference. If you were wondering a few days ago where I was saying a Cartesian product may be useful, here is a realistic example.

Cheers,

Ben