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

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)