Alex Alex - 1 year ago 100
SQL Question

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!

Answer Source

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
     on A.field = B.field1 ;

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download