CODEWITHSUNDEEP
sqlderived-table
Curtis Inderwiesche
Curtis Inderwiesche

Reputation: 4980

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.

Upvotes: 5

Views: 740

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562368

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562368

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

Upvotes: 1

dave
dave

Reputation: 1364

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.

Upvotes: 1

Related Questions