How do I select B if A is not found?-CodePudding

I have a Select-Statement A if it yields no result, I want the result from Select B to be used.

Usually I would use 'OR', but since the statements have completely different TABLES this doesn't seem possible. How do I do that?

CodePudding user response：

Do a UNION ALL. If the first SELECT returns a non-empty result, you are done. Otherwise return the second SELECT's result:

SELECT a FROM table_with_name WHERE a = 1
UNION ALL
SELECT b FROM other_table_with_name
WHERE b = 'xyz'
  AND NOT EXISTS (SELECT a FROM table_with_name WHERE a = 1)

CodePudding user response：

Using a CASE this can be done. Use the case something like this:

SELECT 
  CASE WHEN EXISTS (SELECT a FROM table_with_name WHERE a = 1)
       THEN (SELECT a FROM table_with_name WHERE a = 1)
       ELSE (SELECT b FROM other_table_with_name WHERE b = 'xyz')
  END AS your_result

Should a result be found for the first statement, it will be used. If not, the query in the ELSE part will be executed.