SQL SELECT TOP, LIMIT and ROWNUM Keywords

 In SQL, the SELECT statement is used to query data from a database. The TOP, LIMIT, and ROWNUM keywords are used to restrict the number of rows returned by a query. They are implemented differently depending on the SQL dialect being used.

1. TOP (SQL Server, MS Access, Sybase)

The TOP keyword is used in SQL Server and some other SQL databases (like MS Access) to limit the number of rows returned by a query. It is typically used in the SELECT clause to specify the number of rows you want to retrieve.

Syntax:

SELECT TOP (number) column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT TOP 5 * FROM Employees;

This query returns the first 5 rows from the Employees table.

Note: You can also use PERCENT with TOP to return a percentage of rows, like so:

SELECT TOP 10 PERCENT * FROM Employees;

2. LIMIT (MySQL, PostgreSQL, SQLite, MariaDB)

The LIMIT keyword is used in MySQL, PostgreSQL, SQLite, and MariaDB to restrict the number of rows returned by the query. It is placed at the end of the query.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
LIMIT number;

Example:

SELECT * FROM Employees
LIMIT 5;

This query will return the first 5 rows from the Employees table.

In MySQL, you can also combine LIMIT with an OFFSET to skip a certain number of rows:

SELECT * FROM Employees
LIMIT 5 OFFSET 10;

This skips the first 10 rows and then returns the next 5 rows.

3. ROWNUM (Oracle)

In Oracle, the ROWNUM keyword is used to limit the number of rows returned in a query. However, it behaves differently from TOP and LIMIT because it is assigned to rows before ORDER BY is applied.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE ROWNUM <= number;

Example:

SELECT * FROM Employees
WHERE ROWNUM <= 5;

This query returns the first 5 rows from the Employees table based on the order they appear in the database (not necessarily the order of a query).

If you need to control the order of rows and then limit the result, you must use a subquery:

SELECT * FROM (
  SELECT * FROM Employees
  ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

Summary of Differences:

• TOP is used in SQL Server and similar databases, specifying a fixed number or percentage of rows.
• LIMIT is used in MySQL, PostgreSQL, SQLite, and MariaDB to restrict the number of rows.
• ROWNUM is used in Oracle to restrict rows, but its behavior is based on the row order rather than a defined limit clause.

Each database system has a unique way to handle limiting results, so it's important to choose the correct syntax based on the SQL dialect you are working with.