DB2 SQL TABLE EXPRESSION

Sumit goyal

3 min read

·

May 26, 2021

--

Listen

Share

SQL TABLE EXPRESSION gives you the power to create a temporary table inside a query. The life of this temporary table is only inside that query. As soon as the query is completed temporary table will vanish.

This is a handy and powerful concept and can help you solve complex issues with straightforward queries.

There are 2 types of TABLE EXPRESSIONs in DB2 SQL

1. Nested table expressions(also called Derived tables)
2. Common table expressions

NESTED TABLE EXPRESSION

Nested table expressions(NTE) (also called derived tables)

The basic syntax of the SQL SELECT statement is like

SELECT * FROM table-name

1. Nested table expressions give you the option to replace “table-name”(after FROM clause) with “(SELECT-statement),” which acts as a temporary table.
2. To use this Temporary table in the query, we must give this Temporary table a name (correlation name).

SELECT * FROM (SELECT MAX(SALARY) AS MAXSAL, WORKDEPT FROM EMPLOYEE E1 GROUP BY WORKDEPT) AS T2

In the above query, “(SELECT MAX(SALARY) AS MAXSAL, WORKDEPT FROM CORPDATA.EMPLOYEE E1 GROUP BY WORKDEPT) AS T2” is NTE with correlation name T2.

You can use other tables with NTE in the same query.

SELECT MGRNO, T1.DEPTNO, MAXSAL FROM DEPARTMENT T1,
		(SELECT MAX(SALARY) AS MAXSAL, WORKDEPT FROM EMPLOYEE E1 GROUP BY WORKDEPT) T2
	WHERE T1.DEPTNO = T2.WORKDEPT ORDER BY DEPTNO

In the same way above, you can use multiple NTEs in a single query.

Someone can easy confuse NTE with SubQueries as both follow almost the same syntax. The point to remember here is NTE replaces “Table” in FROM clause where SubQueries give the option to use a query in WHERE and HAVING clauses.

COMMON TABLE EXPRESSION (CTE)

1. Unlike NESTED TABLE EXPRESSION, CTE is not specified in FROM clause of the query.
2. But CTE is specified before the full-select in a SELECT statement using WITH keyword.
3. A single query can contain multiple CTEs, but the keyword WITH is used only once.
4. Inside query, the CTE table is treated as any other normal table.
5. By default, CTE uses column names from the query, it is driven from(Or the system-generated name in case of some SQL Function like SUM() is used), But there is an option to rename CTE table columns.

WITH AVSAL AS
     (SELECT WORKDEPT, AVG(SALARY) as AVGSAL 
        FROM EMPLOYE  GROUP BY WORKDEPT)
SELECT WORKDEPT, MAX(AVGSAL) AS MAXAVGSAL 
FROM AVSAL GROUP BY WORKDEPT;

The above code “WITH AVSAL AS (SELECT WORKDEPT, AVG(SALARY) as AVGSAL FROM EMPLOYEE GROUP BY WORKDEPT)” defines a CTE with the name AVSAL means now there is a temporary table with the name AVSAL available to this query.

In the next line, “SELECT WORKDEPT, MAX(AVGSAL) AS MAXAVGSAL FROM AVSAL GROUP BY DEPT,” a select statement is used to get data from CTE table AVSAL like any other table.

Rename columns for CTE

WITH AVSAL(DEPARTMENT,AVGSALARY) AS
(SELECT   WORKDEPT, AVG(SALARY) as AVGSAL  
FROM  EMPLOYE  GROUP BY WORKDEPT)
SELECT DEPARTMENT, MAX(AVGSALARY) AS MAXAVGSAL 
FROM AVSAL GROUP BY DEPARTMENT;

The above query “WITH AVSAL(DEPARTMENT, AVGSALARY) AS” renames the CTE table AVSAL’s columns to DEPARTMENT and AVGSALARY.

Using Multiple CTEs in a Single Query

This is a sample of a complex query where Multiple CTE tables are being used in combination with SQL JOINS. For multiple CTEs in a single query, there is only a single WITH clause.

WITH Ctable1 AS
(SELECT C1Field1, 
row_number() over(partition by C1Field2) as rowNbr
FROM SampleTable1  WHERE C1Field5 = "XYZ" ), 
		
Ctable2 AS
(SELECT  SampleTable1.* , 
row_number() over(order by C2Field3 desc) as rowNbr
FROM SampleTable2 
      INNER JOIN SampleTable3 ON C2Field = SampleTable3.C3Field
WHERE C2Field5 <= "ABC")SELECT * FROM Ctable1 t1 
       FULL JOIN Ctable2 t2 ON t1.C1Field1 = t2.C2Field1 
WHERE t1.rowNbr = 1 OR t2.rowNbr = 1

In the Above two temporary tables Ctable1 and Ctable2. Ctable1 is created using a simple select statement with OLAP function row_number. Ctable2 is created by joining two tables SampleTable2 and SampleTable3.

And finally, “SELECT statement,” which joins Ctable1 and Ctable2 using FULL JOIN.