A Conditional based Join
Here we are going to see how A Conditional based Join can be used using COALESCE()
A scneario may come in such a way that the value matching column in join may deponds on many columns in tables (e.g two tables ).If coulumn1 has value tn column two will be null and vice versa. In scenario like this COALESCE() plays important role..
SELECT ORG.NAME 'user organisation' FROM
INNER JOIN USERIDENTIFIER USERID
ON USERID.USERSID = USERS.ID
AND USERS.ID = @USERID
INNER JOIN ORGANISATION ORG ON
ORG.ID = COALESCE(USERID.ORGANISATIONID , USERS.OWNORGANISATIONID) -- Line 7
The scenario here is that either USERID.ORGANISATIONID has value and USERS.OWNORGANISATIONID has null and vice versa. The Coalesece function plays a remarkable role here (line 7) and as it always returns non null expression. In any case the above query always will return organisation as a result and wont fail to return.