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.
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.
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.
We get the following table.
Recap
A derived table is the result of a SQL query which is temporarily stored by using an alias.
Like subqueries, derived tables are used when an intermediary result is needed to accomplish a main task.
The function IFNULL
above exposed is the way to do in MySQL and MariaDB only. Other RDBMS‘s have a slightly different way to do it.