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 = 0ORDER 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

Mysql Developers Team announced that version 8.0 will have Common Table Expressions in MySQL (CTEs). So it will be possible to write queries like this:

WITH RECURSIVE my_cte AS(  SELECT 1 AS n  UNION ALL  SELECT 1+n FROM my_cte WHERE n<10)SELECT * FROM my_cte;+------+| n    |+------+|    1 ||    2 ||    3 ||    4 ||    5 ||    6 ||    7 ||    8 ||    9 ||   10 |+------+10 rows in set (0,00 sec)

In Sql the with statement specifies a temporary named result set, known as a common table expression (CTE). It can be used for recursive queries, but in this case, it specifies as subset. If mysql allows for subselectes i would try

select t1.* from  (            SELECT  article.*,                     userinfo.*,                     category.*             FROM    question INNER JOIN                     userinfo ON userinfo.user_userid=article.article_ownerid INNER JOIN category ON article.article_categoryid=category.catid            WHERE   article.article_isdeleted = 0     ) t1ORDER BY t1.article_date DESC Limit 1, 3