# Expression de table communeCommon table expression

Une expression de table commune (CTE) définit un jeu de résultats temporaire qu’un utilisateur peut référencer éventuellement plusieurs fois dans l’étendue d’une instruction SQL.A common table expression (CTE) defines a temporary result set that a user can reference possibly multiple times within the scope of a SQL statement. Une CTE est utilisée principalement dans une instruction SELECT.A CTE is used mainly in a SELECT statement.

## SyntaxeSyntax

``````WITH common_table_expression [ , ... ]
``````

Tandis que `common_table_expression` est défini commeWhile `common_table_expression` is defined as

``````expression_name [ ( column_name [ , ... ] ) ] [ AS ] ( query )
``````

## ParamètresParameters

• expression_nameexpression_name

Spécifie le nom de l’expression de table commune.Specifies a name for the common table expression.

• requêtequery

## ExemplesExamples

``````-- CTE with multiple column aliases
WITH t(x, y) AS (SELECT 1, 2)
SELECT * FROM t WHERE x = 1 AND y = 2;
+---+---+
|  x|  y|
+---+---+
|  1|  2|
+---+---+

-- CTE in CTE definition
WITH t AS (
WITH t2 AS (SELECT 1)
SELECT * FROM t2
)
SELECT * FROM t;
+---+
|  1|
+---+
|  1|
+---+

-- CTE in subquery
SELECT max(c) FROM (
WITH t(c) AS (SELECT 1)
SELECT * FROM t
);
+------+
|max(c)|
+------+
|     1|
+------+

-- CTE in subquery expression
SELECT (
WITH t AS (SELECT 1)
SELECT * FROM t
);
+----------------+
|scalarsubquery()|
+----------------+
|               1|
+----------------+

-- CTE in CREATE VIEW statement
CREATE VIEW v AS
WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4)
SELECT * FROM t;
SELECT * FROM v;
+---+---+---+---+
|  a|  b|  c|  d|
+---+---+---+---+
|  1|  2|  3|  4|
+---+---+---+---+

-- If name conflict is detected in nested CTE, then AnalysisException is thrown by default.
-- SET spark.sql.legacy.ctePrecedencePolicy = CORRECTED (which is recommended),
-- inner CTE definitions take precedence over outer definitions.
SET spark.sql.legacy.ctePrecedencePolicy = CORRECTED;
WITH
t AS (SELECT 1),
t2 AS (
WITH t AS (SELECT 2)
SELECT * FROM t
)
SELECT * FROM t2;
+---+
|  2|
+---+
|  2|
+---+
``````