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.