63

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.

Zoe
  • 27,060
  • 21
  • 118
  • 148
Petruza
  • 11,744
  • 25
  • 84
  • 136
  • It would be a strange schema indeed that had not a single table you could guarantee to exist! – onedaywhen Sep 23 '09 at 09:03
  • 1
    You're right. There are lots of tables that exist, the problem is that once you choose one table, if in the future that table is dropped or renamed, the code you wrote no longer works, and you would be coupling two pieces of the software that have nothing to do with each other yet one change in one of them makes the other stop working. That's called coupling, and coupling in software is generally a bad idea. – Petruza Sep 23 '09 at 12:34
  • 1
    @Petruza: Back in the days when I used Access/Jet, my schema would include a permanent auxiliary table for this purpose named 'RowRowTable'. – onedaywhen Sep 24 '09 at 07:54
  • 1
    PLEASE DON'T ANSWER OR COMMENT. This question is 11 years old, I don't even remember why I asked it but stackoverflow will continue sending me notifications until the end of times. – Petruza Nov 17 '20 at 13:20
  • vandalizing your post will NOT help your case. If you want, you can [disassociate the question from your account](https://meta.stackexchange.com/a/96746), but note that doing so will also remove any and all reputation you've earned or lost on this question – Zoe Nov 19 '20 at 13:09
  • 1
    @zoe so you do see there is a problem that Stackoverflow refuses to solve, right? – Petruza Nov 19 '20 at 13:18
  • There is no problem here. If you don't want notifications at all, disassociate or ignore them. Destroying questions will only backfire against your account – Zoe Nov 19 '20 at 13:19
  • 1
    Yes there is, I just want to not have notifications from age old posts, don't want to disassociate. How hard can it be to be able to turn off notifications for a single post? Most sites have it. – Petruza Nov 19 '20 at 13:21
  • So [post a feature request](//meta.stackoverflow.com) – Zoe Nov 19 '20 at 13:22
  • 1
    @Zoe already did, and others did too, that's why I say SO seems to not care to solve this issue. – Petruza Nov 22 '20 at 14:44

10 Answers10

50

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.

Björn
  • 29,019
  • 9
  • 65
  • 81
  • 1
    Yes. `dual` is born for this purpose. – Rockallite Jul 11 '14 at 03:29
  • Unfortunately, this solution doesn't work in IN subqueries in MySQL under 5.7. `select 1 in (SELECT 1 FROM dual WHERE FALSE);` returns 1. Only solution proposed by @dhruvbird worked for me – ENargit Dec 07 '16 at 12:15
39

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

SELECT NULL LIMIT 0;
Aleh
  • 680
  • 6
  • 7
  • 4
    I like this solution best. Works in MySQL too (v5.5). – noodl Nov 25 '13 at 16:57
  • 1
    No, this doesn't work in a subquery in MySQL. Raise error `This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'` (v5.5). So this answer is not suitable for this question. – Rockallite Jul 11 '14 at 03:20
  • 1
    LIMIT is not standard so obviously it can't work on 'any DB'. Try MSSQL or DB2 for example. – Aleksandr Kravets Oct 05 '17 at 08:45
  • I thought `LIMIT` was fairly standard. This is a neat solution (where it works), and kind-of obvious but for some reason I didn't think of it, so thanks for posting. – Jake May 17 '20 at 23:01
14

T-SQL (MSSQL): 

SELECT Top 0 1;
irrelevantthesis
  • 157
  • 1
  • 2
8

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.

Maksee
  • 2,311
  • 2
  • 24
  • 34
4

This will probably work across all databases.

SELECT * FROM (SELECT NULL AS col0) AS inner0 WHERE col0 IS NOT NULL;
Stefan van den Akker
  • 6,661
  • 7
  • 48
  • 63
dhruvbird
  • 6,061
  • 6
  • 34
  • 39
4
SELECT NULL WHERE FALSE;

it works in postgresql ,mysql, subquery in mysql.

Tim
  • 49
  • 2
3
SELECT TOP 0 * FROM [dbo].[TableName]

This is a reasonable approach to constant scan operator.

tjati
  • 5,761
  • 4
  • 41
  • 56
Nilesh
  • 31
  • 1
  • The OP asked for a query without the need to specify an existing table name, which your answer requires. – Marki555 Jun 19 '15 at 08:18
2

How about this?

SELECT 'MyName' AS EmptyColumn
FROM dual
WHERE 'Me' = 'Funny'
Stefan van den Akker
  • 6,661
  • 7
  • 48
  • 63
Raj More
  • 47,048
  • 33
  • 131
  • 198
  • I've tried something like this, but I guess using Where without From is not legal at least in MySQL, dunno ANSI SQL. – Petruza Sep 21 '09 at 19:28
1
SELECT * FROM (SELECT NULL) WHERE 0
Stefan van den Akker
  • 6,661
  • 7
  • 48
  • 63
aasfalcon
  • 11
  • 1
  • 3
    When providing code that solves the problem, it is best to also give at least a short explanation of how it works so that folks reading won't have to mentally parse it line by line to understand the differences. – Fluffeh Sep 27 '12 at 11:14
  • 6
    on my version of mysql I had to add alias name: SELECT * FROM (SELECT NULL) a WHERE 0; – shark555 Jun 21 '13 at 09:47
0

In PostgreSQL a simple

SELECT;

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

luckydonald
  • 5,976
  • 4
  • 38
  • 58