Is there a SQL query that will always return zero results? [closed]

Question

If there is a MySQL/PostgreSQL/Oracle-specific solution, I'm curious about them all.

Answer 1

Depending on the DBMS, one or more of the following will work:

• SELECT NULL LIMIT 0 (PostgreSQL and MySQL syntax) / SELECT TOP 0 1 (MS SQL Server syntax)
• SELECT NULL WHERE FALSE (DBMS with a boolean type, e.g. PostgreSQL) SELECT NULL WHERE 1=0 (most DBMSes)

For Oracle, these will need to be of the form SELECT NULL FROM DUAL, I believe, as you can't have SELECT without a FROM clause of some sort; not sure which versions of the LIMIT / TOP and WHERE it will accept.

A more elaborate option is to create a (temporary) table and not insert any rows into it, which can give you any number of columns, which will have types associated even though they contain no values:

-- PostgreSQL
CREATE TEMP TABLE dummy ( a Int, b VarChar(42) );
SELECT * FROM dummy;

-- MS SQL Server
CREATE TABLE #DUMMY ( a Int, b VarChar(42) );
SELECT * FROM #DUMMY;

In PostgreSQL, you can even create a table with no columns, allowing you to have a result set with zero rows and zero columns:

CREATE TEMP TABLE empty ();
SELECT * FROM empty;

Another possibility is if the DBMS has set-returning functions, they may be able to return an empty set. For instance, again in PostgreSQL as it's what I know best, you can give an invalid range to generate_series():

SELECT * FROM generate_series(0,-1);

Answer 2

At least in MySQL/PostgreSQL:

SELECT 1 LIMIT 0