Left outer join with OR condition

Analytics
Teradata Employee

Left outer join with OR condition

Hello,

 

I have a query which is in below form

 

sel A.fname, A.Address, B.Dept
from A left Join B
on (A.id= B.id  or A.eid = B.eid);

 

I want to rewrite it without using OR condition.

I tried below query using UNION but it does not provide the expected results. 

SyntaxEditor Code Snippet

sel A.fname, A,Address, B.Dept
from A Inner Join B
on (A.id= B.id ) 

union 

sel A.fname, A,Address, B.Dept
from A Inner Join B
on A.eid = B.eid);

 

Is there a way to rewrite the query to avoid OR condition

4 REPLIES
Teradata Employee

Re: Left outer join with OR condition

You seem to be on the right track. But why did you change from Left Outer Join to Inner Join?

Highlighted
Teradata Employee

Re: Left outer join with OR condition

Hello Fred, thank you for reply.

It was typo Smiley Embarassed. It should be left join

 

sel A.fname, A,Address, B.Dept
from A Left Join B
on (A.id= B.id ) 

union 

sel A.fname, A,Address, B.Dept
from A Left Join B
on A.eid = B.eid);

This change in code using UNION does not provide the expected results. I am chaning OR to any other form because my query goes for product join in explain which I want to avoid.

Teradata Employee

Re: Left outer join with OR condition

Can you explain what unexpected results you are seeing? I did a tiny test and am getting identical results for the OR and the UNION query. 

 

create table test_a (
	id int
	,eid int
	,fname char(1)
	,address char(1)
) primary index(id);

insert into test_a (1,1,'A','A');
insert into test_a (1,2,'B','B');
insert into test_a (2,1,'C','C');
insert into test_a (2,2,'D','D');
insert into test_a (3,3,'E','E');

select * from test_a

create table test_b (
	id int
	,eid int
	,dept char(1)
) primary index(id);

insert into test_b (1,1,'V');
insert into test_b (1,2,'W');
insert into test_b (2,1,'X');
insert into test_b (2,2,'Y');
insert into test_b (3,3,'Z');


sel A.fname, A.Address, B.Dept
from test_a as A 
left join test_b as B
	on (A.id = B.id  or A.eid = B.eid);

-- fname address dept 
-- ----- ------- ---- 
-- A     A       X   
-- A     A       V   
-- A     A       W   
-- B     B       Y   
-- B     B       V   
-- B     B       W   
-- C     C       X   
-- C     C       Y   
-- C     C       V   
-- D     D       X   
-- D     D       Y   
-- D     D       W   
-- E     E       Z   
--	
	
sel A.fname, A.Address, B.Dept
from test_a as A 
left join test_b as B
	on A.id = B.id  
	
union	

sel A.fname, A.Address, B.Dept
from test_a as A 
left join test_b as B
	on A.eid = B.eid
	
-- fname address dept 
-- ----- ------- ---- 
-- A     A       V   
-- A     A       W   
-- A     A       X   
-- B     B       V   
-- B     B       W   
-- B     B       Y   
-- C     C       V   
-- C     C       X   
-- C     C       Y   
-- D     D       W   
-- D     D       X   
-- D     D       Y   
-- E     E       Z   
Teradata Employee

Re: Left outer join with OR condition

Are there duplicate rows in the "expected result"? UNION is implicitly UNION DISTINCT.

 

sel A.fname, A.Address, COALESCE(B.Dept,C.Dept)
from test_a as A 
left join test_b as B
	on A.id = B.id  
left join test_b as C	on A.eid = C.eid
       AND A.id <> C.id /* avoid extra duplicates */