Let's say we want to display student names and name of the course they are enrolled in for all the students who are enrolled in a course that has more than 3 books belonging to it.
To solve this problem, we first need to figure out the course ids of courses which have more than 3 books belonging to them, using the query below:
Once we get the answer, which can be a list of course ids which we can use in a further query to get the desired list of students, using an IN filter.
The same problem can be solved in a single query by using a subquery, but notice that unlike the earlier examples that returned a single result from a subquery, we have a list of results. For cases like this when the result from subquery is a list, we need to use IN as the operator in the WHERE filter clause.