Search code examples
sqlderived-table

When is it not appropriate to use Derived tables?

This SO post details some benefits in performance regarding Derived vs. Temporary tables.

Other than performance, what situations exist for which a Derived table would not be appropriate.

One answer per post with an example would be helpful.

Solution

  • I would prefer to do a self-join on a temporary table than a derived table.

    CREATE TEMPORARY TABLE foo AS SELECT ...;

SELECT ... FROM foo f1 JOIN foo f2 ON ...conditions...;

    Versus using a derived table, where you have to write the whole query twice:

    SELECT ... 
FROM (SELECT ...)
JOIN (SELECT ...) ON ...conditions...;

    But another solution is to use common table expressions which are slightly different from derived tables:

    WITH foo AS (SELECT ...)
SELECT ... FROM foo f1 JOIN foo f2 ON ...conditions...;

    Provided you use a brand of database that supports this syntax (Microsoft, Oracle, IBM, PostgreSQL).