When is it not appropriate to use Derived tables?

This SO post detai开发者_开发知识库ls 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.

---

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).

---

Scope might be one. I think temp tables can be made accessible to other transactions / processes, etc. Derived tables are limited to the block in which they are declared.

---

If you have to access the data in the temporary table in multiple queries it might be less costly to avoid generating the temp data repeatedly:

CREATE TEMPORARY TABLE foo AS SELECT ...;

SELECT ... FROM foo WHERE ...conditions...;

-- sometime later

SELECT ... FROM foo WHERE ...different conditions...;