How to get empty result set with a CASE statement?

Question

I cannot get an empty result in CASE statement. The following query returns NULL as the result:

SELECT CASE WHEN ('test' IS NULL) THEN (SELECT 'null_result') ELSE (SELECT 'empty_result' WHERE 1<>1) END;

I want to get empty result (without any rows).

Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — Progman, May 30 '19 at 09:27
That is not my answer, I want to get empty result (without any rows) — Ganj Khani, May 30 '19 at 10:59
Please edit your question to include a more detailed description of what you want to do. If possible provide a [mcve] as described on https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query. Maybe the usage of `CASE` is wrong in this case. — Progman, May 30 '19 at 11:12

score 1 · Accepted Answer · answered May 31 '19 at 04:52

You can filter out NULL values in the outer WHERE clause.

SELECT CASE WHEN ('test' IS NULL) THEN (SELECT 'null_result') ELSE (SELECT 'empty_result' WHERE 1<>1) END
WHERE CASE WHEN ('test' IS NULL) THEN (SELECT 'null_result') ELSE (SELECT 'empty_result' WHERE 1<>1) END IS NOT NULL;

or

SELECT *
FROM (
        SELECT CASE WHEN ('test' IS NULL) THEN (SELECT 'null_result') ELSE (SELECT 'empty_result' WHERE 1<>1) END as result_case
) x
WHERE result_case IS NOT NULL;

How to get empty result set with a CASE statement?

1 Answers1