Subqueries in the FROM clause

Subqueries are legal in a SELECT statement's FROM clause. The syntax that you'll actually see is:

SELECT ... FROM (<subquery>) AS <name> ...

The AS <name> clause is mandatory, because any table in a FROM clause must have a name. Any columns in the <subquery> select list must have unique names. You may find this syntax described elsewhere in this manual, where the term used is derived tables.

For illustration, assume you have this table:

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

Here's how to use the Subqueries in the FROM clause feature, using the example table:

INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
 FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
 WHERE sb1 > 1;

Result: 2, '2', 4.0.

Here's another example: Suppose you want to know the average of the sum for a grouped table. This won't work:

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

But this query will provide the desired information:

SELECT AVG(sum_column1)
 FROM (SELECT SUM(column1) AS sum_column1
 FROM t1 GROUP BY column1) AS t1;

Notice that the column name used within the subquery (sum_column1) is recognized in the outer query.

At the moment, subqueries in the FROM clause cannot be correlated subqueries.

Retornar