MySQL "WITH" clause
I'm trying to use MySQL to create a view with the "WITH" clause
WITH authorRating(aname, rating) AS
SELECT aname, AVG(quantity)
FROM book
GROUP BY aname
But it doesn't seem like MySQL supports this.
I thought this was pretty standard and I'm sure Oracle supports this. Is there anyway to force MySQL to use the "WITH" clause? I've tried it with the MyISAM and innoDB engine. Both of these don't work.
Answer
Update: MySQL 8.0 is finally getting the feature of common table expressions, including recursive CTEs.
Here's a blog announcing it: http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/
Below is my earlier answer, which I originally wrote in 2008.
MySQL 5.x does not support queries using the WITH syntax defined in SQL-99, also called Common Table Expressions.
This has been a feature request for MySQL since January 2006: http://bugs.mysql.com/bug.php?id=16244
Other RDBMS products that support common table expressions:
- Oracle 9i release 2 and later:
http://www.oracle-base.com/articles/misc/with-clause.php - Microsoft SQL Server 2005 and later:
http://msdn.microsoft.com/en-us/library/ms190766(v=sql.90).aspx - IBM DB2 UDB 8 and later:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000879.htm - PostgreSQL 8.4 and later:
https://www.postgresql.org/docs/current/static/queries-with.html - Sybase 11 and later:
http://dcx.sybase.com/1100/en/dbusage_en11/commontblexpr-s-5414852.html - SQLite 3.8.3 and later:
http://sqlite.org/lang_with.html - HSQLDB:
http://hsqldb.org/doc/guide/dataaccess-chapt.html#dac_with_clause - Firebird 2.1 and later (the first Open Source DBMS to support recursive queries): http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes210.html#rnfb210-cte
- H2 Database (but only recursive):
http://www.h2database.com/html/advanced.html#recursive_queries - Informix 14.10 and later: https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_with.htm