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

Need a 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

Need a 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

Need a 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

Need a hint?

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