2

Is there a way to express an empty relation in SQL without actually creating an empty table?

For testing purposes, I want to find a way to test something like:

SELECT count(*) FROM (...) my_empty_table;

where ... generates an empty table in-line. (I expect to get 0 as answer)

Is this possible?

I tried unsuccessfully:

SELECT count(*) FROM (VALUES) t;
SELECT count(*) FROM () t;

, which give syntax errors.

and

=> SELECT count(*) FROM (SELECT) t;
 count 
-------
     1
(1 row)

(I'm primarily using SQL, but I'm also curious if there is standard-compliant way of writing this).

2

Your last attempt can be changed to:

select count(*) from ( select where false ) t

Which however is not standard SQL.

The only way I can think of in standard SQL is to use another subselect based on a VALUES clause that returns nothing:

select count(*) 
from ( 
  select * 
  from (values (null) ) x
  where false 
) t

Or use an intersection that is empty:

select count(*) 
from ( 
  select 1 
  intersect
  select 2 
) t

That's partially standard SQL, because a FROM clause is required by the standard.

1

To stay SQL-standard compliant, you could use a table from the information schema. The manual:

The information schema is defined in the SQL standard and can therefore be expected to be portable and remain stable

For simplicity I suggest information_schema.information_schema_catalog_name. The manual:

information_schema_catalog_name is a table that always contains one row and one column containing the name of the current database (current catalog, in SQL terminology).

Bold emphasis mine. So:

SELECT count(*)
FROM  (SELECT FROM information_schema.information_schema_catalog_name WHERE false) my_empty_table;

The SELECT list can be empty since we are not interested in actual columns anyway.

Or:

(SELECT FROM information_schema.information_schema_catalog_name LIMIT 0) 

But LIMIT is not standard syntax, which is:

(SELECT FROM information_schema.information_schema_catalog_name FETCH FIRST 0 ROWS ONLY)

(Needlessly verbose, if you ask me.)

Fully standard SQL, proof of concept, but of limited usefulness. None of the existing RDBMS comply to the standard fully.

Notably, Oracle does not implement the information schema. And more popular RDBMS support the non-standard LIMIT clause than the standard FETCH FIRST. Overview in Wikipedia.

The shortest and cheapest non-standard syntax for Postgres is a_horse's first example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.