SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause

 In SQL, different databases have different ways to limit the number of rows returned by a query. The syntax used to limit rows may vary depending on the specific SQL database system you're working with. Below are the various ways to achieve this in popular SQL database systems:

1. TOP Clause (SQL Server, MS Access)

The TOP clause is used in SQL Server and MS Access to limit the number of rows returned by a query.

Syntax:

SELECT TOP (N) column_name(s)
FROM table_name;

• N is the number of rows you want to return.

Example:

SELECT TOP 5 * FROM Employees;

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

2. LIMIT Clause (MySQL, PostgreSQL, SQLite)

The LIMIT clause is used in MySQL, PostgreSQL, and SQLite to restrict the number of rows returned.

Syntax:

SELECT column_name(s)
FROM table_name
LIMIT N;

• N is the number of rows to return.

Example:

SELECT * FROM Employees
LIMIT 5;

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

3. FETCH FIRST / FETCH NEXT (Standard SQL, DB2, Oracle 12c+)

The FETCH FIRST (or FETCH NEXT) clause is used in databases that support the SQL:2008 standard, such as DB2, Oracle 12c and later, and some versions of PostgreSQL.

Syntax:

SELECT column_name(s)
FROM table_name
FETCH FIRST N ROWS ONLY;

• N is the number of rows to return.

Example:

SELECT * FROM Employees
FETCH FIRST 5 ROWS ONLY;

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

4. ROWNUM (Oracle)

Oracle uses ROWNUM to limit the number of rows returned in a query. ROWNUM assigns a unique number to each row in the result set.

Syntax:

SELECT column_name(s)
FROM (SELECT column_name(s) FROM table_name) 
WHERE ROWNUM <= N;

• N is the number of rows you want to limit.

Example:

SELECT * FROM Employees
WHERE ROWNUM <= 5;

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

Note: Be cautious when using ROWNUM in Oracle as the order of rows returned is not guaranteed unless you explicitly use an ORDER BY clause.

Example with ORDER BY:

SELECT * FROM Employees
WHERE ROWNUM <= 5
ORDER BY EmployeeID;

Key Differences:

• SQL Server: Uses TOP N.
• MySQL, PostgreSQL, SQLite: Uses LIMIT N.
• Oracle 12c+ and DB2: Uses FETCH FIRST N ROWS ONLY.
• Oracle pre-12c: Uses ROWNUM.

Important Notes:

• In SQL Server and Oracle, you may often see the use of ORDER BY in combination with the row-limiting clauses (like TOP or ROWNUM) to ensure consistent results when querying data.

• In PostgreSQL, LIMIT can also be combined with OFFSET to skip a number of rows, for example:

  SELECT * FROM Employees
  LIMIT 5 OFFSET 10;
  

This will return rows 11 to 15 (i.e., it skips the first 10 rows).

Each database has its own quirks, so it’s important to consult the specific documentation for the database you're working with.