Updated on 2024-01-23 GMT+08:00

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;