T-SQL, Create a Null Flag when Joining
A normalized database will usually include "lookup tables" or "list tables". For example, a table of state names...pay grades...football team names. You get the idea. A typical lookup table is small and its data does not often change. A lookup table can be associated with zero to many records in other tables. Furthermore, if referential integrity is enforced, any record in a lookup table cannot be removed while an association exists in another table. So -- when retrieving results from a lookup table, I often find it useful to include a flag indicating whether a flag has such an association. Take this diagram for example:
In this example, the database contains a list of horses. Horse colors are defined in the Colors table and referenced in the Horses table by the foreign key column ColorId. We are all familiar with a simple SELECT to retrieve one or columns from the Colors table. But what if I want to know which record can be deleted? This information could be very handy if you are displaying a "Delete" button next to each name as you display it. You do not want to show a delete button for any Color record than cannot be removed. So as your web page iterates the result set it needs some sort of indicator to know when to hide the delete button. There is a very simple, elegant way to do this.
#1. We know that a LEFT OUTER JOIN is inclusive and will return a null for any rows that do not have a corresponding value in the joined table. #2. The ISNULL function evaluates a returned column to see if it's null and if so replaces null with a specified value (in our case "0"). Solution. All that needs to be done then is to make of note of this evaluation in the result set by way of a pseudo column "IsAssociated".