Query execution order

Database

Query execution order

Hi , what is the order of query execution in terdata.

 

as per my understanding below is the order of execution for SQL select statement in RDBMS

 

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

select column_name as new_name from db.tab where new_name='something'   --is an error in db2 as where clause executes first new_name is not existed by that time

 

but the same SQL executing in teradata. i am confused with execution order 

can somebody please explain execution order in Teradata.

 

 


Accepted Solutions
Junior Contributor

Re: Query execution order

That's what I wrote, the logical processing is the same (it's the way Standard SQLworks), but an alias can be used in any place in Teradata.

1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: Query execution order

It's the same logical order, you just need to add OLAP-functions:

 

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause/aggregation
  4. HAVING clause
  5. OLAP Functions
  6. QUALIFY -- Teradata specific
  7. SAMPLE/TOP -- Teradata specific
  8. SELECT clause
  9. ORDER BY clause

But due to historical reasons (Teradata is older than Standard SQL and implemented a QUEL-based language before) usage of an alias is allowed in any place.

 

This brakes the rules, but is nice to have :-)

There's just one thing to watch: don't assign an alias name which matches an existing column name if you want to use it in another place.

Re: Query execution order

I executed the below SQL in Teradata and it executed successfully ,

but as per the order of execution where clause should execute first before the select clause, but how the where clause able to get the col3 (case stment )values before select statement execution.

 

and also within select clause there are three case statements  in which second case statement is accesing third case statement which is last in the order of select clause

please explain how this is happening internally

 

 

SyntaxEditor Code Snippet

Select 

case when 'A'='A' then 'ONE' else 'NONE' end  as Col1,case when Col3='NONE' then 'A' else 'ONE' end as Col2,case when Col1='A' then 'ONE' else 'NONE' end as Col3

from DB.TBL  where Col3='ONE'

 

 

 

Junior Contributor

Re: Query execution order

That's what I wrote, the logical processing is the same (it's the way Standard SQLworks), but an alias can be used in any place in Teradata.

Highlighted
Teradata Employee

Re: Query execution order

Internally each case statement gets translated into a logic tree. Dependencies, such as Col2 depending on Col3 depending on Col1 are arranged into logical order.  As for the WHERE clause, technically the SELECT is the last thing executed - all the steps required for the Select are executed first, and the WHERE clause may be pushed down into one of those steps if possible.  In this case (as well as conceptually) the WHERE clause is just part of the SELECT.

 

By the way, you don't need a FROM clause in this SQL statement, since you are not really accessing any tables.  You can take that out ans it still works.  Try "Select Date, User;"