join all data for null

The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
New Member

join all data for null

I have a lookup table which i want to join with customers table to find if they are eligible or not. 


 Rule Table



so rule 3 to 5 says all products qualify. and rule 1 and 2 says all segments qualify with mentioned products. I have actualy created the code which works but i am looking if there is something more efficient. as i think i am missing something.


i want to achieve a result that will give me all possible combinations if null or an exact match if a value is available


SyntaxEditor Code Snippet

sel RuleId, P.Productid ,S.Segmentid,Cost   
FROM Rule R INNER JOIN Product p ON COALESCE(R.productid,P.productid) = P.productid
INNER JOIN Segment S ON COALESCE(R.segmentid,S.segmentid) = S.segmentid


Teradata Employee

Re: join all data for null

Hi swedev,


You may have better performances using this query, but you have to test it :

    SELECT R.RuleId, P.Productid, S.Segmentid, R.Cost
      FROM Rule    as R
INNER JOIN Product as P ON P.productid = R.productid
CROSS JOIN Segment as S
     WHERE R.segmentid is null
    SELECT R.RuleId, P.Productid, S.Segmentid, R.Cost
      FROM Rule    as R
CROSS JOIN Product as p
INNER JOIN Segment as S ON S.Segmentid = R.segmentid
     WHERE R.productid is null;