Select values that SQL JOIN fails on

Question

Currently, to figure out what value a join is failing on in SQL I use the following method

SELECT DISTINCT ACC.Name
     ,ACC.Area
     ,CASE
          WHEN A.Area IS NULL AND N.Name IS NULL
               THEN 'Name and Area do not match'
          WHEN A.Area IS NULL 
               THEN 'Area does not match'
          WHEN N.Name IS NULL
               THEN 'Name is null'
          ELSE 
               'Yes'
          END as IsTopAccount
 FROM Test.dbo.Accounts ACC
 LEFT JOIN Test.dbo.TopAccounts A ON A.Area = ACC.Area
 LEFT JOIN Test.dbo.TopAccounts N ON N.Name = ACC.Name

In the above, an account must be joined to the TopAccounts table on both Area and Name. The two joins are necessary for me to test for each condition in the case statement.

Is it possible to test for each condition in the case statement more efficiently, perhaps with one LEFT JOIN instead of two seperate LEFT JOIN's?

See this sql fiddle

Answer 1

I guess this would work:

SELECT ACC.Name
     ,ACC.Area
     ,CASE
          WHEN T.Area IS NULL AND T.Name IS NULL
               THEN 'Not matching at all'
          WHEN T.Area != ACC.Area
               THEN 'Matching for name only'
          WHEN T.Name != ACC.Name
               THEN 'Matching for area only'
          ELSE 
               'Yes'
          END as IsTopAccount
 FROM A ACC
         LEFT JOIN TA T ON T.Area = ACC.Area 
                                        OR T.Name = ACC.Name

Answer 2

If I understood your question, I think a single FULL OUTER JOIN which gives not matching records as null from both tables, would also give the results. Fiddle-Demo

SELECT A.Name ,A.Area
      ,CASE
          WHEN TA.Area IS NULL AND TA.Name IS NULL
               THEN 'Name and Area do not match'
          WHEN A.Area IS NULL 
               THEN 'Area does not match'
          WHEN TA.Name IS NULL
               THEN 'Name is null'
          ELSE 
               'Yes'
          END as IsTopAccount
 FROM A  FULL OUTER JOIN  TA 
      ON TA.Area = A.Area AND TA.Name = A.Name

You can change the CASE to check null from both tables as required. Here are some good visual examples about joins.

As per comment, Same results with a single LEFT JOIN Fiddle-Demo

SELECT DISTINCT A.Name ,A.Area,
          CASE WHEN TA.Area IS NULL AND TA.Name IS NULL
               THEN 'Name and Area do not match'
          WHEN TA.Area <> A.Area 
               THEN 'Area does not match'
          WHEN TA.Name <> A.Name
               THEN 'Name is does not match'
          ELSE 'Yes' END IsTopAccount
FROM A LEFT JOIN  TA 
      ON A.Area = TA.Area OR A.Name = TA.Name
ORDER BY A.Name