Here is the video going over Sub Query examples in this post (It may be easier to see the examples with each of their results) :

Before we dive in, I have the database schema included here below for easy reference just in case it helps (Note that the highlighted fields in bright yellow are from the adjustments that I made to the original set of data before I populated the database. It won’t affect anything here.):

A sub query (a.k.a. nested query) is a query that is contained within another query. The results of the inner query is used to inform the outer query of specific data to be used.

The relational database management system will execute the inner query first, then it works its way to the outer query.

Sub queries can be nested inside another sub query, as well as inside SELECT, INSERT, UPDATE, or DELETE statements. Typically, sub queries are within the WHERE clause of a SELECT statement. Comparison operators can be used with sub queries (>, <, =, IN, ANY, ALL).

Although, they are easy to read and use, sub queries can cause performance issues. Generally, it is recommended to use joins rather than sub queries where possible for much greater performance outcomes.

Let’s take a look at some examples with our Yoga database. For the first example, let’s find the names of the teachers who had total sales of 100,000 or more.

The first thing we want to do here is query from the taught_by table to retrieve all the teacher_id where the total_sales amount meets our criteria. So below is our initial step:

SELECT DISTINCT teacher_id 
FROM taught_by
WHERE total_sales >= 100000;

This query finds all the teacher_id information needed. Notice that we are using DISTINCT to avoid duplicate ids. Now we can use the results of this query (which will now serve as our sub query) to query the names of all the teachers that are associated with those ids. So let’s adjust the statement to reflect this with the inner and outer queries:

SELECT f_name, l_name
FROM yoga_teacher
WHERE teacher_id IN(
    SELECT DISTINCT teacher_id 
    FROM taught_by
    WHERE total_sales >= 100000
    )
;

Now, the results of this statement will be a list of all the first and last names of all the yoga teachers that match the ids that have total sales of 100,000 or above. Another way to look at this statement is by subbing in the actual resulting values of the sub query. That may help to visually understand what is actually happening in a simplified way:

SELECT f_name, l_name
FROM yoga_teacher
WHERE teacher_id IN(1111,1112,1117,1118,1120);

So, as you can see in the above statement, we have the specific values within the IN operator to define our conditions. These are the values retrieved from our sub query.

Now for our next example, let’s find all the class names taught by Gemma Lawrence.

The first piece of information we want to obtain is Gemma Lawrence’s teacher id. So, below is the query that will achieve that:

SELECT teacher_id
FROM yoga_teacher
WHERE f_name = 'Gemma' AND l_name = 'Lawrence';

Next, we will nest this query inside another query in order to find all the class ids that are associated with Gemma’s teacher id. So, see below for how we build that out:

SELECT class_id
FROM taught_by
WHERE teacher_id IN(
    SELECT teacher_id
    FROM yoga_teacher
    WHERE f_name = 'Gemma' AND l_name = 'Lawrence'
    )
;

Now, we can further nest these sub queries inside another query in order to retrieve all the names of the classes that the class ids identified through this statement are associated with. So below is our final statement to retrieve the data that meets the full criteria to complete our goal for this example:

SELECT class_name
FROM class_offered
WHERE class_id IN(
    SELECT class_id
    FROM taught_by
    WHERE teacher_id IN(
	SELECT teacher_id
        FROM yoga_teacher
        WHERE f_name = 'Gemma' AND l_name = 'Lawrence'
        )
    )
;

This query would be the same as the statement written below if we just subbed in the values from the results within our sub queries:

SELECT class_name
FROM class_offered
WHERE class_id IN(200, 202, 203);

That’s it for now. I hope this is helpful.

Feel free to watch the related video. It may be easier to visualize and understand the examples.

Thanks for learning with me, friends :).