SQL LIMIT

Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.

In this lesson we'll cover:

• Automatic LIMIT

• Why should you limit your results?

• Using the SQL LIMIT command

The limit restricts how many rows the SQL query returns. The default value is 100; It's telling the database to only return the first 100 rows of the query. Because the dataset tutorial.us_housing_units has more than 100 rows, the queries thus far haven't been returning the full result sets. Try turning the LIMIT off (by clicking the check mark next to it) and running this query.

SELECT *
  FROM tutorial.us_housing_units

You'll notice many more rows get returned. You can tell by checking the header of the results table:

Many analysts use limits as a simple way to keep their queries from taking too long to return. The aim of many of your queries will simply be to see what a particular table looks like—you'll want to scan the first few rows of data to get an idea of which fields you care about and how you want to manipulate them. If you query a very large table (such as one with hundreds of thousands or millions of rows) and don't use a limit, you could end up waiting a long time for all of your results to be displayed, which doesn't make sense if you only care about the first few.

The limiting functionality is built to prevent you from accidentally returning millions of rows without meaning to (we've all done it). The following syntax does the same thing as having the box checked with a value of 100:

SELECT *
  FROM tutorial.us_housing_units
 LIMIT 100

Practice Problem

---

Write a query that uses the LIMIT command to restrict the result set to only 15 rows.

Try it outSee the answer

SELECT *   FROM tutorial.us_housing_units  LIMIT 15