What is that Teradata? syntax for a subquery aliased with a parameter (can't find a name of ref for this)

Question

it goes like this:

SELECT LOOKUP.LOOKUP_ID 
FROM (
    SELECT INNER_ID FROM 
        (
         SELECT max(INNER_ID) INNER_ID 
         FROM INNER_TABLE 
        ) INNER_QUERY 
      ) LOOKUP (LOOKUP_ID) 

It's a legacy code - I inherited - in Teradata, still runs in TD 16.20, but this is the only instance I have run into something so strange. And why would you key it like this - is another question.

Answer 1

Well these are subqueries in the FROM clause. Such subqueries are also known as "derived tables".

The innermost subquery is

(
  SELECT MAX(inner_id) inner_id 
  FROM inner_table 
) inner_query 

Here we select the maximum inner_id and call this value inner_id again. We reference this one-row query result by the alias inner_query.

The next subquery is

(
  SELECT inner_id
  FROM ( <above subquery> ) inner_query 
) lookup (lookup_id) 

We select the aforementioned maximum inner_id that is still called inner_id and call the one-row result set now lookup and the value lookup_id.

At last we select that value

SELECT lookup.lookup_id FROM ( ... ) lookup (lookup_id);

We would get the same result with a mere

SELECT MAX(inner_id) AS lookup_id 
FROM inner_table;

of course.

Maybe you are just unfamiliar with the alias name syntax for tables and columns:

FROM mytable table_alias

or

FROM mytable table_alias (col1_alias, col2_alias, ...)