SQL, or structured query language, lets us gather data from a database through queries. It also enables us to insert, update, and delete that data.
In this blog post, we will focus on how to fetch data and limit the results using SQL.
Why should you limit SQL query results?
A database is usually a huge collection of data. Sometimes we don't need to fetch all of the results. To limit the results, we can optimize the query.
Limiting query results is important for DB performance. Fetching a large result when it is not required incurs extra load on the database and impacts user experience.
How to limit query results in SQL
The syntax is different for SQL Server, Oracle, and MySQL for limiting the data.
- MySQL uses
- ORACLE uses
- MS Access and SQL Server use
We'll see examples of how each one works in detail below.
We have the following table named
students with their details as you can see below:
How to limit a query in MySQL
Below is the syntax for MySQL.
SELECT (expression) FROM table_name LIMIT 5;
As an example, we'll select the first 5 records from the table.
Let's use our table
students for this demonstration.
-- fetch top 5 values from table SELECT * FROM students LIMIT 5;
How to Combine LIMIT with ORDER BY
When you combine LIMIT with ORDER BY, you can get more meaningful results. For example we can use this to find the top 5 students who scored greater than 70% on their exam.
Let's order our table
students with the column
name and choose the top 5 from the result. You can do that like this:
SELECT * FROM students order by name LIMIT 5;
How to limit results – Oracle syntax
Below is the equivalent syntax for our first example in Oracle.
SELECT * FROM students FETCH FIRST 5 ROWS ONLY;
In older versions of Oracle, you can use ROWNUM to restrict the number of rows returned by a query.
SELECT * FROM students WHERE ROWNUM < 5;
How to limit results in SQL – MS Access syntax
Below is the equivalent syntax for our first example in MS Access.
SELECT TOP 5 * FROM students;
The LIMIT functionality can be very powerful for query optimization when combined with sorting. Efficient queries are lighter on the system and swift for the user. It is always recommended to LIMIT the results where applicable.