SQL Case When statement to count rows on multiple tables

Question

I am trying to write a query which checks whether multiple tables have been populated, so I have to check multiple tables, in case that only one of them has 0 records then I have to return 'No' in output, otherwise if all of those tables have more than 0 (i.e 1,2,3... records) it should return 'YES'.

I have already tried using:

SELECT
    CASE WHEN (SELECT COUNT(*) FROM LorikSkema.TABLE1)  > 0 THEN 'YES'
    ELSE 'NO'
    END FROM DUAL;

This seems to work for only one table, but I'm having trouble finding a suitable query for multiple tables based on this logic.

Answer 1

You can use AND to add the checks for the other tables. But I think using EXISTS could be faster than count(*).

SELECT CASE
         WHEN EXISTS (SELECT *
                             FROM table1)
              AND EXISTS (SELECT *
                                 FROM table2)
              ...
              AND EXISTS (SELECT *
                                 FROM tablen) THEN
           'YES'
         ELSE
           'NO'
       END
       FROM dual;