A derived table only lives for the life of a single query. It is created by the user and populated with a SELECT statement. The derived table must always have a name and require an alias for any calculated columns, such as those used for aggregation.

Most derived tables join to other tables so they can deliver calculated columns. Below, we want to calculate the SUM(ORDER_TOTAL) to identify which customers have paid the most.

You can create a derived table before the first select with a WITH statement or create it anywhere after the first FROM clause. We created the derived table below using the INNER JOIN keywords after the first FROM clause.

Please review the following items in the derived table query below (in bold). First, I indented the derived table so it stands out a bit better. You don’t have to do this, but it is a best practice for clarity. Second, think of a derived table joining exactly like a normal join (between two tables), but you create the derived table on the fly and join it with another table.


SELECT
ORDER_NUMBER
,O.CUSTOMER_NUMBER
,ORDER_DATE
,ORDER_TOTAL
, SUM_ORDER — this column comes from the derived table created below
FROM SQL_CLASS.ORDER_TABLE O — ORDER_TABLE aliased as O
INNER JOIN — create the derived table below
(SELECT
CUSTOMER_NUMBER,
SUM(ORDER_TOTAL) SUM_ORDER — Alias all calculations in a derived table
FROM SQL_CLASS.ORDER_TABLE
GROUP BY CUSTOMER_NUMBER) AS MY_DERIVED_TABLE
ON O.CUSTOMER_NUMBER = MY_DERIVED_TABLE.CUSTOMER_NUMBER
ORDER BY O.CUSTOMER_NUMBER;

— result set below

The derived table below is the same as the one above but joins using a traditional join, not an ANSI join. A comma separates the two tables, and we changed the ON clause to a WHERE clause.

SELECT
ORDER_NUMBER
,O.CUSTOMER_NUMBER
,ORDER_DATE
,ORDER_TOTAL
,SUM_ORDER — this column comes from the derived table created below
FROM SQL_CLASS.ORDER_TABLE O, — A comma separates the two tables
(SELECT
CUSTOMER_NUMBER,
SUM(ORDER_TOTAL) SUM_ORDER
FROM SQL_CLASS.ORDER_TABLE
GROUP BY CUSTOMER_NUMBER) AS MY_DERIVED_TABLE
WHERE O.CUSTOMER_NUMBER = MY_DERIVED_TABLE.CUSTOMER_NUMBER
ORDER BY O.CUSTOMER_NUMBER;

— same result set below

When you create a derived table, you will have two SELECT statements. One SELECT populates the derived table. The other SELECT uses the derived table in the query.

Below, we create the derived table using a WITH statement. If you only highlight the WITH statement where you created the derived table and execute it, it will fail.

You must create the derived table and then run the entire SQL to select from it.

A WITH statement creates the derived table at the beginning of the SQL statement. It is then queried or joined in the SELECT below. The query works when you create the WITH statement and then SELECT from it below.

Notice in our query that we have two SELECT statements. The first SELECT materializes the table, populating it with data. The second SELECT statement uses the derived table in a query, in this case, a query joining two tables.

WITH MY_DERIVED_TABLE AS — The derived table is created first
(SELECT
CUSTOMER_NUMBER
,SUM(ORDER_TOTAL) as SUM_ORDER
,COUNT(*) as COUNT_ORDERS
,MAX(ORDER_TOTAL) as MAX_ORDER
FROM SQL_CLASS.ORDER_TABLE
GROUP BY CUSTOMER_NUMBER)
— I separated the derived table and the SELECT, but these are run all together
SELECT
ORDER_NUMBER
,O.CUSTOMER_NUMBER
,ORDER_DATE
,ORDER_TOTAL
,SUM_ORDER — this column comes from the derived table created above
,COUNT_ORDERS
,MAX_ORDER
FROM SQL_CLASS.ORDER_TABLE O
INNER JOIN
MY_DERIVED_TABLE
ON O.CUSTOMER_NUMBER = MY_DERIVED_TABLE.CUSTOMER_NUMBER
ORDER BY O.CUSTOMER_NUMBER;

I will now create a join with three derived tables, including two, using the WITH technique and the other in the lower part of the query. But first, I will run a three-table join and then add the three derived tables. The three table join is below.

SELECT
C.SUBSCRIBER_NO
,C.CLAIM_DATE
,C.CLAIM_AMT
,P.PROVIDER_CODE
,P.P_ERROR_RATE
,S.SERVICE_CODE
,S.SERVICE_PAY
FROM SQL_CLASS.CLAIMS C
INNER JOIN
SQL_CLASS.PROVIDERS P
ON C.PROVIDER_NO = P.PROVIDER_CODE
INNER JOIN
SQL_CLASS.SERVICES S
ON S.SERVICE_CODE = C.CLAIM_SERVICE
ORDER BY SUBSCRIBER_NO, CLAIM_DATE;

I will now join three derived tables to the previous join. We created two derived tables using WITH. Notice that there is only one WITH keyword. The second WITH table merely has a comma and the name.

Because we are joining each derived table back to claims, we had to create the second and third derived tables using a join.

The complex query below provides a great lesson for creating derived tables. Because we have three derived tables, we will have four SELECT statements. Three statements are needed to materialize the derived tables, and one is to SELECT from them all.

WITH FIRST_DERIVED AS
(SELECT SUBSCRIBER_NO
,SUM(CLAIM_AMT) as SUM_CLAIMS
FROM SQL_CLASS.CLAIMS
GROUP BY 1)

,SECOND_DERIVED AS
(SELECT SUBSCRIBER_NO
,AVG(P_ERROR_RATE) as AVG_P_ERROR
FROM SQL_CLASS.PROVIDERS
INNER JOIN
SQL_CLASS.CLAIMS
ON CLAIMS.PROVIDER_NO = PROVIDERS.PROVIDER_CODE
GROUP BY 1)

SELECT
C.SUBSCRIBER_NO
,C.CLAIM_DATE
,C.CLAIM_AMT
,P.PROVIDER_CODE
,P.P_ERROR_RATE
,S.SERVICE_CODE
,S.SERVICE_PAY
,FIRST_DERIVED.SUM_CLAIMS
,CAST(SECOND_DERIVED.AVG_P_ERROR as DECIMAL(6,4)) as AVG_P_ERROR
,THIRD_DERIVED.SUM_SER_PAY
FROM SQL_CLASS.CLAIMS C
INNER JOIN
SQL_CLASS.PROVIDERS P
ON C.PROVIDER_NO = P.PROVIDER_CODE
INNER JOIN
SQL_CLASS.SERVICES S
ON S.SERVICE_CODE = C.CLAIM_SERVICE
INNER JOIN
(SELECT SUBSCRIBER_NO
,SUM(SERVICE_PAY) as SUM_SER_PAY
FROM SQL_CLASS.CLAIMS
INNER JOIN SERVICES
ON CLAIM_SERVICE = SERVICE_CODE
GROUP BY 1) AS THIRD_DERIVED
ON THIRD_DERIVED.SUBSCRIBER_NO = C.SUBSCRIBER_NO
INNER JOIN
FIRST_DERIVED
ON FIRST_DERIVED.SUBSCRIBER_NO = C.SUBSCRIBER_NO
INNER JOIN
SECOND_DERIVED
ON SECOND_DERIVED.SUBSCRIBER_NO = C.SUBSCRIBER_NO
ORDER BY C.SUBSCRIBER_NO, CLAIM_DATE;

Congratulations! You have just mastered derived tables on Teradata.

Posted in Blog