Mastering Nested Sub-Queries: Accurate Table-Column Relationships in Complex SQL with Star Columns

This article discusses the challenge of building correct relationships between tables and columns in nested sub-queries with star columns. It uses a Teradata SQL code example to illustrate the process.

The article focuses on a sub-query aliased as SUBSCRIBER_ and its derived columns, which are used in an outer query. It explains that some columns are explicitly listed in the sub-query’s select list, while others come from a star column (SUBSCR.*) referencing the PRD2_ODW.SUBSCRIBER_ table.

The analysis then examines the outer query’s select list, particularly columns from the SUBSCRIBER_ sub-query. It demonstrates how to determine the origin of each column:

1. Columns not explicitly listed in the sub-query (e.g., SUBS_ID, ACTIVATION_DATE) are derived from the star column and belong to the PRD2_ODW.SUBSCRIBER_ table.
2. Columns explicitly listed in the sub-query (e.g., FIRST_TP_ID, CUST_ID) are traced back to their original tables (SUBS_CUST in these cases).

This process helps in accurately mapping columns to their source tables, which is crucial for understanding complex queries and maintaining data lineage in nested sub-queries with star columns.

Here is the Teradata SQL code used in the demo:

In line 130: SUBSCRIBER_ is the alias of the sub-query, and derived column of this sub-query is used in the outer query.

The derived column for SUBSCRIBER_ is start from line 90 to line 111 that from different tables. There is also a star column * prefixed with SUBSCR which is the alias of the PRD2_ODW.SUBSCRIBER_ table.

Now, let take a look at the outer query, especially the select list part that start from line 23 to line 87, and we only focus on the columns that from the sub-query SUBSCRIBER_.

line 23: SUBSCRIBER_.SUBS_ID, column SUBS_ID is not explicitly listed in the select list from line 90 to line 111, so this column must from SUBSCR.*, and SUBSCR is the alias of the PRD2_ODW.SUBSCRIBER_ table in line 112.

• Conclusion: SUBS_ID is the column of table: PRD2_ODW.SUBSCRIBER_

line 25: SUBSCRIBER_.REGISTRATION_DATE (NAMED REGISTRATION_DTTM), column REGISTRATION_DATE is not explicitly listed in the select list from line 90 to line 111, so this column must from SUBSCR.*, and SUBSCR is the alias of the PRD2_ODW.SUBSCRIBER_ table.

• Conclusion: REGISTRATION_DATE is the column of table: PRD2_ODW.SUBSCRIBER_

line 26: SUBSCRIBER_.FIRST_CALL (NAMED FIRST_CALL_DTTM), column FIRST_CALL is not explicitly listed in the select list from line 90 to line 111, so this column must from SUBSCR.*, and SUBSCR is the alias of the PRD2_ODW.SUBSCRIBER_ table.

• Conclusion: FIRST_CALL is the column of table: PRD2_ODW.SUBSCRIBER_

line 27: SUBSCRIBER_.FIRST_TP_ID, column FIRST_TP_ID is explicitly listed in the select list at line 91: SUBS_CUST.FIRST_TP_ID.

• Conclusion: FIRST_TP_ID is the column of table: SUBS_CUST

line 28: SUBSCRIBER_.CUST_ID, column CUST_ID is explicitly listed in the select list at line 92: SUBS_CUST.CUST_ID.

• Conclusion: CUST_ID is the column of table: SUBS_CUST

line 30: SUBSCRIBER_.DEALER_ID, column DEALER_ID is explicitly listed in the select list at line 102: SUBS_CUST.DEALER_ID.

• Conclusion: DEALER_ID is the column of table: SUBS_CUST

line 35:  SUBSCRIBER_.SRVP_COV_CUST_ID, column SRVP_COV_CUST_ID is explicitly listed in the select list at line 93: SUBS_CUST.SRVP_COV_CUST_ID.

• Conclusion: SRVP_COV_CUST_ID is the column of table: SUBS_CUST

line 37: SUBSCRIBER_.TP_ID, column TP_ID is explicitly listed in the select list at line 94: SUBS_CUST.TP_ID.

• Conclusion: TP_ID is the column of table: SUBS_CUST

line 38: SUBSCRIBER_.BLOCK_QUOTA (NAMED IS_BLOCK_QUOTA), column BLOCK_QUOTA is explicitly listed in the select list at line 95: SUBS_CUST.BLOCK_QUOTA.

• Conclusion: BLOCK_QUOTA is the column of table: SUBS_CUST

line 40: WHEN (SUBSCRIBER_.REL_CAT_ID < 0) THEN (NULL), column REL_CAT_ID is explicitly listed in the select list at line 96: SUBS_CUST.REL_CAT_ID.

• Conclusion: REL_CAT_ID is the column of table: SUBS_CUST

line 47: SUBSCRIBER_.STATUS_CHNG_RSN_ID, column STATUS_CHNG_RSN_ID is explicitly listed in the select list at line 97: SUBS_CUST.STATUS_CHNG_RSN_ID.

Conclusion: STATUS_CHNG_RSN_ID is the column of table: SUBS_CUST

line 48: SUBSCRIBER_.STATUS_ID, column STATUS_ID is explicitly listed in the select list at line 98: SUBS_CUST.STATUS_ID.

• Conclusion: STATUS_ID is the column of table: SUBS_CUST

line 49: SUBSCRIBER_.SUBS_TYPE_ID, column SUBS_TYPE_ID is explicitly listed in the select list at line 99: SUBS_CUST.SUBS_TYPE_ID,

• Conclusion: SUBS_TYPE_ID is the column of table: SUBS_CUST

line 50: SUBSCRIBER_.BRANCH_ID (NAMED BRANCH_ID), column BRANCH_ID is explicitly listed in the select list at line 100: SUBS_CUST.BRANCH_ID,

• Conclusion: BRANCH_ID is the column of table: SUBS_CUST

line 61: SUBSCRIBER_.BRANCH_ID (NAMED BRANCH_ID), column BRANCH_ID is explicitly listed in the select list at line 100: SUBS_CUST.BRANCH_ID,

• Conclusion: BRANCH_ID is the column of table: SUBS_CUST

line 62: CAST((SUBSCRIBER_.CALC_PLATFORM_ID) AS DECIMAL(2, 0))(NAMED CALC_PLATFORM_ID), column CALC_PLATFORM_ID is explicitly listed in the select list at line 110: END)(NAMED CALC_PLATFORM_ID), and this is a column alias, the underlying column is SUBS_CUST.CALC_PLATFORM_ID

• Conclusion: CALC_PLATFORM_ID is the column of table: SUBS_CUST

line 66: WHEN (((SUBSCRIBER_.BRANCH_ID = 61) AND (SUBSCRIBER_.CALC_PLATFORM_ID IN (6, 7, 8, 9))) AND (:_spVV0 >= DATE ‘2018-04-30’)) THEN (0), column BRANCH_ID is explicitly listed in the select list at line 100: SUBS_CUST.BRANCH_ID,

• Conclusion: BRANCH_ID is the column of table: SUBS_CUST

line 71: CAST((SUBSCRIBER_.LAST_FLASH_DTTM) AS DATE)(NAMED LAST_FLASH_DTTM), column LAST_FLASH_DTTM is explicitly listed in the select list at line 104: SUBS_CLR_FLASH.LAST_FLASH_DTTM,

• Conclusion: LAST_FLASH_DTTM is the column of table: SUBS_CLR_FLASH

line 73:  WHEN (NOT (SUBSCRIBER_.FLASH_CODE_ID IS NULL)) THEN (SUBSCRIBER_.FLASH_CODE_ID), column FLASH_CODE_ID is explicitly listed in the select list at line 105: SUBS_CLR_FLASH.FLASH_CODE_ID,

• Conclusion: FLASH_CODE_ID is the column of table: SUBS_CLR_FLASH

line 77: WHEN (NOT (SUBSCRIBER_.IF_NEW_CHURN IS NULL)) THEN (SUBSCRIBER_.IF_NEW_CHURN), column IF_NEW_CHURN is explicitly listed in the select list at line 106: SUBS_CLR_FLASH.IF_NEW_CHURN,

• Conclusion: IF_NEW_CHURN is the column of table: SUBS_CLR_FLASH

Conclusion

The table PRD2_ODW.SUBSCRIBER_ has the following columns:

PRD2_ODW.SUBSCRIBER_.SUBS_ID
PRD2_ODW.SUBSCRIBER_.ACTIVATION_DATE
PRD2_ODW.SUBSCRIBER_.REGISTRATION_DATE
PRD2_ODW.SUBSCRIBER_.FIRST_CALL