Occasionally at conference or a Meetup, someone will approach me
and ask me for help with a MySQL problem. Eight out of ten
times their difficulty includes a sub query. "I get an error
message about a corrugated or conflabugated sub query or some
such," they say, desperate for help. Usually with a
bit of fumbling we can get their problem solved. The
problem is not a lack of knowledge for either of us but that sub
queries are often hard to write.
MySQL 8 will be the first version of the most popular database on the web with Common Table Expressions or CTEs. CTEs are a way to create temporary tables and then use that temporary table for queries. Think of them as easy to write sub queries!
WITH is The Magic WordThe new CTE magic is indicated with the WITH clause.
mysql> WITH myfirstCTE[Read more]
AS (SELECT * FROM world.city WHERE …