# 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 <a href="#derived-tables-vs-sub-query" id="derived-tables-vs-sub-query"></a>

* 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;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://gchandra.gitbook.io/data-warehousing/rdbms/sql-concepts/derived-tables.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.