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.

Are you trying to populate a DataTable from a Stored Procedure? — REA_ANDREW, Feb 26 '09 at 10:54
@REA_ANDREW : the fact that I intend to use this empty data as a return resultset from a stored procedure doesn't mean I don't know how to handle a resultset in c#. I suggest you delete your off-topic answer:) — Brann, Feb 26 '09 at 11:19
I have deleted. :) I misunderstood both your question and your response! — REA_ANDREW, Feb 26 '09 at 11:21
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. — schooner, Feb 26 '09 at 11:06

GateKiller · Accepted Answer · 2009-02-26T11:53:49.790

28

Having just run both:

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

They produce exactly the same execution plan.

edited Feb 26 '09 at 11:53

answered Feb 26 '09 at 11:34

GateKiller

score 1 · Answer 2 · answered Feb 26 '09 at 10:55

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.

answered Feb 26 '09 at 10:55

AnthonyWJones

Ok. Are there any performances implication depending on the table I choose for my select? I guess not... – Brann Feb 26 '09 at 10:57
I doubt there is any big performance issue, the optimizer is going to see that there is no need to read anything from the DB, take a look at the execution plan. – AnthonyWJones Feb 26 '09 at 12:03

score 1 · Answer 3 · answered Feb 26 '09 at 11:27

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'

3 Answers3