SELECT with EXISTS (Subquery) in Teradata

Database

SELECT with EXISTS (Subquery) in Teradata

I can't be the first non-Teradata user to ask this question. But what the heck can I possibly do to get aroudn this limitation of not being able to use scalar subqueries in my select clause?

Let's say I want to pull a list of my customers and create some sort of binary indicator based on whether they ordered a certain type of product:

SELECT a.*
, CASE
WHEN EXISTS
(SELECT 1
FROM tblOrder O
INNER JOIN tblProduct
ON O.Product_id = P.Product_id
WHERE O.customer_id = C.customer_id
AND P.Product_Type IN (2, 5, 6, 9)
)
THEN 1
ELSE 0
END AS My_Custom_Indicator
FROM tblCustomer C
INNER JOIN tblOtherStuff S
ON C.CustomerID = S.CustomerID

I understand how to rewrite this using OUTER joins, ... however I am not sure how this works when things get more complicated. This is only a simple example. In reality, I am trying to create upwards of 20 different binary indicators, all of which are based on EXISTS (Subquery) in various tables with 1-to-many and many-to-many relationships. How could I possibly use OUTER joins without creating scalability problems? Some of my table sizes approach 1 billion in row count.

I'm looking for the absolute most efficient syntax to use with this sort of problem - but instead of the example above, assume I am using CASE WHEN EXISTS (Subquery) 20 differnet times on 10 different large tables with one to many relationships.

Thanks in advance. This is one of my biggest headaches with Teradata, and if I figure this out, maybe I will stop transferring data off Teradata into a competing environment all the time.

2 REPLIES
Senior Apprentice

Re: SELECT with EXISTS (Subquery) in Teradata

You must use an older version of Teradata, this syntax should be supported since 13.10.

But it's definitely not the best way to get that info, and i doubt that it's best case in any other DBMS.

Depending on the actual query i i usually try to rewrite to OLAP functions or Derived Tables with Outer Joins:

SELECT a.*
, CASE when o.CustomerID is null then 0 else 1 END AS My_Custom_Indicator
FROM tblCustomer C
INNER JOIN tblOtherStuff S
ON C.CustomerID = S.CustomerID
left join
(
SELECT distinct O.customer_id
FROM tblOrder O
INNER JOIN tblProduct
ON O.Product_id = P.Product_id
where P.Product_Type IN (2, 5, 6, 9)
) as o
on O.customer_id = C.customer_id

Dieter

Re: SELECT with EXISTS (Subquery) in Teradata

Thanks Dieter,  I have another question so I will post in a new thread. This is helpful however it feels like I'm being inefficient as the query grows larger.