Thread: SELECT from two tables with different field names?

Hello!

Due to some legacy apps, I end up with two tables with similar information, but with zero intersection (i.e., both tables are exclusive of each other).

For illustration:

table_one has the fields emp_id and fullname

table_two has the fields employee_id, first_name, and last_name

Now, I need a query that will search for employee ID in both tables and return his/her full name.

How do I do that? Will a simple UNION suffice?

Rgds,

Pandu Poluan, 12.12.2011 05:39:
> Hello!
>
> Due to some legacy apps, I end up with two tables with similar information, but with zero intersection (i.e., both
tablesare exclusive of each other). 
>
> For illustration:
>
> table_one has the fields emp_id and fullname
>
> table_two has the fields employee_id, first_name, and last_name
>
> Now, I need a query that will search for employee ID in both tables and return his/her full name.
>
> How do I do that? Will a simple UNION suffice?

Yes a UNION should do (actually a UNION ALL as it will not try to remove duplicates which makes the query faster)

select *
from (
    select emp_id, fullname
    from table_one

    union all

    select employee_id,
           first_name||' '||last_name
    from table_two
) t
where emp_id = 1

On Dec 12, 2011 3:25 PM, "Thomas Kellerer" <spam_eater@gmx.net> wrote:
>
> Pandu Poluan, 12.12.2011 05:39:
>
>> Hello!
>>
>> Due to some legacy apps, I end up with two tables with similar information, but with zero intersection (i.e., both tables are exclusive of each other).
>>
>> For illustration:
>>
>> table_one has the fields emp_id and fullname
>>
>> table_two has the fields employee_id, first_name, and last_name
>>
>> Now, I need a query that will search for employee ID in both tables and return his/her full name.
>>
>> How do I do that? Will a simple UNION suffice?
>
>
> Yes a UNION should do (actually a UNION ALL as it will not try to remove duplicates which makes the query faster)
>
> select *
> from (
>   select emp_id, fullname
>   from table_one
>
>   union all
>
>   select employee_id,
>          first_name||' '||last_name
>   from table_two
> ) t
> where emp_id = 1
>

Thank you! I can see how UNION ALL will speed the query.

But, shouldn't I put the WHERE clause in the inner SELECTs?

Rgds,

Pandu Poluan, 13.12.2011 04:40:
>  > Yes a UNION should do (actually a UNION ALL as it will not try to remove duplicates which makes the query faster)
>  >
>  > select *
>  > from (
>  >   select emp_id, fullname
>  >   from table_one
>  >
>  >   union all
>  >
>  >   select employee_id,
>  >          first_name||' '||last_name
>  >   from table_two
>  > ) t
>  > where emp_id = 1
>  >
>
> Thank you! I can see how UNION ALL will speed the query.
>
> But, shouldn't I put the WHERE clause in the inner SELECTs?

I thought your goal was to just have a single condition. But if you can push that into the union that would be more
efficient.

Regards
Thomas

On Dec 13, 2011 4:34 PM, "Thomas Kellerer" <spam_eater@gmx.net> wrote:
>
> Pandu Poluan, 13.12.2011 04:40:
>
>>  > Yes a UNION should do (actually a UNION ALL as it will not try to remove duplicates which makes the query faster)
>>  >
>>  > select *
>>  > from (
>>  >   select emp_id, fullname
>>  >   from table_one
>>  >
>>  >   union all
>>  >
>>  >   select employee_id,
>>  >          first_name||' '||last_name
>>  >   from table_two
>>  > ) t
>>  > where emp_id = 1
>>  >
>>
>> Thank you! I can see how UNION ALL will speed the query.
>>
>> But, shouldn't I put the WHERE clause in the inner SELECTs?
>
>
> I thought your goal was to just have a single condition. But if you can push that into the union that would be more efficient.
>

Well, I need a single query not a single condition, but thanks for confirming :-)

Rgds,