4

Is it possible to write a SELECT statement, which returns dataset with zero rows and zero columns?

Dims
  • 47,675
  • 117
  • 331
  • 600
  • 4
    No, a SELECT returns at least 1 column. – jarlh Jul 26 '16 at 09:55
  • 1
    Perhaps this question could be made to seem useful if you explained the `turing-complete` tag. – underscore_d Jul 26 '16 at 10:16
  • I can't imagine any situation where a resultset with no columns could ever be useful for anything: it's hard to imagine what it would even *mean*, except that you've not queried for anything... in which case, what is it that you have done? – eggyal Jul 26 '16 at 11:52
  • 1
    @eggyal It would be one less special case to worry about when autogenerating queries. Otherwise you might be inclined to just unconditionally include a `NULL` column all the time. – binki Dec 29 '17 at 22:29
  • @binki: but such an auto-generated query is necessarily erroneous, so having an error thrown is more correct than burying it with a valid result by virtue of adding superfluous columns. – eggyal Dec 29 '17 at 22:31

5 Answers5

5

A dataset will always have at least 1 column, even if it contains no data.

SELECT NULL;

EDIT:

As pointed out by @eggyal , above syntax will return a null row.

His query select null from dual where false; wont return a row.

Mad Dog Tannen
  • 7,129
  • 5
  • 31
  • 55
  • 2
    This still has 1 row, too—[`select null from dual where false;`](http://sqlfiddle.com/#!9/9eecb7d/70867/0) would still contain a single column, but no rows. – eggyal Jul 26 '16 at 11:50
  • 3
    FYI, `dual` can be used as a dummy table if (as here) no tables need be referenced. – eggyal Jul 26 '16 at 13:15
  • @eggyal `dual` is an elegant solution, more concise than the one I gave, where it works, but it's not universally supported. It works in Oracle and MySQL (at least some versions?) but not in postgres. – cazort Jul 02 '21 at 15:30
  • 1
    @cazort: to be fair, the question was tagged [tag:mysql]… but of course it’s always helpful to have portable answers too! – eggyal Jul 02 '21 at 15:42
1

Not possible in my opinion. You will get at least one column, but no rows.

Select null from yourTable where 1 = 2;
jarlh
  • 42,561
  • 8
  • 45
  • 63
MIftikharK
  • 67
  • 8
1

This works for postgresql:


create table test22 ();
select * from test22;
Hristo Kolev
  • 1,486
  • 1
  • 16
  • 33
0

it's normally used for Creating empty Table from an Existing Table

CREATE TABLE NEW_TABLE_NAME AS
  SELECT * 
  FROM EXISTING_TABLE_NAME
  where 1=2
M.Hassan
  • 10,282
  • 5
  • 65
  • 84
0

No, but it is possible to return a query with no rows. In order to do this without referencing any tables, you can use a subquery:

SELECT NULL FROM (SELECT NULL) AS temp WHERE false;

This query will have one (empty) column, but no rows.

I've used the above construct when there is a query that is different in different cases, followed by a code loop that iterates through the results, and under some conditions you want to make it skip the loop. Replacing the query with the one above is a way of returning empty results and thus skipping the loop without an if block. Because the query contains no table names, that aspect of the code never needs to be changed, and for this reason I prefer it to adding a condition like WHERE false in an existing query.

I prefer this solution to the more concise one referencing dual because that construct is not supported in PostgreSQL; this solution works with any backend that supports subqueries.

cazort
  • 516
  • 6
  • 19