Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: with 表达式
Is the execution process the same for “with aa as ( xxx ) select * from aa;” and “select * from ( xxx );”?
Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: with 表达式
Is the execution process the same for “with aa as ( xxx ) select * from aa;” and “select * from ( xxx );”?
It won’t create a temporary table, for example, create temporary table aa (xxxx).
Check the release notes after version 6.1.1 for any bug fixes related to CTE.
Please refer to the official documentation,
If you encounter any bugs, feel free to “complain” on the forum.
The WITH statement is a commonly used syntax in SQL for creating temporary tables or views to be used in subsequent queries. It can simplify the writing of complex queries and improve query performance.
The functionality of CTE has no significant bugs. Versions 6.1.7 or 6.5.x are more suitable as most issues have been fixed.
Refer to these fixes:
The WITH clause is not particularly well supported in TiDB yet. It’s best to use version 7 or above; I haven’t tested this myself. However, based on my previous tests in Oracle, it should be the same.
The execution plan is different, use lower versions with caution.
It might be more reliable to change it to a subquery. Is this about migrating to TiDB?