Chapter 3 : Derived table

In this chapter we are going to see the so-called derived tables. Derived tables are in fact virtual tables and actually do not exist in the database. They are indeed temporarily stored by using an alias.

Figure 3.1 : Database schema

1. Derived table

Let’s assume we’d like to get the number of likes and the number of comments that each post (article) has.

SELECT P.ID, P.post_title, derivLikes.nbLikes, derivComm.nbComments
FROM Posts AS P
LEFT JOIN
   ( SELECT post_ID, COUNT(*) AS nbLikes
     FROM Likes
     GROUP BY post_ID ) AS derivLikes ON P.ID = derivLikes.post_ID
LEFT JOIN 
   ( SELECT post_ID, COUNT(*) AS nbComments
     FROM Comments
     GROUP BY post_ID ) AS derivComm ON P.ID = derivComm.post_ID

We get the following table.

Let’s tell a bit more on the construction of derived tables. In the example above two derived tables have been constructed. The first one contains the number of likes that each post has and the second one the number of comments that each post has. To both of them a label is assigned by using aliases, namely derivLikes and derivComm. Finally outer joins are done for aggregating data from the table Posts and from both derived tables.

2. Cleaning

Now we’d like to display a 0 when no matching occurs in outer joins. On that purpose we can make use of the IFNULL function.

SELECT P.ID, P.post_title, IFNULL(derivLikes.nbLikes,0) AS Likes, IFNULL(derivComm.nbComments,0) AS Comments
FROM Posts AS P
LEFT JOIN
   ( SELECT post_ID, COUNT(*) AS nbLikes
     FROM Likes
     GROUP BY post_ID ) AS derivLikes ON P.ID = derivLikes.post_ID
LEFT JOIN 
   ( SELECT post_ID, COUNT(*) AS nbComments
     FROM Comments
     GROUP BY post_ID ) AS derivComm ON P.ID = derivComm.post_ID

We get the following table.