MySQL Derived Table

MySQL supports derived tables in straightforward terms called virtual tables as it provides us the virtual table that can be used free of memory directly using the SELECT statement.

The concept is very much similar to temporary tables. Using a derived table using the select statement is much easier than the temporary table because there is no need to implement the steps that the temporary table follows.

Syntax

SELECT * FROM  
(SELECT col1, col2,..., colN from table_name)derived_table_name  
WHERE condition;

Example: Derived Table in MySQL

Today, we will be using the tables 'payments' and 'customers.'

Our job is to fetch the names and numbers of customers who have not made any payment.  

SELECT * FROM  
(SELECT c.customer_number AS id, c.customer_name as name, c.phone, p.amount as payment from customerss c INNER JOIN payments p ON c.customer_number = p.customer_number where p.amount is null)Tab  
WHERE Tab.name = 'luke'  

• Here we can see we have used the concept of the derived table.
• So customers and payment tables have been merged using inner join and common IDs.
• After we have isolated the customer name where the amount placed is null, there is no payment done.
• This yields the customers wh have not made payment.
• So coming back to the derived table concept here, after using the SELECT statement within the parenthesis, we used the inner join operation part as our derived table/virtual table and named it 'Tab.'
• We can see that we have interacted with the virtual table by using WHERE and filtered out customer name luke from the unpaid list.
• Calling Tab works because it is a derived table of the inner join operation.

Conclusion:
 
So this is how we can effectively use the derived table and temporarily store a whole query within the SELECT statement to be used and queried with the other planned conditions.