Press "Enter" to skip to content

Common Table Expressions in MySQL

Robert Sheldon looks at the syntax for common table expressions in MySQL:

As with many relational database management systems, MySQL provides a variety of methods for combining data in a data manipulation language (DML) statement. You can join multiple tables in a single query or add subqueries that pull data in from other tables. You can also access views and temporary tables from within a statement, often along with permanent tables.

MySQL also offers another valuable tool for working with data—the common table expression (CTE). A CTE is a named result set that you define in a WITH clause. The WITH clause is associated with a single DML statement but is created outside the statement. However, only that statement can access the result set.

The syntax is very similar to that of SQL Server save for an explicit RECURSIVE clause rather implicit recursion as in T-SQL.