SQL query with limit 0

sql

19,778

Solution 1

I think it is for parsing query only.

In simple approach The query execution steps are :

Parsing query
Make execution plan(including select best index to use).
Fetch data from disk.

Solution 2

As per MySQL docs:
LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. It can also be employed to obtain the types of the result columns if you are using a MySQL API that makes result set metadata available.

Solution 3

I finally found some knowledgeable thing,when I have created a demo for you,use below mentioned fiddle to check the execution plan when comparing with limit 0 and 1.

http://sqlfiddle.com/#!9/82a8f0/5

What I have seen is that,in execution if we use Limit 0 than its not taking consideration of Table.check my sqlfiddle mentioned above.

Note : I have created only simple table to check Execution Plan difference in demo.

19,778

Lin Ma

Updated on November 05, 2022

Comments

Lin Ma over 1 year
I see people write SQL query, for example,
```
select count(*) 
from xxx_table 
where yyy='abc' 
limit 0
```
wondering what means limit 0 here? Referred some documents and discussions and still feel confused.
- Sagar R almost 8 years
  
  you should probably visit this page, dba.stackexchange.com/questions/105850/…
- Lin Ma almost 8 years
  
  @SagarR, thanks for the reply and vote up. I find similar stuff when doing the research. And the specific question is for count(*) used with limit 0, I think count (*) must return 1 row (i.e. the count of records under some conditions, should be exact one record/row returned). So, I think adding limit 0 or not should not be a differences -- since limit 0 means first row (which is the only row returned by count(*)). Any comments are appreciated. :)
- a_horse_with_no_name almost 8 years
  
  Which DBMS are you using?
- Lin Ma almost 8 years
  
  @a_horse_with_no_name, most of the time MySQL, but this is a general question, your advice is highly appreciated. Vote up for your ask. :)
Lin Ma almost 8 years

Thanks Artur, I find similar stuff when doing the research. Confused what means empty set here? I think count (*) must return something (i.e. the count of records under some conditions, should be at least one record). Any comments are appreciated. :)
Artur Opalinski almost 8 years

@Lin Ma: 'empty set' means you do not get ANY rows from such query. SELECT COUNT.. produces a row (as many other SELECT statements do) - but you will not see it.
hansvb almost 8 years

So all this query basically does is check if the tables and columns mentioned exist.
Sagar R almost 8 years

@ArturOpalinski : short and simple but very useful answer.
Lin Ma almost 8 years

@ArturOpalinski, thanks for the reply, vote up. 1. For the row produced, do you mean the count number? 2. What do you mean not see it? A bit more details are appreciated.
Lin Ma almost 8 years

@Thilo, vote up, and confused and a bit more details are appreciated. Why it just check table/column exist? You mean underlying it does not do anything to count the real data and get a count? Thanks.
Lin Ma almost 8 years

@SagarR, if no results are returned, what is the purpose of this query?
hansvb almost 8 years

Yes, I don't think it will actually run the query (i.e. it will not do any counting). But it won't return any data even if it does the counting.
hansvb almost 8 years

As for the purpose, you have to ask the people who wrote the query. It could be a sort of "database ping". Or a check if the column exists.
Sagar R almost 8 years

@LinMa : if I say about MS Sql Server, There is "TOP 0" similar to LIMIT 0,what I found is there is execution change while we are using TOP 0.It doesn't scan through PK and clustered index,so you can save that time.
Lin Ma almost 8 years

@Thilo, vote up for your both replies and wondering if the purpose is to check column exists, they can use describe table statement, correct? Any special of limit 0?
Lin Ma almost 8 years

@SagarR, vote up for your reply. You mentioned behavior of top 0, the question is limit 0, how is about behavior of limit 0? Thanks.
Lin Ma almost 8 years

Thanks Msf, vote up for your reply and wondering what is the purpose of parse query?
Mostafa Vatanpour almost 8 years

I think Parsing query consists of check for syntax error, command type and checking table and column names with meta data information
Lin Ma almost 8 years

Thanks Sagar R, vote up for your nice sample. So, you want to prove select count(*) from abc limit 0 does not return or doing anything? Or you want to prove something else?
Lin Ma almost 8 years

Actually my question is, in what scenario, should we use limit 0. Your advice is appreciated. :)
Lin Ma almost 8 years

Thanks Msf, vote up. Then why not limit 1 to test with real data?
Lin Ma almost 8 years

And I am using MySQL Workbench and I think workbench could check basic column type/table for us, and not sure the special value of limit 0? Your advice is appreciated. :)
Mostafa Vatanpour almost 8 years

Limit 1 when using count(*) will fetch all records and with normal columns will fully execute query and fetch only one record.
Mostafa Vatanpour almost 8 years

Mysql Workbench may use low level functions and APIs to fetch column type/table but we use normal SQL commands to do things.
Lin Ma almost 8 years

Thanks Ragesh, vote up for your reply. My specific question is what means limit 0? I understand what means limit. :)
Lin Ma almost 8 years

Thanks Msf, what do you expect when running limit 0? There might be errors like invalid table or column name? And there are no actual return values from the query itself (even if the query has no issues), correct?
Lin Ma almost 8 years

Thanks Msf. Vote up your reply and mark your reply as answer. Have a good night. :)
GrandOpener over 3 years

Tangential, but it's also very helpful that LIMIT 0 merely does what it promises to do when you are programmatically building queries. If, for example, it were an error, systems that build queries based on other inputs would have to specifically guard against the case where 0 records are requested. Implemented as it is, things "just work."