How To Use Subqueries in the FROM clause in MySQL?

If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. A subquery used in this way become a temporary table, and you must provide a table alias name for the subquery as in "SELECT ... FROM (SELECT ...) aliasName". The following statement shows you how to use a subquery as base table for the main query:

mysql> SELECT * FROM (SELECT l.id, l.url, r.comment
   FROM fyi_links l LEFT OUTER JOIN fyi_rates r 
   ON l.id = r.id) WHERE url LIKE '%er%';
ERROR 1248 (42000): Every derived table must have its own alias

mysql> SELECT * FROM (SELECT l.id, l.url, r.comment
   FROM fyi_links l LEFT OUTER JOIN fyi_rates r 
   ON l.id = r.id) s WHERE s.url LIKE '%er%';
+-----+-------------------+-----------+
| id  | url               | comment   |
+-----+-------------------+-----------+
| 101 | dev.fyicenter.com | The best  |
| 102 | dba.fyicenter.com | Well done |
| 103 | sqa.fyicenter.com | Thumbs up |
| 107 | www.winrunner.com | NULL      |
+-----+-------------------+-----------+
4 rows in set (0.06 sec)

⇒ Counting Groups Returned from GROUP BY in MySQL

⇐ Using Subqueries with the EXISTS Operator in MySQL

⇑ SELECT Statements with JOIN and Subqueries in MySQL

⇑⇑ MySQL Database Tutorials