Why is "LIMIT 0" even allowed in MySQL SELECT statements?

Question

Limit 0, 1000 returns the first 1,000 results, but LIMIT 0 returns 0 results.

That's not very intuitive imho. For example, dumb old me thought that removing the 1000 would remove the upper limit to the SELECT query, thus returning all of the results.

Why would anybody even want to query MySQL for 0 results?

Answer 1

From the MySQL documentation

LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. When using one of the MySQL APIs, it can also be employed for obtaining the types of the result columns.

Answer 2

limit 0 can be used to get the same columns types of other tables

create table newtable
select col1 from table1 limit 0;

That way, a hard-coded description of the columns types for newtable is not needed, ensuring that the columns types will still match even if a change occurs in the description of table1 before creating newtable

It also works with a more complete statement, involving indexes, engine, multiple tables, etc

create table newtable (primary key (col1)) engine=memory
select col1,col2,col3 from table1,table2 limit 0;