MySQL "With" Clause

How do you use the WITH clause in MySQL?

MySQL prior to version 8.0 doesn't support the WITH clause (CTE in SQL Server parlance; Subquery Factoring in Oracle), so you are left with using:

• TEMPORARY tables
• DERIVED tables
• inline views (effectively what the WITH clause represents - they are interchangeable)

The request for the feature dates back to 2006.

As mentioned, you provided a poor example - there's no need to perform a subselect if you aren't altering the output of the columns in any way:

  SELECT * 
    FROM ARTICLE t
    JOIN USERINFO ui ON ui.user_userid = t.article_ownerid
    JOIN CATEGORY c ON c.catid =  t.article_categoryid
   WHERE t.published_ind = 0
ORDER BY t.article_date DESC 
   LIMIT 1, 3

Here's a better example:

SELECT t.name,
       t.num
  FROM TABLE t
  JOIN (SELECT c.id
               COUNT(*) 'num'
          FROM TABLE c
         WHERE c.column = 'a'
      GROUP BY c.id) ta ON ta.id = t.id

query not working only when inside WITH ... AS statement

CTE expressions is not supported before MySQL 8.0
You can use sub-queries instead:

SELECT * FROM (
    SELECT * FROM Station WHERE Station.stationProvider = 'olympia_odos'
) AS company_stations;

Error using WITH clause and UPDATE in MySql

Defining a CTE using the WITH clause does not implicitly join that CTE to other tables in your query.

You would need to use a JOIN in your query to the CTE:

WITH updateables AS (
    SELECT id_product, quantity
    FROM products_order
    WHERE id_order = 1239 AND state = 10
)
UPDATE product JOIN updateables ON product.id_product = updateables.id_product
SET product.stock = updateables.quantity;

mySQL version of 'with' clause

You can do it with a sub-select:

select *
from (
  select long_string
  from longstring
  where hash = 'searchhash'
) AS dataset
where long_string = 'searchstring'

---

Related Topics