So far in this course, we have covered single query types of SQL statements. That means that we'll run a statement such as SELECT * FROM addresses or guides but there wasn't any inside nested logic and that's what we're going to be getting into in this guide. We're going to talk about subqueries.

So what a subquery is it gives us the ability to run multiple queries at the same time. They actually are a nested query inside of another query. Here I'm going to create a SELECT statement where I look for a guides_title, guides_revenue, and then FROM the guides_table. Now, this is where the subquery is going to start it's going to start inside of the WHERE clause. I’m going to say WHERE guides revenue is equal to.

Now inside of parentheses, we're going to run a new query. I'm going to say SELECT MAX guides_revenue and then FROM guides. Now I can close the parentheses out.

Let's actually run this and see what the result are. Now if I run this you can see that it returns 2 items.

Now we do have a little bit of an issue because remember guides_revenue if we run max on it and max finds the largest item. This is going to have some weird results because we need to cast it as an “unsigned integer”. So I'm going to say “cast guides_revenue as unsigned".

Now let's run the same thing again. Now we have a result of five thousand. What this is doing is it's giving us the ability to grab the largest values. That is not the hardest thing to do. We could just say where are guides revenue and then you find the max value from that.

What I wanted was to actually return the record itself and I wanted to dynamically find what that value is. Let's take for example how we would have to do this manually. We'd have to already know what guides revenue max was, we'd have to know it was 5000.

Now if I run this you can see that now this returns the same result. You may wonder why in the world would we need a nested subquery. The reason for that is there are many times when you are going to have no clue what the maximum revenue is. So that is where subqueries come in. Subqueries make it possible to work with truly dynamic data.

As you start to build out more professional applications and you're asked to build large queries. You're going to find yourself in a situation many times where you don't know the value that you're going to be looking for is. So you're going to have to do things like this where your here statement is not going to have a hardcoded number. It is simply going to go out and it's going to go find whatever the largest value is in this case.

Now I could run a similar query but we're going to have an entire section on functions but just say you have a little bit of idea.

If I wanted the smallest number I can run MIN and now if I run this you can see that this now brings me the result of 500.

So this is something that you'll find very helpful when it comes to building out your system. That is that you can run a query inside of another one and now start working with dynamic data.

Code

USE devcamp_sql_course_schema;

SELECT guides_title, guides_revenue
FROM guides
WHERE guides_revenue = (
  SELECT MAX(CAST(guides_revenue AS UNSIGNED))
  FROM guides
);

SELECT guides_title, guides_revenue
FROM guides
WHERE guides_revenue = 5000;

SELECT guides_title, guides_revenue
FROM guides
WHERE guides_revenue = (
  SELECT MIN(CAST(guides_revenue AS UNSIGNED))
  FROM guides
);