LIMIT vs TOP vs FETCH FIRST syntax in SQL - Hands-On Comparison

LIMIT vs TOP vs FETCH FIRST syntax

📖 Scenario: You are managing a small bookstore database. You want to learn how to get a limited number of book records from your database using different SQL syntaxes.

🎯 Goal: Build SQL queries using LIMIT, TOP, and FETCH FIRST to select a specific number of rows from a books table.

📋 What You'll Learn

Create a table called books with columns id, title, and author

Insert exactly 5 books with given titles and authors

Write a query using LIMIT to select the first 3 books

Write a query using TOP to select the first 2 books

Write a query using FETCH FIRST to select the first 4 books

💡 Why This Matters

🌍 Real World

Limiting query results is common when you want to preview data or paginate results in applications.

💼 Career

Knowing different SQL limit syntaxes helps you write queries compatible with various database systems like MySQL, SQL Server, and Oracle.

1

Create the books table and insert 5 books

Write SQL statements to create a table called books with columns id (integer), title (text), and author (text). Then insert these 5 books exactly: (1, 'The Hobbit', 'J.R.R. Tolkien'), (2, '1984', 'George Orwell'), (3, 'Pride and Prejudice', 'Jane Austen'), (4, 'To Kill a Mockingbird', 'Harper Lee'), and (5, 'The Great Gatsby', 'F. Scott Fitzgerald').

SQL

-- Create the books table and insert 5 books
-- Your code here

Hint

Use CREATE TABLE to make the table and INSERT INTO with multiple rows to add books.

2

Write a query using LIMIT to select the first 3 books

Write a SQL query to select all columns from the books table and use LIMIT 3 to get only the first 3 rows.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Hobbit', 'J.R.R. Tolkien'),
  (2, '1984', 'George Orwell'),
  (3, 'Pride and Prejudice', 'Jane Austen'),
  (4, 'To Kill a Mockingbird', 'Harper Lee'),
  (5, 'The Great Gatsby', 'F. Scott Fitzgerald');

-- Select first 3 books using LIMIT
-- Your code here

Hint

Use SELECT * FROM books LIMIT 3; to get the first 3 rows.

3

Write a query using TOP to select the first 2 books

Write a SQL query to select the top 2 rows from the books table using the TOP keyword. Select all columns.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Hobbit', 'J.R.R. Tolkien'),
  (2, '1984', 'George Orwell'),
  (3, 'Pride and Prejudice', 'Jane Austen'),
  (4, 'To Kill a Mockingbird', 'Harper Lee'),
  (5, 'The Great Gatsby', 'F. Scott Fitzgerald');

SELECT * FROM books LIMIT 3;

-- Select top 2 books using TOP
-- Your code here

Hint

Use SELECT TOP 2 * FROM books; to get the first 2 rows in SQL Server style.

4

Write a query using FETCH FIRST to select the first 4 books

Write a SQL query to select all columns from the books table and use FETCH FIRST 4 ROWS ONLY to get only the first 4 rows.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
  (1, 'The Hobbit', 'J.R.R. Tolkien'),
  (2, '1984', 'George Orwell'),
  (3, 'Pride and Prejudice', 'Jane Austen'),
  (4, 'To Kill a Mockingbird', 'Harper Lee'),
  (5, 'The Great Gatsby', 'F. Scott Fitzgerald');

SELECT * FROM books LIMIT 3;

SELECT TOP 2 * FROM books;

-- Select first 4 books using FETCH FIRST
-- Your code here

Hint

Use SELECT * FROM books FETCH FIRST 4 ROWS ONLY; to get the first 4 rows in standard SQL.

Practice

1. Which SQL clause is used to limit the number of rows returned in a query in MySQL and PostgreSQL?

easy

A. LIMIT

B. TOP

C. FETCH FIRST

D. ROWNUM

Solution

1. Step 1: Identify the database systems mentioned

   MySQL and PostgreSQL use the LIMIT clause to restrict rows.

2. Step 2: Match the clause to the database

   TOP is for SQL Server, FETCH FIRST is for Oracle and others, ROWNUM is Oracle-specific but older style.

3. Final Answer:

   LIMIT -> Option A

4. Quick Check:

   MySQL/PostgreSQL use LIMIT [OK]

Hint: MySQL/PostgreSQL use LIMIT to restrict rows [OK]

Common Mistakes:

• Confusing TOP with LIMIT
• Using FETCH FIRST in MySQL
• Using ROWNUM in PostgreSQL

2. Which of the following is the correct syntax to get the top 5 rows in SQL Server?

easy

A. SELECT * FROM table WHERE ROWNUM <= 5;

B. SELECT * FROM table LIMIT 5;

C. SELECT * FROM table FETCH FIRST 5 ROWS ONLY;

D. SELECT TOP 5 * FROM table;

Solution

1. Step 1: Identify SQL Server syntax for limiting rows

   SQL Server uses TOP n after SELECT to limit rows.

2. Step 2: Check each option

   SELECT * FROM table LIMIT 5; uses LIMIT (MySQL/PostgreSQL), SELECT * FROM table FETCH FIRST 5 ROWS ONLY; uses FETCH FIRST (Oracle), SELECT * FROM table WHERE ROWNUM <= 5; uses ROWNUM (Oracle). Only SELECT TOP 5 * FROM table; is correct for SQL Server.

3. Final Answer:

   SELECT TOP 5 * FROM table; -> Option D

4. Quick Check:

   SQL Server uses TOP [OK]

Hint: TOP goes right after SELECT in SQL Server [OK]

Common Mistakes:

• Using LIMIT in SQL Server
• Placing TOP after FROM
• Using FETCH FIRST in SQL Server

3. Given this query in Oracle:

SELECT * FROM employees FETCH FIRST 3 ROWS ONLY;

What will be the result?

medium

A. Syntax error because FETCH FIRST is not valid in Oracle

B. Returns the first 3 rows from employees

C. Returns all rows because FETCH FIRST is ignored

D. Returns 3 random rows from employees

Solution

1. Step 1: Understand FETCH FIRST in Oracle

   Oracle supports FETCH FIRST n ROWS ONLY to limit rows returned.

2. Step 2: Determine query behavior

   The query returns exactly the first 3 rows from the employees table as ordered by the query (default order if none specified).

3. Final Answer:

   Returns the first 3 rows from employees -> Option B

4. Quick Check:

   Oracle FETCH FIRST limits rows [OK]

Hint: FETCH FIRST limits rows in Oracle [OK]

Common Mistakes:

• Thinking FETCH FIRST causes syntax error in Oracle
• Assuming FETCH FIRST returns random rows
• Believing FETCH FIRST is ignored

4. Identify the error in this SQL Server query:

SELECT * FROM customers FETCH FIRST 10 ROWS ONLY;

medium

A. Should use LIMIT instead of FETCH FIRST

B. Missing ORDER BY clause with FETCH FIRST

C. FETCH FIRST is not supported in SQL Server

D. Syntax is correct, no error

Solution

1. Step 1: Check FETCH FIRST support in SQL Server

   SQL Server does not support FETCH FIRST; it uses TOP instead.

2. Step 2: Analyze other options

   Missing ORDER BY clause with FETCH FIRST is incorrect because FETCH FIRST requires ORDER BY in some databases but SQL Server does not support FETCH FIRST at all. Should use LIMIT instead of FETCH FIRST is wrong because SQL Server uses TOP, not LIMIT. Syntax is correct, no error is false because syntax is invalid.

3. Final Answer:

   FETCH FIRST is not supported in SQL Server -> Option C

4. Quick Check:

   SQL Server uses TOP, not FETCH FIRST [OK]

Hint: SQL Server uses TOP, not FETCH FIRST [OK]

Common Mistakes:

• Using FETCH FIRST in SQL Server
• Confusing LIMIT with FETCH FIRST
• Assuming FETCH FIRST works without ORDER BY

5. You want to write a query that returns the top 10 rows from a table named sales in a database that supports both FETCH FIRST and LIMIT. Which query is correct and why?

Options:
A) SELECT * FROM sales LIMIT 10;
B) SELECT * FROM sales FETCH FIRST 10 ROWS ONLY;
C) SELECT TOP 10 * FROM sales;
D) SELECT * FROM sales WHERE ROWNUM <= 10;

hard

A. Correct if the database supports LIMIT syntax

B. Correct if the database supports FETCH FIRST syntax

C. Correct only in SQL Server

D. Correct only in Oracle with ROWNUM

Solution

1. Step 1: Understand the database supports both FETCH FIRST and LIMIT

   Some modern databases like DB2 or Oracle 12c+ support FETCH FIRST, while others like MySQL use LIMIT.

2. Step 2: Choose the correct syntax for the database

   If the database supports both, FETCH FIRST is the ANSI standard and preferred for portability. LIMIT is not standard SQL. TOP is SQL Server specific, and ROWNUM is Oracle-specific older syntax.

3. Final Answer:

   SELECT * FROM sales FETCH FIRST 10 ROWS ONLY; -> Option A

4. Quick Check:

   FETCH FIRST is ANSI standard for limiting rows [OK]

Hint: Use FETCH FIRST for ANSI standard row limiting [OK]

Common Mistakes:

• Using TOP in non-SQL Server databases
• Assuming LIMIT is standard SQL
• Using ROWNUM outside Oracle