Menu
I am trying to join a common table expression to an existing table (table1) as follows.
select column1, column2 from table1
left outer join
;with cte as (
select column1, column2 from table2)
select column1, column2 from cte
on table1.column1 = cte.column1
The errors are:
What am I doing wrong? Should I be using a CTE for this task?
499
All replies. CTEs can't be created within a SELECt query. As correctly suggested take the CTE definition outside the JOIN and use the CTE name in the JOIN. CTE definition that starts with WITH should not be preceded by any other statement, so as per the general practice, always use a semi colon before WITH.
When you define a CTE you're doing so before any of the rest of the query. So you can't write: LEFT JOIN ( ;WITH CTE ... ) And as long as one CTE is defined before others, it can be referred to within the CTEs that follow.
Multiple CTE query definitions can be defined in a CTE. A CTE must be followed by a single SELECT statement. INSERT , UPDATE , DELETE , and MERGE statements aren't supported.
The performance of CTEs and subqueries should, in theory, be the same since both provide the same information to the query optimizer. One difference is that a CTE used more than once could be easily identified and calculated once. The results could then be stored and read multiple times.
The CTE must come at the beginning of the query.
with cte as (
select column1, column2 from table2
)
select column1, column2 from table1
LEFT JOIN cte
on table1.column1 = cte.column1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
