CODEWITHSUNDEEP
sqloracle11gleft-join
Alex
Alex

Reputation: 347

Select two columns one join

Given this SQL query:

select A.field1 as field
from A left join B
on A.field1 = B.field1 
union all
select A.field2 as field 
from A left join B
on A.field2 = B.field1;

Is there some way to get the same with few lines? i.e: is there another way to get two columns from a table A joined each one separated with a column from the table B, and then both put into the same resultset column? The reason for needing that is the real query has a more complex join and where conditions, and for that is a bit big and, almost all, redundant.

Thank you in advance!

Upvotes: 0

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

With the left join and assuming that the join to B doesn't produce duplicates, then your query is more simply written as:

select A.field1 as field from A
union all
select A.field2 from A;

The left join to B doesn't do anything in this case.

I assume your query is more complicated or you intend an inner join. You could do the union all before the join:

select A.field
from (select field1 from A union all
      select field2 from A
     ) a join
     B
     on A.field = B.field1 ;

Whether or not this has worse or the same performance depends on the nature of the data.

Upvotes: 2

Related Questions