Let’s say we want to display the number of unique authors that have written a book for each course.

One way of doing it would be using a complex JOIN query

Another way of doing this is by using a subquery in JOIN clause.

Notice, that in the above query, the subquery in the JOIN clause creates a link between courseId and count of distinct authorIds. Then it is joined with the name of courses using courseId as a link. This means the subquery can be individually calculated first and then be worked upon later.

Sometimes, when there is an expected use of results of a subquery, we can create them separate using an SQL property called VIEWs. Notice the set of statements below.

Notice that we abstract the results of the first query in a VIEW called booksSummary. The same VIEW can be used as a table later in any kind of query/statement.

Run the statements below to see what is saved in the booksSummary VIEW.