TOP vs LIMIT across databases in SQL - Visual Side-by-Side Comparison

Concept Flow - TOP vs LIMIT across databases

Write SELECT query

↓

Choose database

↓

If SQL Server or MS Access

↓

Use TOP N

↓

If MySQL, PostgreSQL, SQLite

↓

Use LIMIT N

↓

Execute query

↓

Get top N rows as result

The flow shows how to write a query to get top N rows, choosing TOP for SQL Server and LIMIT for MySQL/PostgreSQL/SQLite.

Execution Sample

SQL

SELECT TOP 3 * FROM Employees ORDER BY Salary DESC;

SELECT * FROM Employees ORDER BY Salary DESC LIMIT 3;

Two queries that get the top 3 highest paid employees, one using TOP (SQL Server) and one using LIMIT (MySQL/PostgreSQL).

Execution Table

Step	Query Type	Database	Syntax Used	Action	Result Rows
1	Select top N rows	SQL Server	TOP 3	Select top 3 rows ordered by Salary descending	3 rows with highest Salary
2	Select top N rows	MySQL/PostgreSQL	LIMIT 3	Select rows ordered by Salary descending, limit to 3	3 rows with highest Salary
3	Select top N rows	SQLite	LIMIT 3	Select rows ordered by Salary descending, limit to 3	3 rows with highest Salary
4	Select top N rows	Oracle	ROWNUM or FETCH FIRST	Use ROWNUM or FETCH FIRST 3 ROWS ONLY	3 rows with highest Salary
5	Exit	All	N/A	Query executed and results returned	Top N rows returned

💡 Execution stops after returning the top N rows as per database syntax.

Variable Tracker

Variable	Start	After SQL Server Query	After MySQL/PostgreSQL Query	After SQLite Query	After Oracle Query
Result Rows	Empty	3 highest Salary rows	3 highest Salary rows	3 highest Salary rows	3 highest Salary rows

Key Moments - 3 Insights

Why can't I use TOP in MySQL or PostgreSQL?

TOP is specific to SQL Server and some others; MySQL and PostgreSQL use LIMIT instead, as shown in execution_table rows 1 and 2.

Can I use LIMIT without ORDER BY to get top rows?

Without ORDER BY, LIMIT just returns any N rows, not necessarily the top ones. Ordering is needed to define 'top'. See execution_table rows 1 and 2 where ORDER BY Salary DESC is used.

How does Oracle get top N rows if it doesn't support TOP or LIMIT?

Oracle uses ROWNUM or FETCH FIRST syntax to limit rows, as shown in execution_table row 4.

Visual Quiz - 3 Questions

Look at the execution_table, which syntax is used in SQL Server to get top N rows?

A LIMIT N

B TOP N

C ROWNUM

D FETCH FIRST N ROWS ONLY

💡 Hint

Check execution_table row 1 under Syntax Used column.

At which step in the execution_table does the query for MySQL/PostgreSQL appear?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look for MySQL/PostgreSQL in the Database column.

If you remove ORDER BY from the queries, what changes in the result rows according to variable_tracker?

A Query will fail with syntax error

B Result rows remain the same, top salaries returned

C Result rows become random N rows, not necessarily top salaries

D Result rows will be empty

💡 Hint

Refer to key_moments question 2 explanation about ORDER BY necessity.

Concept Snapshot

TOP vs LIMIT in SQL:
- SQL Server uses: SELECT TOP N ...
- MySQL/PostgreSQL/SQLite use: SELECT ... LIMIT N
- Oracle uses: ROWNUM or FETCH FIRST N ROWS ONLY
- ORDER BY needed to define 'top' rows
- Syntax differs by database, but goal is same: limit rows returned

Full Transcript

This visual execution compares how different databases limit query results to top N rows. SQL Server uses TOP N syntax, while MySQL, PostgreSQL, and SQLite use LIMIT N. Oracle uses ROWNUM or FETCH FIRST syntax. The queries must include ORDER BY to get meaningful top rows. The execution table shows each step with syntax and results. Variable tracking shows the result rows after each query. Key moments clarify common confusions about syntax differences and the importance of ORDER BY. The quiz tests understanding of which syntax belongs to which database and the effect of removing ORDER BY. The snapshot summarizes the key syntax differences and usage rules.