MySQL: view with subquery in FROM FROM constraint

Question

In MySQL 5.0, why the following error occurs when trying to create a view with a subquery in the FROM clause?

ERROR 1349 (HY000): SELECT view contains subquery in FROM clause

If this is a limitation for the MySQL engine, then why haven't they implemented this function yet?

Also, what are some good ways to get around this limitation?

Are there any workarounds that work for any subquery in the FROM clause, or are there some queries that cannot be expressed without using the subquery in the FROM clause?

---

Request example (was buried in a comment):

SELECT temp.UserName FROM (SELECT u1.name as UserName, COUNT(m1.UserFromId) as SentCount FROM Message m1, User u1 WHERE u1.uid = m1.UserFromId Group BY u1.name HAVING SentCount > 3 ) as temp 

Answer 1

Could the request in your comment just be written as:

 SELECT u1.name as UserName from Message m1, User u1 WHERE u1.uid = m1.UserFromID GROUP BY u1.name HAVING count(m1.UserFromId)>3 

This should also help with known speed issues with subqueries in MySQL.

Answer 2

I had the same problem. I wanted to create a view to show information for the last year, from a table with entries from 2009 to 2011. Here's the original request:

 SELECT a.* FROM a JOIN ( SELECT a.alias, MAX(a.year) as max_year FROM a GROUP BY a.alias ) b ON a.alias=b.alias and a.year=b.max_year 

Solution outline:

• create a view for each subquery
• replace subqueries with these views

Here's a solution request:

 CREATE VIEW v_max_year AS SELECT alias, MAX(year) as max_year FROM a GROUP BY a.alias; CREATE VIEW v_latest_info AS SELECT a.* FROM a JOIN v_max_year b ON a.alias=b.alias and a.year=b.max_year; 

It works fine on mysql 5.0.45 without a significant speed penalty (compared to execution, the original subquery is selected without any lookups).

Answer 3

This is a known issue.

http://dev.mysql.com/doc/refman/5.1/en/unnamed-views.html

http://bugs.mysql.com/bug.php?id=16757

Many IN queries can be rewritten with both (left outer) joins and IS (NOT) NULLs. eg

 SELECT * FROM FOO WHERE ID IN (SELECT ID FROM FOO2) 

can be rewritten as

 SELECT FOO.* FROM FOO JOIN FOO2 ON FOO.ID=FOO2.ID 

or

 SELECT * FROM FOO WHERE ID NOT IN (SELECT ID FROM FOO2) 

may be

 SELECT FOO.* FROM FOO LEFT OUTER JOIN FOO2 ON FOO.ID=FOO2.ID WHERE FOO.ID IS NULL 

Answer 4

creating a view for each subquery is the way to go. It works like a charm.

Answer 5

You can get around this by creating a separate VIEW for any subquery that you want to use, and then join what you are creating. Here is an example: http://blog.gruffdavies.com/2015/01/25/a-neat-mysql-hack-to-create-a-view-with-subquery-in-the-from-clause/

This is very convenient, as you will most likely want to use it again and be able to save your SQL DRY.