Subqueries are legal in a SELECT
statement's FROM
clause. The actual syntax
is:
SELECT ... FROM (subquery
) [AS]name
...
The [AS]
clause is mandatory, because every table in a
name
FROM
clause must have a name. Any columns in
the subquery
select list must have
unique names.
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.
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.
It is possible under certain circumstances to modify table data
using EXPLAIN
SELECT
. This can occur if the outer query accesses any
tables and an inner query invokes a stored function that changes
one or more rows of a table. Suppose that there are two tables
t1
and t2
in database
d1
, created as shown here:
mysql>CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec) mysql>USE d1;
Database changed mysql>CREATE TABLE t1 (c1 INT);
Query OK, 0 rows affected (0.15 sec) mysql>CREATE TABLE t2 (c1 INT);
Query OK, 0 rows affected (0.08 sec)
Now we create a stored function f1
which
modifies t2
:
mysql>DELIMITER //
mysql>CREATE FUNCTION f1(p1 INT) RETURNS INT
mysql>BEGIN
mysql>INSERT INTO t2 VALUES (p1);
mysql>RETURN p1;
mysql>END //
Query OK, 0 rows affected (0.01 sec) mysql>DELIMITER ;
Referencing the function directly in an
EXPLAIN
SELECT
does not have any effect on
t2
, as shown here:
mysql>SELECT * FROM t2;
Empty set (0.00 sec) mysql>EXPLAIN SELECT f1(5);
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) mysql>SELECT * FROM t2;
Empty set (0.00 sec)
This is because the SELECT
statement did not reference any tables, as can be seen in the
table
and Extra
columns of
the output. This is also true of the following nested
SELECT
:
mysql>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql>SHOW WARNINGS;
+-------+------+------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------+ | Note | 1249 | Select 2 was reduced during optimization | +-------+------+------------------------------------------+ 1 row in set (0.00 sec) mysql>SELECT * FROM t2;
Empty set (0.00 sec)
However, if the outer SELECT
references any tables, then the optimizer executes the statement
in the subquery as well:
mysql>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+ | 1 | PRIMARY | a1 | system | NULL | NULL | NULL | NULL | 0 | const row not found | | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+ 3 rows in set (0.00 sec) mysql>SELECT * FROM t2;
+------+ | c1 | +------+ | 5 | +------+ 1 row in set (0.00 sec)
This also means that an
EXPLAIN
SELECT
statement such as the one shown here may take a
long time to execute:
EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
This is because the BENCHMARK()
function is executed once for each row in t1
.
User Comments
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.
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
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.
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.
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.
After spending some time on how to do a subquery on a query using a UNION, the correct syntax is as follows :
SELECT
service, price, sum(nb_transaction) as nb_transaction
FROM (
(
SELECT
service, price, count(id) as nb_transaction
FROM billing.bills t1
GROUP BY service, price
)
UNION ALL
(
SELECT
service, price, count(id) as nb_transaction
FROM billing_archive.bills t2
GROUP BY service, price
)
) t3
GROUP BY service
You can nest select statements; a select clause in place of a table can contain another select clause. This works if you want to perform two different aggregation operations. For example, I have a database with invoices and line items, and I want to know the total sales for each day, and the number of distinct products I sold on each day. To get the number of distinct product codes, I create a table using "select distinct" and group the products by date. To get the frequency counts, I then create another table in which I count the distinct codes for each day. To get the sums, I merge the count table and the sum table by date. To do this in one MySQL statement, I do the following:
select total_sales, distinct_products, sales_date
from (select sum(inv_total) as total_sales,
inv_date as sales_date
from invoice
group by inv_date)
as t0,
(select count(dpc) as distinct_products,
date_cat as count_date
from (select distinct prod_code as dpc,
inv_date as date_cat
from invoice, line
where invoice.inv_number = line.inv_number)
as t1
group by date_cat)
as t3
where t0.sales_date = t3.count_date;
Add your own comment.