CODEWITHSUNDEEP
sqlmysqlstored-proceduresnullresultset
Petruza
Petruza

Reputation: 12276

How to select an empty result set?

I'm using a stored procedure in MySQL, with a CASE statement.

In the ELSE clause of the CASE ( equivalent to default: ) I want to select and return an empty result set, thus avoiding to throw an SQL error by not handling the ELSE case, and instead return an empty result set as if a regular query would have returned no rows.

So far I've managed to do so using something like:
Select NULL From users Where False

But I have to name an existing table, like 'users' in this example. It works, but I would prefer a way that doesn't break if eventually the table name used is renamed or dropped.

I've tried Select NULL Where False but it doesn't work.

Using Select NULL does not return an empty set, but one row with a column named NULL and with a NULL value.

Upvotes: 67

Views: 72546

Answers (10)

Tim
Tim

Reputation: 69

SELECT NULL WHERE FALSE;

it works in postgresql ,mysql, subquery in mysql.

Upvotes: 6

Aleh
Aleh

Reputation: 700

This should work on most DBs, tested on Postgres and Netezza:

SELECT NULL LIMIT 0;

Upvotes: 41

aasfalcon
aasfalcon

Reputation: 11

SELECT * FROM (SELECT NULL) WHERE 0

Upvotes: 1

Raj More
Raj More

Reputation: 48024

How about this?

SELECT 'MyName' AS EmptyColumn
FROM dual
WHERE 'Me' = 'Funny'

Upvotes: 2

dhruvbird
dhruvbird

Reputation: 6189

This will probably work across all databases.

SELECT * FROM (SELECT NULL AS col0) AS inner0 WHERE col0 IS NOT NULL;

Upvotes: 4

luckydonald
luckydonald

Reputation: 6886

In PostgreSQL a simple

SELECT;

works. You won't even get any columns labeled 'unknown'.
Note however, it still says 1 row retrieved.

Upvotes: 0

Nilesh
Nilesh

Reputation: 31

SELECT TOP 0 * FROM [dbo].[TableName]

This is a reasonable approach to constant scan operator.

Upvotes: 3

irrelevantthesis
irrelevantthesis

Reputation: 177

T-SQL (MSSQL): 

SELECT Top 0 1;

Upvotes: 16

Maksee
Maksee

Reputation: 2401

How about 

 SELECT * FROM (SELECT 1) AS TBL WHERE 2=3

Checked in myphp, and it also works in sqlite and probably in any other db engine.

Upvotes: 8

Björn
Björn

Reputation: 29411

There's a dummy-table in MySQL called 'dual', which you should be able to use.

select
    1
from
    dual
where
    false

This will always give you an empty result.

Upvotes: 52

Related Questions