TOP vs LIMIT across databases in SQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding TOP and LIMIT usage

Which statement correctly describes the difference between TOP and LIMIT clauses in SQL?

A <code>TOP</code> and <code>LIMIT</code> are interchangeable and work identically in all SQL databases.

B <code>LIMIT</code> is used only in SQL Server; <code>TOP</code> is used in MySQL and PostgreSQL.

C <code>TOP</code> is used to filter rows based on a condition; <code>LIMIT</code> is used to sort rows.

D <code>TOP</code> is used in SQL Server and returns a specified number of rows; <code>LIMIT</code> is used in MySQL and PostgreSQL for the same purpose.

💡 Hint

Think about which databases you have heard use TOP and which use LIMIT.

TOP is a clause used in SQL Server to limit the number of rows returned. LIMIT is used in MySQL and PostgreSQL for the same purpose. They are not interchangeable across all databases.

❓ query_result

intermediate

Result of TOP vs LIMIT queries

Given a table Employees with 5 rows, what will be the output of these two queries?

-- Query 1 (SQL Server):
SELECT TOP 3 * FROM Employees ORDER BY EmployeeID;

-- Query 2 (MySQL):
SELECT * FROM Employees ORDER BY EmployeeID LIMIT 3;

What is true about their outputs?

A Both queries return the first 3 rows ordered by EmployeeID.

B Query 1 returns 3 rows; Query 2 returns all 5 rows.

C Query 1 returns rows unordered; Query 2 returns 3 rows ordered.

D Both queries return all 5 rows.

💡 Hint

Consider how TOP and LIMIT work with ORDER BY.

Both TOP 3 and LIMIT 3 limit the output to 3 rows after ordering by EmployeeID. So both return the first 3 rows ordered by EmployeeID.

📝 Syntax

advanced

Identify the syntax error in LIMIT usage

Which of the following MySQL queries will cause a syntax error?

SELECT * FROM Orders LIMIT;

A SELECT * FROM Orders LIMIT;

B SELECT * FROM Orders LIMIT 5;

C SELECT * FROM Orders LIMIT 5 OFFSET 10;

D SELECT * FROM Orders LIMIT 10, 5;

💡 Hint

Check if LIMIT requires a number after it.

LIMIT must be followed by a number specifying how many rows to return. LIMIT; without a number is a syntax error.

❓ optimization

advanced

Performance difference between TOP and LIMIT

In a large SQL Server database, which query is generally more efficient to retrieve the first 10 rows?

A) SELECT TOP 10 * FROM LargeTable ORDER BY CreatedDate DESC;
B) SELECT * FROM LargeTable ORDER BY CreatedDate DESC LIMIT 10;

A Query B is more efficient because LIMIT is faster than TOP in SQL Server.

B Query A is efficient because SQL Server optimizes TOP; Query B causes syntax error in SQL Server.

C Both queries are equally efficient and valid in SQL Server.

D Query A causes syntax error; Query B works fine in SQL Server.

💡 Hint

Think about which clause is supported by SQL Server.

SQL Server supports TOP but not LIMIT. Using LIMIT in SQL Server causes syntax error. TOP is optimized for SQL Server.

🔧 Debug

expert

Debugging incorrect use of TOP and LIMIT

A developer writes this query in PostgreSQL:

SELECT TOP 5 * FROM Customers ORDER BY CustomerID;

What error will PostgreSQL raise?

A Error: LIMIT clause missing.

B Unknown column 'TOP'.

C Syntax error near 'TOP'.

D No error; query runs successfully.

💡 Hint

Recall which databases support TOP.

PostgreSQL does not support TOP. Using it causes a syntax error near 'TOP'.