Derived Tables

A derived table is similar to a Temporary table but derived from another SELECT statement.

When a subquery starts at the From clause (instead of Where), the result set is called Derived table.

Table invoices have around ten columns; our derived table 'tbl' has four columns.

use murach;

SELECT
    *
FROM
    (
    SELECT
        invoice_number
        ,invoice_date 
        ,payment_total 
        ,credit_total 
    FROM invoices
    ) tbl;

Query with Case statements

use Murach;

SELECT
    *
FROM
    (
    SELECT
        invoice_number
        ,invoice_date 
        ,payment_total 
        ,credit_total 
        ,CASE terms_id
            WHEN 1 THEN 'Net due 10 days'
            WHEN 2 THEN 'Net due 20 days'
            WHEN 3 THEN 'Net due 30 days'
            WHEN 4 THEN 'Net due 60 days'
            WHEN 5 THEN 'Net due 90 days'
        END AS terms
    FROM invoices
    ) tbl;

'tbl' is the Table alias name. It can be anything. Just give a name for the output of the Derived query.

Derived Tables vs Sub Query

• When the query is used with FROM clause, it's the DERIVED table.

• When the query is used with a WHERE clause, it's Sub Query.

Sub Query

use sakila;

SELECT 
  * 
FROM 
    actor
WHERE 
    actor_id IN 
    (SELECT actor_id FROM film_actor
    WHERE film_id = 
        (SELECT film_id FROM film 
        WHERE title = 'Ace Goldfinger')
    );

Derived Table

use sakila;

SELECT * FROM 
(
    SELECT 
        * 
    FROM 
        actor
    WHERE 
        actor_id IN 
        (SELECT actor_id FROM film_actor
        WHERE film_id = 
            (SELECT film_id FROM film 
            WHERE title = 'Ace Goldfinger')
        )
    ) t;