When is it not appropriate to use Derived tables?

Question

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.

That [question is tagged for SQL Server](http://stackoverflow.com/questions/2326395/which-one-have-better-performance-derived-tables-or-temporary-tables), so the info should be considered specific to it and not for all databases. — OMG Ponies, Jun 30 '10 at 16:19

score 1 · Answer 1 · answered Jul 29 '10 at 23:07

1

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.

answered Jul 29 '10 at 23:07

dave

1,344
10
16

score 1 · Accepted Answer · answered Jul 31 '10 at 00:52

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

score 1 · Answer 3 · answered Jul 31 '10 at 00:55

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

When is it not appropriate to use Derived tables?

3 Answers3