What's the preferred way to return an empty table in SQL?

Question

I know I can return an empty table using the following query :

select * from tbFoo where 1=2

but that code doesn't look nice to me.

Is there a 'standard' way of doing this?

If you're wondering why I want to do such a strange thing, it's because I can't name the datatables I return from a stored procedure, so I need empty placeholders.

Answer 1

Most of the time I see 1=0 but yes thats pretty much the standard approach when you really have to. Although really having to is rare.

Answer 2

select * from tbFoo where 1=2 is what I've used in the past. Not sure if its a "standard" way but I've used it many times and seen it used by others.

Answer 3

What you really need is information_schema, using it will allow you to find out the definition of a table.

You don't mention which database you are using, so here is a link about information_schema Support in MySQL, PostgreSQL (and MSSQL, Oracle, Etc)

An example from the site;

SELECT table_name, column_name, is_nullable, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'employees'

In your case, all you need are the column names;

SELECT column_name 
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'employees'

Answer 4

Having just run both:

SELECT TOP 0 * FROM Table
and
SELECT * FROM Table WHERE 1=0

They produce exactly the same execution plan.