2

I've just tried running SELECT * FROM tbl LIMIT 0, which unsurprisingly, returns 0 results.

I could see an argument for making it be an error, because it will never return any result, but then again it isn't invalid like LIMIT 'HelloWorld'.

Are there any actual real-world use cases for LIMIT 0, or is it just allowed because there's no real reason to not to allow it?

  • 1
    I never thought about this - interesting question. Can a LIMIT be a parameter (which could be 0) - or a subselect i.e. SELECT COUNT(foo) FROM bar?`+1 for interesting question. – Vérace Mar 23 '16 at 16:15
  • Tested - it doesn't appear to allow either parameter or select - bug? – Vérace Mar 23 '16 at 16:21
  • Yeah it seemed weird, I can't possibly think of a use case for LIMIT 0, but it might just be the case of "Because LIMIT 0 doesn't break anything" so no point making it illegal. – TMH Mar 23 '16 at 16:27
  • 4
    Such a query (as well as many others , e.g. WHERE 1=0 ) will return column metadata. – a1ex07 Mar 23 '16 at 16:30
  • 2
    Ahh, so it's a way to get things like the column names without bringing back unneeded data? – TMH Mar 23 '16 at 16:34
2

Perhaps a matter of convenience

I once answered a post Table JOIN and CREATE new table extremely slow, mentioning how CREATE TABLE AS SELECT will create a table and then perform INSERT INTO SELECT to populate the table

There are ways to use LIMIT 0 to create a temp table without indexes

CREATE TABLE mytable SELECT * FROM othertable LIMIT 0;

In another post, MySQL Locks while CREATE TABLE AS SELECT, I mentioned under OPTION #2 using WHERE 1=2 as a way to create a blank temp table with no indexes

CREATE TABLE 1_temp_foo SELECT * FROM crm_companies WHERE 1=2;
ALTER TABLE 1_temp_foo ENGINE=InnoDB;

In terms of end result, using LIMIT 0 and WHERE 1=2 and even WHERE 0 are synonymous.

I agree with your assumption that it was allowed for no particular reason (because there's no real reason to not allow it, as you say), but it sure comes in handy for such occasions.

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.