| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | | | 1 | PRIMARY | a1 | system | NULL | NULL | NULL | NULL | 0 | const row not found | Mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2 References any tables, then the optimizer executes the statement | Note | 1249 | Select 2 was reduced during optimization | | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | Mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2 This is also true of the following nested Statement did not reference any tables, as can be seen in the | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | Mysql> CREATE FUNCTION f1(p1 INT) RETURNS INT Tables and an inner query invokes a stored function that changes This can occur if the outer query accesses any It is possible under certain circumstances to modify table data Subquery in the FROM clause is unavailable This occursīecause upper-level queries need information about all tablesĭuring the optimization phase, and the table represented by a (that is, derived temporary tables are built). Subqueries in the FROM clause are executed Subqueries in theįROM clause cannot be correlated subqueries, Subqueries in the FROM clause can return a ( sum_column1) is recognized in the outer Notice that the column name used within the subquery However, this query provides the desired information: SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1 Here is another example: Suppose that you want to know theĪverage of a set of sums for a grouped table. Here is how to use a subquery in the FROMįROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb Any columns inįor the sake of illustration, assume that you have this table:ĬREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT) Ĭlause is mandatory, because every table in aįROM clause must have a name.
0 Comments
Leave a Reply. |