MySQL View with Subquery in the FROM Clause Limitation

Question

In MySQL 5 0 why does the following error occur when trying to create a view with a subquery in the FROM clause      ERROR 1349  HY000   View s SELECT contains a subquery in the FROM clause   If this is a limitation of the MySQL engine  then why haven t they implemented this feature yet   Also  what are some good workarounds for this limitation   Are there any workarounds that work for any subquery in the FROM clause or are there some queries that can not be expressed without using a subquery in the FROM clause     An example query  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  gt  3   as temp

User · Answer

create a view for each subquery is the way to go  Got it working like a charm

User · Answer

Couldn t your query 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  gt 3   That should also help with the known speed issues with subqueries in MySQL

User · Answer

It appears to be 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 re-written as  left outer  joins and an IS  NOT  NULL of some sort  for example  SELECT   FROM FOO WHERE ID IN  SELECT ID FROM FOO2    can be re-written as  SELECT FOO   FROM FOO JOIN FOO2 ON FOO ID FOO2 ID   or  SELECT   FROM FOO WHERE ID NOT IN  SELECT ID FROM FOO2    can be  SELECT FOO   FROM FOO  LEFT OUTER JOIN FOO2  ON FOO ID FOO2 ID WHERE FOO ID IS NULL

User · Answer

Couldn t your query 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  gt 3   That should also help with the known speed issues with subqueries in MySQL

User · Answer

It appears to be 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 re-written as  left outer  joins and an IS  NOT  NULL of some sort  for example  SELECT   FROM FOO WHERE ID IN  SELECT ID FROM FOO2    can be re-written as  SELECT FOO   FROM FOO JOIN FOO2 ON FOO ID FOO2 ID   or  SELECT   FROM FOO WHERE ID NOT IN  SELECT ID FROM FOO2    can be  SELECT FOO   FROM FOO  LEFT OUTER JOIN FOO2  ON FOO ID FOO2 ID WHERE FOO ID IS NULL

User · Answer

You can work around this by creating a separate VIEW for any subquery you want to use and then join to that in the VIEW you re creating  Here s 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 quite handy as you ll very likely want to reuse it anyway and helps you keep your SQL DRY

User · Answer

I had the same problem   I wanted to create a view to show information of the most recent year  from a table with records from 2009 to 2011   Here s the original query   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   Outline of solution    create a view for each subquery replace subqueries with those views   Here s the solution query   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 much of a speed penalty  compared to executing the original sub-query select without any views

User · Answer

create a view for each subquery is the way to go  Got it working like a charm

User · Answer

Couldn t your query 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  gt 3   That should also help with the known speed issues with subqueries in MySQL

User · Answer

I had the same problem   I wanted to create a view to show information of the most recent year  from a table with records from 2009 to 2011   Here s the original query   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   Outline of solution    create a view for each subquery replace subqueries with those views   Here s the solution query   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 much of a speed penalty  compared to executing the original sub-query select without any views

User · Answer

Couldn t your query 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  gt 3   That should also help with the known speed issues with subqueries in MySQL

User · Answer

It appears to be 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 re-written as  left outer  joins and an IS  NOT  NULL of some sort  for example  SELECT   FROM FOO WHERE ID IN  SELECT ID FROM FOO2    can be re-written as  SELECT FOO   FROM FOO JOIN FOO2 ON FOO ID FOO2 ID   or  SELECT   FROM FOO WHERE ID NOT IN  SELECT ID FROM FOO2    can be  SELECT FOO   FROM FOO  LEFT OUTER JOIN FOO2  ON FOO ID FOO2 ID WHERE FOO ID IS NULL

User · Answer

It appears to be 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 re-written as  left outer  joins and an IS  NOT  NULL of some sort  for example  SELECT   FROM FOO WHERE ID IN  SELECT ID FROM FOO2    can be re-written as  SELECT FOO   FROM FOO JOIN FOO2 ON FOO ID FOO2 ID   or  SELECT   FROM FOO WHERE ID NOT IN  SELECT ID FROM FOO2    can be  SELECT FOO   FROM FOO  LEFT OUTER JOIN FOO2  ON FOO ID FOO2 ID WHERE FOO ID IS NULL

User · Answer

You can work around this by creating a separate VIEW for any subquery you want to use and then join to that in the VIEW you re creating  Here s 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 quite handy as you ll very likely want to reuse it anyway and helps you keep your SQL DRY

[mysql] MySQL: View with Subquery in the FROM Clause Limitation

Examples related to mysql

Examples related to sql

Examples related to view

Examples related to mysql-error-1349