SQL 中上限关键字与限制关键字的区别

一个快速问题: 假设我有以下两个问题:

SELECT TOP 2 * FROM Persons;

还有

SELECT * FROM Persons limit 2;

我想知道执行上述两个查询之间的区别？ 基本上，我想知道什么时候应该使用 limit关键字，什么时候适合使用 top关键字。 此外，数据库如何基于上述2个查询返回结果。

71711 次浏览
小开
最佳答案

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)

小开

limit works on MySQL and PostgreSQL, top works on SQL Server, rownum works on Oracle.

小开

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.

小开

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 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.

小开

There is no difference. The TOP and LIMIT keywords function identically, and will return the same thing.

小开

TOP & LIMIT both work on amazon Redshift

小开

The difference between top and limit is, top only work with single table where as limit can work with join as well

小开

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.


提交答案