# View flattening

When evaluating a statement that references a view, Derby internally transforms a view into a derived table. This derived table might also be a candidate for flattening into the outer query block.

A view or derived table can be flattened into the outer query block if all of the following conditions are met:

- The select list is composed entirely of simple column references and constants.
- There is no GROUP BY clause in the view.
- There is no DISTINCT in the view.
- There is no ORDER BY, result offset, or fetch first clause in the view.

For example, given view v1(a,b):

    SELECT Cities.city_name, Countries.country_iso_code
    FROM Cities, Countries
    WHERE Cities.country_iso_code = Countries.country_iso_code

and a SELECT that references it:

    SELECT a, b
    FROM v1 WHERE a = 'Melbourne'

after the view is transformed into a derived table, the internal query is

    SELECT a, b
    FROM (select Cities.city_name, Countries.country_iso_code
    FROM Cities, Countries
    WHERE Cities.country_iso_code = Countries.country_iso_code) v1(a, b)
    WHERE a = 'Melbourne'

After view flattening it becomes

    SELECT Cities.city_name, Countries.country_iso_code
    FROM Cities, Countries
    WHERE Cities.country_iso_code = Countries.country_iso_code
    AND Cities.city_name = 'Melbourne'

Parent topic: View transformations
Related concepts
Predicates pushed into views or derived tables