MySQL "WITH" clause

Question

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

You might be interested in somethinkg like this:

select * from (
    select * from table
) as Subquery

You've got the syntax right:

WITH AuthorRating(AuthorName, AuthorRating) AS
   SELECT aname         AS AuthorName,
          AVG(quantity) AS AuthorRating
   FROM Book
   GROUP By Book.aname

However, as others have mentioned, MySQL does not support this command. WITH was added in SQL:1999; the newest version of the SQL standard is SQL:2008. You can find some more information about databases that support SQL:1999's various features on Wikipedia.

MySQL has traditionally lagged a bit in support for the SQL standard, whereas commercial databases like Oracle, SQL Server (recently), and DB2 have followed them a bit more closely. PostgreSQL is typically pretty standards compliant as well.

You may want to look at MySQL's roadmap; I'm not completely sure when this feature might be supported, but it's great for creating readable roll-up queries.

Oracle does support WITH.

It would look like this.

WITH emps as (SELECT * FROM Employees)
SELECT * FROM emps WHERE ID < 20
UNION ALL
SELECT * FROM emps where Sex = 'F'

@ysth WITH is hard to google because it's a common word typically excluded from searches.

You'd want to look at the SELECT docs to see how subquery factoring works.

I know this doesn't answer the OP but I'm cleaning up any confusion ysth may have started.

Building on the answer from @Mosty Mostacho, here's how you might do something equivalent in MySQL,for a specific case of determining what entries don't exist in a table, and are not in any other database.

select col1 from (
   select 'value1' as col1 union
   select 'value2' as col1 union
   select 'value3' as col1
) as subquery
left join mytable as mytable.mycol = col1
where mytable.mycol is null
order by col1

You may want to use a text editor with macro capabilities to convert a list of values to the quoted select union clause.