Why do the CTEs work different from the Subqueries in MySQL?

Question

I try deleting all the goods, whose type_name starts with D.

Let's say I wanna delete goods from the types of dentistry, delicacies, darts_game. So I check whether the type is in the list d_goods_ids (type is a FK of good_type_id).

WITH d_goods_ids AS (SELECT good_type_id FROM GoodTypes 
                 WHERE GoodTypes.good_type_name LIKE "d%")

DELETE FROM Goods
WHERE type IN d_goods_ids;

SELECT * FROM Goods;

But MySQL raises an error:

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd_goods_ids; SELECT * FROM Goods' at line 5

Whereas if I use a subquery, everything works perfectly:

DELETE FROM Goods
WHERE type IN (SELECT good_type_id FROM GoodTypes 
               WHERE GoodTypes.good_type_name LIKE "d%");

SELECT * FROM Goods;

Shouldn't both variants work the same in this context?

Answer 1

You can reference a CTE in a subquery, but not like you are trying to do it.

WITH d_goods_ids AS (SELECT good_type_id FROM GoodTypes
                 WHERE GoodTypes.good_type_name LIKE "d%")

DELETE FROM Goods
WHERE type IN (SELECT good_type_id FROM d_goods_ids);

In this example, the CTE is in a subquery, referenced in a FROM clause as if it's a table name.

https://dev.mysql.com/doc/refman/8.4/en/with.html says:

cte_name names a single common table expression and can be used as a table reference in the statement containing the WITH clause.

(bold emphasis is mine)

So you can use a CTE where you would otherwise use a table reference.

Another way of thinking about the syntax is that you can't substitute an isolated table name for a subquery.

In other words, the following example is not a valid IN predicate, for the same reason using a CTE in that place is not valid:

DELETE FROM Goods
WHERE type IN GoodTypes;