fbpx

COMMON TABLE EXPRESSIONS IN SQL

วิธีการรับมือกับการเปลี่ยนแปลงในการงาน และ ชีวิต

24 Feb COMMON TABLE EXPRESSIONS IN SQL

Posted at 11:34h in SQL, Technology News by
0 Likes

In SQL Server 2005, Microsoft introduced the Common Table Expression (CTE). CTEs share similarities with VIEWS and derived tables, but are really not the same as either. Oracle SQL also supports CTEs and while the syntax is basically the same, some of the properties that we’ll discuss may be slightly different.

Let’s take a look at a very simple CTE to get started.

CREATE TABLE #Vehicles
(
    VehicleID VARCHAR(5)
    ,VehicleType VARCHAR(5)
    ,Location VARCHAR(3)
    ,PRIMARY KEY (VehicleID)
);

INSERT INTO #Vehicles
VALUES ('12211', 'TRUCK', 'BKK'),('12212', 'CAR', 'BKK')
    ,('12213', 'TRUCK', 'CNX'),('12214', 'CAR', 'CNX'),
    ('12215', 'TRUCK', 'HDY'),('12216', 'CAR', 'HDY');

WITH BKKVehicles AS
(
    SELECT VehicleID, VehicleType, Location
        ,rn=ROW_NUMBER() OVER (ORDER BY VehicleID)
    FROM #Vehicles
    WHERE Location = 'BKK'
)
SELECT VehicleID, VehicleType, Location
FROM BKKVehicles;

Our CTE begins with the keyword WITH and ends at the closing parenthesis. Below the CTE is what I’ll call the “main query.” This CTE retrieves only vehicles whose location is BKK and adds a ROW_NUMBER to that result:

VehicleID  VehicleType  Location  rn
12211      TRUCK        BKK       1
12212      CAR          BKK       2

There is a widespread belief that CTEs can improve performance, but the truth is they neither improve nor detract from performance. They are simply a way to make your code more readable, although they do offer a couple of things that may also make your life a bit easier.
Let’s look at some of the rules/properties of a CTE, comparing and contrasting with VIEWs and derived tables where appropriate.

  • You must remember to terminate the statement preceding the CTE with a semicolon, otherwise SQL will throw this error message at you:
    “Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.”
  • You can rename the columns returned by the CTE by providing the column names between parentheses immediately after the name of the CTE (our CTE is named BKKVehicles) and before “AS.”
  • A CTE must contain a SELECT and it may not contain INSERT, UPDATE, DELETE or MERGE statements.
  • CTEs will inherit the indexing of the tables upon which they are based.
  • CTEs are more like a derived table than a VIEW because they exist only for the life of the main query which follows them. In order to reuse a CTE in a subsequent query, you must resupply the same code to the second query.
  • You can use a CTE as source or target in UPDATE, INSERT, DELETE and MERGE queries, but there are some restrictions. This is similar to a VIEW.
  • You may have more than one CTE associated with a query. When more than one CTE is defined, they are referred to as “cascaded” or “stacked” CTEs. You may not however nest CTEs within CTEs.
  •  You may code CTEs within VIEWs, FUNCTIONs or Stored Procedures.
  • You may refer to a CTE more than once in the main query. Contrast this with a derived table, which if you’d like to use it more than once, must be coded as many times as you need it.
  • You may refer to a CTE in another CTE as long as the CTE being referred to occurs above the CTE that is doing the referring in the CTE stack.
  • CTEs are the only construct in T-SQL that supports recursive queries.

CTEs are most often used with SELECT, but you can UPDATE through a CTE also.

WITH BKKVehicles AS
(
    SELECT VehicleID, VehicleType, Location
        ,rn=ROW_NUMBER() OVER (ORDER BY VehicleID)
    FROM #Vehicles
    WHERE Location = 'BKK'
)
UPDATE BKKVehicles
SET VehicleType = 'VAN'
WHERE Location = 'HDY';

In this example, no rows are updated because the table returned by the CTE does not contain any rows whose location is HDY.

Likewise you can delete through a CTE.

WITH BKKVehicles AS
(
    SELECT VehicleID, VehicleType, Location
        ,rn=ROW_NUMBER() OVER (ORDER BY VehicleID)
    FROM #Vehicles
    WHERE Location = 'BKK'
)
DELETE FROM BKKVehicles
WHERE rn > 1;

SELECT *
FROM #Vehicles;

After the DELETE runs, the rows remaining in our table are:

VehicleID   VehicleType   Location
12211       TRUCK         BKK
12213       TRUCK         CNX
12214       CAR           CNX
12215       TRUCK         HDY
12216       CAR           HDY

This is actually quite a useful method of deleting duplicate rows from a table.

We mentioned that when updating or deleting through a CTE, certain restrictions may apply. Basically all that means is that the target rows in the target table must be unambiguous. For example, if you happen to JOIN the target table with another table, the JOIN must be exact (no duplicate rows generated) otherwise the effort will likely fail. Highly complex queries involving many JOINs, etc. may also confuse the compiler and make it unable to recognize the target table.

Here’s an example of using a CTE as the source for an INSERT, to generate some additional sample rows in our table. It also demonstrates how you can name the columns generated by the CTE.

WITH MoreRows (VehicleID, VehicleType, Location) AS
(
    SELECT '12218','VAN','BKK'
    UNION ALL SELECT '12219','VAN','CNX'
    UNION ALL SELECT '12220','VAN','HDY'
)
INSERT INTO #Vehicles (VehicleID, VehicleType, Location)
SELECT VehicleID, VehicleType, Location
FROM MoreRows;

A CTE can also act as either source or target tables for a MERGE, but since MERGE is a topic that is deserving of consideration on its own, we’ll do a separate blog entry for that.

Final Remarks

We have demonstrated how a Common Table Expression can be used in SELECT, UPDATE, DELETE and INSERT statements. CTEs are basically a way to improve the readability of the SQL code you produce, having no impact on their performance.

While we have mentioned that CTEs can be used for recursive queries, we haven’t provided any examples of this because it is quite an advanced topic. However if you are interested in how to do this, you might want to take a look at Exploring Recursive CTEs by Example.