WITH...AS
Function
This statement is used to define the common table expression (CTE) using WITH...AS to simplify the query and make the result easier to read and maintain.
Syntax
1 |
WITH cte_name AS (select_statement) sql_containing_cte_name;
|
Keyword
- cte_name: Name of a public expression. The name must be unique.
- select_statement: complete SELECT clause.
- sql_containing_cte_name: SQL statement containing the defined common expression.
Precautions
- A CTE must be used immediately after it is defined. Otherwise, the definition becomes invalid.
- Multiple CTEs can be defined by WITH at a time. The CTEs are separated by commas and the CTEs defined later can quote the CTEs defined earlier.
Example
Define SELECT courseId FROM course_info WHERE courseName = 'Biology' as CTE nv and use nv as the SELECT statement in future queries.
1 |
WITH nv AS (SELECT courseId FROM course_info WHERE courseName = 'Biology') SELECT DISTINCT courseId FROM nv;
|
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.