Difference between Top and Limit Keyword in SQL
SqlSql Problem Overview
A quick Question. Suppose I have the following two queries:
SELECT TOP 2 * FROM Persons;
and
SELECT * FROM Persons limit 2;
I want to know the difference between the execution of the above 2 queries?
Basically, I want to know when should I use the limit keyword and when it is appropriate to use the top keyword.
Also, How does the database return results based on the above 2 queries.
Sql Solutions
Solution 1 - Sql
If you are using SQL Server use TOP if you are using MySQL or Postgres use Limit!
AFAIK there is no product that currently supports both. Here's one list of current implementations and here's another (covers more products but in less detail)
Solution 2 - Sql
As stated in my comment for Martin Smith's answer above, there are products that support both, LIMIT and TOP (as you can see here). The difference is that TOP only selects the first n records, but LIMIT allows the definition of an offset to retrieve a specific range of records:
SELECT * FROM ... LIMIT 5 OFFSET 10
This statement selects the first 5 records, after skipping 10 records and this isn't possible with TOP.
The example I posted is only checked against the DBS I linked above. I didn't check a SQL standard, because of a lack of time.
Solution 3 - Sql
TOP & LIMIT both work on amazon Redshift
Solution 4 - Sql
limit works on MySQL and PostgreSQL, top works on SQL Server, rownum works on Oracle.
Solution 5 - Sql
There is no difference. The TOP and LIMIT keywords function identically, and will return the same thing.
Solution 6 - Sql
The difference between top and limit is, top only work with single table where as limit can work with join as well
Solution 7 - Sql
The DISTINCT command and the TOP command can't work together.
The DISTINCT command and the LIMIT command do work together.
So if you are using DISTINCT you must use LIMIT.
Solution 8 - Sql
one big mistake, LIMIT is slowly because select is return full and then database server return only limited data. When it is posible used to TOP.