In a recent labs
release a new feature was introduced by Oracle, or actually
two very related new features were introduced. The first new
feature is Common Table Expressions (CTEs), which is also known
WITH. The second feature is recursive CTEs, also
An example of
WITH non_root_users AS (SELECT User, Host FROM mysql.user WHERE User<>'root')
SELECT Host FROM non_root_users WHERE User = ?
The non-CTE equivalent is this:
SELECT Host FROM
(SELECT User, Host FROM mysql.user WHERE User<>'root') non_root_users
WHERE User = ?
This makes it easier to understand the query, especially if there are many subqueries.
Besides using regular subqueries or CTEs you could also put the subquery in a view, but this requires more …[Read more]
There is a new feature in the MariaDB 10 Beta which caught my
eye: support for returning a result set on delete.
With a 'regular' DELETE operation you only get to know the number of affected rows. To get more info or actions you have to use a trigger or a foreign key. Anoter posibility is doing a SELECT and then a DELETE and with the correct transaction isolation a transactional support this will work.
With the support for the RETURNING keyword this has become easier to do and it will probably bennefit performance and save you a few roundtrips and a few lines of code.
There is already support for RETURNING in PostgreSQL. And PostgreSQL has an other nifty feature for which RETURNING really helps: CTE or common table expressions or the WITH keyword. I really hope to see CTE support in MySQL or MariaDB some day.