I'm in need of a sanity check on a sql query that will return a value if it exists in the table or return a default value if it doesn't

Question

What I'm trying to do is return a mailbox location for a select few users and a catch all mailbox location for everyone else. In postfix you can pass it a query in the of the form

SELECT mailbox FROM virtual_mailboxes WHERE email = '%s'

and it will replace %s with the email user it's looking for. I have a table that contains a set of users that need their own mailbox(root, support, postmaster) and I want to be able to create a query that returns the configured mailbox or 'all/'. For example if the username is 'support' then I want it to pull the mailbox column for username 'support'. If the username is 'anybody' which doesn't match an rows in the table I want it to return 'all/'.

Here is the query I came up with.
SELECT
CASE 
  WHEN v2.username IS NOT NULL THEN v2.mailbox
  ELSE 'all/'
END AS mailbox2
FROM virtual_mailboxes v1
     LEFT JOIN virtual_mailboxes v2 ON v2.username = SUBSTRING_INDEX('[email protected]', '@', 1)
LIMIT 1

It works but I feel like it's very wrong to do it that way and I'm curious if there is a better way.

One other thing, postfix will only replace the first occurrence of %s. Thanks for any help.

Answer 1

SELECT coalesce( 
    (SELECT mailbox FROM virtual_mailboxes WHERE email = '%s'), 
    'all/'
);