If My Filter Doesn't Find Any Records, Show Me All the Records Instead!

Here's a neat little SQL subquery. The request was: If no records match the specified criteria show me all records instead. I think it was to be used in a form where the user would be able to browse the data. They'd use keyword to subset the data, but the design point was to never not show any records.

This can be done using the Exists keyword. Here's an example:

SELECT Clients.*
FROM Clients
WHERE
  (Clients.ClientCode Between "BC" And "BZ")   
  OR   
  (NOT EXISTS
    (SELECT * FROM Clients WHERE Clients.ClientCode Between "BC" And "BZ")
   ) ;

The EXISTS condition resolves to true when the subquery (inside the brackets starting with SELECT) returns any records. In this case I use NOT to reverse that. So if the condition WHERE Clients.ClientCode Between "BC" And "BZ" doesn't find any records, the criteria staring with NOT EXISTS is true, and so all records match the complete criteria (because of the OR) and the query returns all the rows in the table.