Additional languages
« 13.2.8.7 Correlated Subqueries
13.2.8.9 Subquery Errors »
Section Navigation      [Toggle]

13.2.8.8. Subqueries in the FROM clause

Subqueries are legal in a SELECT statement's FROM clause. The actual syntax is: 

SELECT ... FROM (subquery) [AS] name ...

The [AS] name clause is mandatory, because every table in a FROM clause must have a name. Any columns in the subquery select list must have unique names. You can find this syntax described elsewhere in this manual, where the term used is “derived tables.”

For the sake of illustration, assume that you have this table: 

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

Here is how to use a subquery in the FROM clause, 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 is another example: Suppose that you want to know the average of a set of sums for a grouped table. This does not work: 

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

However, this query provides 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.

Subqueries in the FROM clause can return a scalar, column, row, or table. Subqueries in the FROM clause cannot be correlated subqueries, unless used within the ON clause of a JOIN operation.

Subqueries in the FROM clause are executed even for the EXPLAIN statement (that is, derived temporary tables are built). This occurs because upper-level queries need information about all tables during the optimization phase, and the table represented by a subquery in the FROM clause is unavailable unless the subquery is executed.


User Comments

Posted by Are you mortal? Then prepare to die. on November 24 2004 3:02pm[Delete] [Edit]

You can use this feature to get 'double' counts, or frequency counts from mysql in a sinlge query...

select g2, count(*) from
(select
g1,
count(*) as g2
from
t1
group by
g1
) as virtual_table
group by
g2;

The inner query gives you the frequency of each group 'g1' in the table t1. The outer query groups over this 'table' to give you the 'frequency frequency' or the 'frequency count' (g2), which tells you how often groups of a certain frequency occur in your data.

For example, perhaps the most common frequency for a group is 1, then a few groups occur twice, and one group occurs 1000 times...

inner query results...
[fixed]
g1 g2
a 1
b 1
c 1
d 1
e 1
... ad nausim
w 2
x 2
y 2
z 1000
[\fixed]


The overall query results...

g2 count(*)
1 26ish
2 3
1000 1

This is somthing I find useful to calculate over my data, and previously used two tables.

Posted by [name withheld] on December 2 2004 11:45am[Delete] [Edit]

Unfortunately, it seems that it is not possible to use a subquery in the FROM clause that contains a UNION.

EX:
SELECT *
FROM (
SELECT * from table1 WHERE blabla1
UNION
SELECT * from table1 WHERE blabla2
) AS test_alias

this won't work

Posted by Ricardo Santos on January 17 2005 1:12pm[Delete] [Edit]

I try this select in my tables and it work perfect(MySQL 4.1.8) :

select sum(Type) as Type,sum(Qtt) as Qtt from
(
select count(type_con) as Type, sum(qtt_loc) as QTT from api_pq_200412 where id=1 and type_con=1 and dt between '2004-12-01' and '2005-01-17'
union all
select count(type_con) as Typeb, sum(qtt_loc) as QTTb from api_pq_200501 where id=1 and type_con=1 and dt between '2004-12-01' and '2005-01-17 '
) as sels

This select return the result:
Type QTT
527 7886

It's possible to use Union with SubQueries.

Posted by Jim Grill on March 21 2005 6:16pm[Delete] [Edit]

Pulling your hair out because of errors like so: "ERROR 1248 (42000): Every derived table must have its own alias" ?

If you have several complicated unioned queries that you want to use in a sub query try encapsulating them like so:

SELECT col1,col2,col3 FROM
(

SELECT * FROM
(
SELECT col1,col2,col3 FROM myTable WHERE ... etc.
) AS DerivedTable1

UNION

SELECT * FROM
(
SELECT col1,col2,col3 FROM myTable WHERE ... etc.
) AS DerivedTable2

) AS MainTable GROUP BY... HAVING... ORDER BY... LIMIT... etc.

This method of aliasing will add years to your life by providing the unique distinction required of derived tables in sub queries without having to spend a bunch of time trying to alias every table and field while continuing to get "derived table" errors. Thanks goes to David Fells for helping me keep some of my hair over this one!

Have fun.

Posted by Lefteris on February 1 2006 4:51am[Delete] [Edit]

Wonderful tip! Thanks. Just to add that we still can't avoid an alias for each COUNT(), MAX() in the deep-nested queries. Example based on the above:

SELECT maxcol1,col2,col3 FROM
(

SELECT * FROM
(
SELECT MAX(col1) AS maxcol1,col2,col3 FROM myTable WHERE ... etc.
) AS DerivedTable1

UNION

SELECT * FROM
(
SELECT MAX(col1) AS maxcol1,col2,col3 FROM myTable WHERE ... etc.
) AS DerivedTable2

) AS MainTable GROUP BY... HAVING... ORDER BY... LIMIT... etc.

Add your own comment.