SELECT a row even if one does not exist


Yes! Yet another SQL post (sigh).

It is occasionally desirable to perform a SELECT that should show a row even when the primary table does not have a row that meets the selection criteria.

For example

SELECT   CASE
           WHEN c.name IS NULL THEN 'No suspects found'
           ELSE 'Suspect:'
         END
       , c.Name
       , c.Height
       , c.EyeColour
       , c.ModusOperandi
  FROM Criminals c
       LEFT OUTER JOIN prisoners p
                    ON p.id = c.id
 WHERE c.ModusOperandi LIKE '%' + @crimeFact + '%'
       AND p.prison IS NULL;

should show No suspects found when @crimeFact = 'left selfie at scene'. But instead it displays no row at all.

To get the desired result, join each row to a row that will always exist – one you create yourself:

SELECT   CASE
           WHEN c.name IS NULL THEN 'No suspects found'
           ELSE 'Suspect:'
         END
       , c.Name
       , c.Height
       , c.EyeColour
       , c.ModusOperandi
  FROM Criminals c
       LEFT OUTER JOIN prisoners p
                    ON p.id = c.id
       RIGHT JOIN (SELECT 1 constant) AS dummy 
               ON p.ModusOperandi LIKE '%' + @crimeFact + '%'
                  AND p.prison IS NULL;

Note: the terms of the WHERE clause are moved into the ON clause for the dummy record.
Our search will now properly return a row with No suspects found in the first column and NULL in the other 4 columns.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s