maxm maxm - 1 year ago 63
MySQL Question

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

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

Answer Source

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) );

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

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download