Common Table Expression In SQL | Examples

      No Comments on Common Table Expression In SQL | Examples

The use of a subquery (sub-select) statement in T-SQL queries is common but can cause slow performance as the “subquery” needs to to be evaluated first. We can use a common table expressions (CTE) in place as a temporary result set to work with. The code can also be reused in other procedures as opposed to placing temp tables or sub-queries where needed.

WITH ExpressionName (Field1, Field2, Field3) AS
(SELECT Column1, Column2, Column3 FROM MyTable)

SELECT Field1, Field2, Field3 FROM ExpressionName

The CTE does have limitations but the results can be used in SELECT, INSERT, UPDATE and DELETE statements. Also, if you are unable to create or gain access to a VIEW this can be a replacement in your statement.

Simple CTE Example

To demonstrate the structure here is a small example where we need to pull Car Dealership identifier into a query where we are unable to use a field in a JOIN with the [CarDealers] table. In this example we are querying the [CarDealers] table using a CTE so we have a list of each Dealer ID in the state of Utah. Using a suquery the statement might look like this:

SELECT Region, State, DealerID 
FROM AllLocations 
WHERE State IN (SELECT State FROM Car_Dealers_US WHERE State = 'UT')

We can write a CTE instead to improve the statement readability and make this result set available throughout the query without repeating the subquery where needed.

WITH CarDealers (DealerID, City, State) AS
(SELECT ID, City, StateAbbrev FROM Car_Dealers_US WHERE State = 'UT')

SELECT Region, State, DealerID 
FROM AllLocations 
LEFT JOIN CarDealers
ON CarDealers.State = AllLocations.State

We are now only returning the Region and Dealer ID’s that exist in the state of “Utah”.

Advantages of CTE

  • Replace subqueries (WHERE NOT IN, IN)
  • Can use SELECT, INSERT, DELETE, UPDATE, MERGE
  • Reusable code
  • able to use recursion for parent child or hierarchical lists

Disadvantages of CTE

For non-recursive CTE’s there are also limitations.

  • Cannot use clauses: ORDER BY (except using TOP), INTO, OPTION, FOR BROWSE
  • “CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement”
  • Using more than 1 CTE they must be join using: UNION ALL, UNION, EXCEPT, or INTERSECT