Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL Tutorial - Using Subqueries in the FROM Clause

By: FYIcenter.com

(Continued from previous topic...)

How To Use Subqueries in the FROM clause?

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)

(Continued on next topic...)

  1. How To Join Two Tables in a Single Query?
  2. How To Write a Query with an Inner Join?
  3. How To Define and Use Table Alias Names?
  4. How To Write a Query with a Left Outer Join?
  5. How To Write a Query with a Right Outer Join?
  6. How To Write a Query with a Full Outer Join?
  7. How To Write an Inner Join with the WHERE Clause?
  8. How To Name Query Output Columns?
  9. What Is a Subquery?
  10. How To Use Subqueries with the IN Operator?
  11. How To Use Subqueries with the EXISTS Operator?
  12. How To Use Subqueries in the FROM clause?
  13. How To Count Groups Returned with the GROUP BY Clause?
  14. How To Return Top 5 Rows?
  15. How To Return the Second 5 Rows?
  16. How To Use UNION to Merge Outputs from Two Queries Together?

MySQL Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...