LIMIT Clause

In the previous section, we discussed how to retrieve records from a database using the SELECT statement. For all those queries, the resulting output was all the records available in the table. This can become problematic if you have a table with millions or even billions of rows especially when you just need a subset of that information for validation. Here is where the LIMIT clause becomes useful.

The LIMIT clause returns a sample of $n$ rows of the records that you are querying for. If you want to return 10 values, you would specify 10 when applying the LIMIT clause.

Let's demonstrate this in an example. Below is the result of a query that returns all the records available in the customers_new table.

select *
from customers_new

| customer_id | first_name | last_name  | email                        | city        | country     | registration_date |
|-------------|------------|------------|------------------------------|-------------|-------------|-------------------|
| 2001        | Alice      | Johnson    | [email protected]      | New York    | USA         | 2023-01-15        |
| 2002        | Bob        | Williams   | [email protected]       | London      | UK          | 2023-02-20        |
| 2003        | Carol      | Brown      | [email protected]        | Toronto     | Canada      | 2023-03-10        |
| 2004        | David      | Davis      | [email protected]        | Sydney      | Australia   | 2023-04-05        |
| 2005        | Emma       | Miller     | [email protected]        | Paris       | France      | 2023-05-12        |
| 2006        | Frank      | Wilson     | [email protected]       | Berlin      | Germany     | 2023-06-18        |
| 2007        | Grace      | Moore      | [email protected]        | Tokyo       | Japan       | 2023-07-22        |
| 2008        | Henry      | Taylor     | [email protected]       | Madrid      | Spain       | 2023-08-30        |
| 2009        | Ivy        | Anderson   | [email protected]       | Rome        | Italy       | 2023-09-14        |
| 2010        | Jack       | Thomas     | [email protected]        | Amsterdam   | Netherlands | 2023-10-08        |
| 2011        | Kate       | Jackson    | [email protected]       | Stockholm   | Sweden      | 2023-11-12        |
| 2012        | Liam       | White      | [email protected]         | Oslo        | Norway      | 2023-12-05        |

In this example, we have all the data retrieved as output. However, suppose we wanted to only see a sample of 5 rows of the output. We can write a query with the limit clause as seen below to return a sample of 5 rows.

select *
from customers_new
limit 5

| customer_id | first_name | last_name  | email                        | city        | country     | registration_date |
|-------------|------------|------------|------------------------------|-------------|-------------|-------------------|
| 2001        | Alice      | Johnson    | [email protected]      | New York    | USA         | 2023-01-15        |
| 2002        | Bob        | Williams   | [email protected]       | London      | UK          | 2023-02-20        |
| 2003        | Carol      | Brown      | [email protected]        | Toronto     | Canada      | 2023-03-10        |
| 2004        | David      | Davis      | [email protected]        | Sydney      | Australia   | 2023-04-05        |
| 2005        | Emma       | Miller     | [email protected]        | Paris       | France      | 2023-05-12        |

What happers if we set LIMIT at value 7? Below is the query and the resulting output.

select *
from customers_new
limit 7

| customer_id | first_name | last_name  | email                        | city        | country     | registration_date |
|-------------|------------|------------|------------------------------|-------------|-------------|-------------------|
| 2001        | Alice      | Johnson    | [email protected]      | New York    | USA         | 2023-01-15        |
| 2002        | Bob        | Williams   | [email protected]       | London      | UK          | 2023-02-20        |
| 2003        | Carol      | Brown      | [email protected]        | Toronto     | Canada      | 2023-03-10        |
| 2004        | David      | Davis      | [email protected]        | Sydney      | Australia   | 2023-04-05        |
| 2005        | Emma       | Miller     | [email protected]        | Paris       | France      | 2023-05-12        |
| 2006        | Frank      | Wilson     | [email protected]       | Berlin      | Germany     | 2023-06-18        |
| 2007        | Grace      | Moore      | [email protected]        | Tokyo       | Japan       | 2023-07-22        |
| 2008        | Henry      | Taylor     | [email protected]       | Madrid      | Spain       | 2023-08-30        |
| 2009        | Ivy        | Anderson   | [email protected]       | Rome        | Italy       | 2023-09-14        |
| 2010        | Jack       | Thomas     | [email protected]        | Amsterdam   | Netherlands | 2023-10-08        |

Limits are very useful in returning a sample of data of the query that you are running. Data analysis using complex queries are often written iteratively to achieve a specific output and limit clause is very useful invalidating the results without returning all the records which can be computationally expansive.