Checking if a table has any rows, and return string if it doesn't

Question

If there is a table, test, how can we return "No data" if the table has only title but no other rows?

IF (SELECT 1 FROM test) < 1 THEN (SELECT ' No data');
    
END IF;

The following code keeps returning SQL ERROR(1064): You have syntax errors in your SQL query. However, I am using the syntax specified in the manual.

Answer 1

I think the best way to check if a table has records on it is to execute an SQL Statement that return '0' when no records are found and 'n' when records are found, being 'n' the number of records existing on table... And you can simply do it like this:

SELECT COUNT(1) AS TOTAL_ROWS FROM test;

... being the result saved on a column named as TOTAL_ROWS

Though, regarding your request, it's possible to get that result through the following query:

SELECT IF(TOTAL_ROWS = 0, 'No data', 'There\'s data on \'test\' table') AS `EXISTS DATA?` 
FROM (
   SELECT COUNT(1) AS TOTAL_ROWS FROM test
)t
;

As you can see, the result is obtained through a couple of nested queries:

• The inner query SELECT COUNT(1) AS TOTAL_ROWS FROM test counts the total of existing records on test TABLE and save the result on TOTAL_ROWS
• The outer query checks what value was saved on TOTAL_ROWS and presents the desired label, depending on the result.