SQL LIMIT: Quick Tutorial

Watson Tech World

Learn how to use the SQL LIMIT clause to efficiently retrieve a specific number of rows from your database. This tutorial covers the fundamental concept of LIMIT, demonstrates its practical application with examples in MySQL Workbench and an interactive SQL environment, and shows how it's essential for managing large datasets and improving query performance.  Subscribe / follow for more! --- Run SQL code online p1: https://www.mycompiler.io/view/7NDuKr6c36n Run SQL code online p2: https://www.mycompiler.io/new/sql Code from video: https://go.watsontechworld.com/82-code-sql-limit --- 🚀 Launch your websites with Cloudways — managed cloud hosting made easy. Try Cloudways for fast, secure WordPress, WooCommerce, or custom apps. 👉 Get started with our link 👇 https://go.watsontechworld.com/cloudways --- 00:00 Intro 00:36 Explaining SQL LIMIT 03:34 SQL LIMIT examples with MySQL Workbench 12:15 SQL LIMIT examples with interactive website 14:17 Conclusion --- #SQL #MySQL #SQLTutorial #database #MySQLWorkbench

Transcript

00:00Hi everyone, in this video I'm going to do a tutorial on SQL Limit, the limit

00:07keyword, and it's a very useful thing in MySQL and I'm going to give you several

00:13examples and teach you all about it. Everything you need to know. So we'll use

00:21MySQL Workbench, I'll show you using MySQL in the terminal, and I'll show you

00:28using an interactive SQL site. So let's get started. So the first thing is I'll

00:39explain about the SQL Limit statement. In a nutshell, the limit clause is a way to

00:47restrict or limit the number of rows of output. It will give you at most the

00:53first n rows, where n is the number you specify after the keyword limit. It is

01:00useful if you have a lot of rows of output to deal with, and it is also a way to

01:05limit duplicate rows if you use limit one. And for example, a lot of rows, I'll give

01:11you an example with more than a hundred thousand rows, and so limiting the number

01:17of rows can be very useful. The basic form is basically any SQL query ending with

01:26limit n, where n is a positive integer. And limit n will always be the last part of an

01:32SQL statement if you choose to use it. So this is an example select star from your

01:39table, limit 5. That means if there's five or more rows, it'll just get five rows. And

01:48there could actually, if this has less than five rows, limit 5 will basically not help you. But

01:58this will just give you at most five rows. Limit will never give you more rows than the actual query.

02:11So again, let's say that if this was even just one row, limit 5 would not help it. But it would limit

02:19it to five if there was five or more. Okay, and then this one here is similar. If we have a salary table,

02:27limit 10. And I'll show you here, we have a salary table. And for example, here, if we want to limit

02:34to the first 10 rows, we would get something like this. If we wanted to select all, we would get all of

02:41these rows, all 17 data rows. But if we say limit 10, we would just get these.

02:47And similarly, if we said, for example, select name, gender, yearly, salary, from salary, and limit 5,

02:59what that would do is get these columns and limit it to five rows. So I said name, gender, yearly, salary.

03:09So we would get this, this, and this, these columns. And it would only give us these first five rows.

03:22So it would look like that. So that's the data we would get.

03:28And again, like I mentioned, we're going to do some examples here later. Okay, let's get started with

03:34MySQL Workbench. So again, this is a table, I've called it salary, and I've made a salary table

03:42in a database. This is just the person's name, their career, yearly salary, gender, age, and age group.

03:52And let's get started. So if we want to show all the rows and all the data from that table,

03:59you can do a select star from the table, in this case, select star from salary. And note that we have

04:0617 rows. But what if we want to limit it to just five rows? We can do the same thing and add limit five,

04:16or limit anything, just as long as so again, there's 17 rows, it would only really be useful if we said

04:22basically limit the maximum. If we go more than 17, it's not going to be really useful. So anyway,

04:33that's limit five. Let's say we wanted it to limit just two. If we wanted to limit it to just two,

04:40then it'll just give us two. I'll go back to, again, let's say we want to limit 10,

04:45this gives us 10. I'll go back to five. So here limit five. Now let's select these columns. So the name

04:53column, gender column, career, age, yearly salary, from salary. Okay, this was something

05:02if we want to order it by age. So if we want to go from youngest to oldest, we see we have all this

05:12data here. So this person's 23, the person is the youngest, and the oldest is Wayne at 75.

05:20But let's say we want to do the same thing, but we want to limit it to just 10 people.

05:25This query here is the same. All right, so this part query is the same as here. I just broke it up

05:31into multiple lines, so it's easier to see. So again, select these columns from this table.

05:42And this means basically sort it by age and ascending, but we're going to limit it to 10,

05:4910 rows. People with the highest income down to the people with the lowest income.

05:55So this person makes the most, and this is the second highest down to here. These two people make

06:03the least per year. Now let's say we want to do the same thing, except we want to limit it to seven rows.

06:12And let's do that.

06:13And what do you know? We have seven rows. So this person makes the most, and this person makes the

06:21seventh most. So I think this is still a respectable salary. And then this person makes quite a lot,

06:28way more than most people.

06:32And again, that was just from this table. So there's so much you can do even with a simple table with SQL.

06:38Now let's do a much larger, let's use a much larger database. So this is a star database.

06:48And basically it has a lot of information about stars. It has these columns. I won't explain all

06:55these because it would take time. You can check this out and pause the video if you want to read more

06:59about this. But basically there's the star name and parsecs from Earth, light years from Earth,

07:07the constellation full name, and the apparent magnitude, which means basically the brightness

07:13as seen from Earth. And the smaller this number would mean the higher, the higher the brightness

07:19as seen from Earth. So let's select all. Okay. And one thing to note is that in MySQL Workbench,

07:25we actually have this thing that says limit to 1000 rows by default. And that's actually a similar,

07:31I think that is actually using this command, the limit command. But I'm going to say don't limit,

07:40just to show you here, because there's more than 100,000 rows. And I want to show you

07:45why this command is very useful. Okay. So we've got quite a lot of rows, again,

07:55more than 100,000. And MySQL Workbench actually kind of froze on me for a few moments because

08:10there's so many rows. So there's almost 120,000 rows, or anyway, more than 100,000 rows,

08:18approaching 120,000 rows. And when you do have so many rows, for example, in an Excel file,

08:25or even SQL, it can actually, yeah, the system can go quite slow. So this is another reason why

08:35to use the limit statement. So anyway, there's more than 100,000 rows in this. And just to show you

08:45here, this statement, the count star, what that does actually is, it tells us that

08:53how many rows exactly are in that query. So this just tells us exactly how many rows are in basically

09:00this query. So there's quite a lot of rows. And now again, dealing with so many different rows can be

09:06a problem for a lot of different reasons. So this is one reason why having limit is so useful. Now let's

09:13limit it to just five rows. Okay, this is a thing I wanted to mention is that this, this one only

09:21give us actually run row. So giving us limit five won't actually help us. It'll still only give us

09:26one row. Let's go back to this one here. Instead of over 100,000 rows, let's limit it to just 100 rows.

09:35And note here, when I can scroll down, I only get 100 rows.

09:40So that's very useful. So it's much easier to deal with 100 rows than over 100,000.

09:51Okay, now this statement is saying, select the star name, the light years from Earth,

09:56the constellation full name, and apparent magnitude from the star data. And this is going to give us,

10:04again, more than 100,000 rows. I won't sort it because it might slow down a lot. This one also has

10:13more than 100,000 rows. But this one here, we'll just limit it to, and if I just want to scroll down,

10:21I guess because this column is not aligned, you can't see, but there are more than 100,000 rows.

10:27This one here, we're going to do the same one, except now we're going to limit it to just 100 rows.

10:33And note that we have just 100 rows, so it's very useful.

10:39And now let's do this one here. Let's order, let's do a similar query, but now let's order by light

10:46years from Earth ascending. So basically get the closest stars, and then ending with the stars furthest

10:54away. And of course, our sun is the closest, and then the next closest would be Proxima Centauri.

11:02And then these three stars are actually part of a trinary system. So they're the closest stars to us.

11:11And then that still also gives us more than 100,000 rows. But if we want to do this,

11:17limit to just 100 rows. Note that we have just 100 rows, and it's very useful. Okay, now,

11:28running out of time, let's see here, we'll do another one here. Let's do a,

11:37let's do this one here, we'll do a group by. So let's group by the average light years from Earth

11:43for constellations and order by the average light years from Earth.

11:52So this still gives us a lot of, this gives us 72. So 72 constellations and the average light years

11:59from Earth. But let's say that we wanted to limit that. We didn't want 72 rows, let's say we wanted

12:06just 50 rows. And note here, we have just 50. Okay, now I wanted to show you one thing before we go,

12:16is that we can, you can run this code and do the same things I did before, but in a web browser.

12:27So this one selects all the data from the salary table. It's the same data as here.

12:33And for example, but let's say we wanted to run this query, but limit it to just five rows.

12:42You can do limit five. And see, we only have five rows. Similarly, let's select these columns,

12:49the name, gender, career, age, yearly salary, from salary, order by age. So what that does is

12:57basically get the, get the rows and sort them from youngest to oldest. So this person's the youngest,

13:03this person's the oldest, Wayne is the oldest, and Cheryl is the youngest.

13:10And similarly, the same query, I just broke it up over a few different lines.

13:16Let's say we want just 10 rows.

13:19So if we're going to uncomment all of these, we want just 10 rows.

13:25So this has all the rows. If we want just 10 rows.

13:30Notice we have fewer rows.

13:35And I think we can do one more here.

13:39Or I run out of time.

13:40So this query will sort by the yearly salary, basically from highest income to lowest income.

13:49And then this one will sort it the same query, except we will

14:00limit it to just seven people. And let's do that here.

14:04Okay, fantastic. And if you go to just this URL, you can copy this and you can paste it in and you

14:14can run it, run it on your own. Okay, I hope you enjoyed this video. This video was a quick tutorial

14:22on the SQL limit statement. I hope you enjoyed it. And if you enjoyed it, please subscribe to or follow

14:30this channel and see you in the next one. Thank you.

Category

Transcript

Recommended