database - Why is a union mysql query over three tables faster than join on impossible where clauses? -

my schema set such have 3 tables player coach , manager 3 have foreign key table employee contains not more single auto-incrementing id , string type representing type of employee ie. 'player' or 'coach' or 'manager'.

however, different employees have different ids based on team work for. have lookup table team_x_lookup each team (where x team number or name) maps team id global id. also, have generated column within each player coach , manager table each team contains team id, column indexed. column null if employee not on team.

to fetch employee team id have 2 select statements

select * employee e     left join player p on (e.`type` = 'player' , p.employee_id = e.id)     left join coach c on (e.`type` = 'coach' , c.employee_id = e.id)     left join manager m on (e.`type` = 'manager' , m.employee_id = e.id) e.id = (     select employee_id team_x_lookup t t.team_id = 6 );  select * (     select * player team_id = 6      union     select * coach team_id = 6     union      select * manager team_id = 6 ) emp; 

i have indexes on select on. running explain gives me these respectively:

1   primary e       const   primary,id_unique           primary 4   const   1   100.00   1   primary p       const   primary,employee_id _unique primary 4   const   1   100.00   1   primary c       const   primary,employee_id _unique primary 4   const   0   0.00    unique row not found 1   primary m       const   primary,employee_id _unique primary 4   const   0   0.00    unique row not found 2   subquery t      const   primary,team_id_unique      primary 4   const   1   100.00    1   primary         <derived2>                                      6               100.00   2   derived         player          ref team_x_id_index team_x_id_index 5   const   1   100.00  using index 3   union           coach           ref team_x_id_index team_x_id_index 5   const   1   100.00  using index 4   union           manager         ref team_x_id_index team_x_id_index 5   const   1   100.00  using index n   union result    <union2,3,4>                                                            using temporary 

i don't second way of doing because requires me have potentially 20 more columns each table. but, don't understand how union query 50% faster lookup table query! doesn't union have select on 3 tables, whereas cross reference query notice 1 of 3 joins possible , discard other two?

i know how make cross reference table faster, additionally because union requires me select same number of columns each table in union while each of tables have different number of columns should returned.

edit

i have 30,000 elements in database , doing timing querying first half of data via union , second half via join. time comes out 3.9s union , 7.0s join.

you can make first query more efficient removing subquery, this:

select e.* employee e inner join team_x_lookup t on e.id = t.employee_id left join player p on (e.`type` = 'player' , p.employee_id = e.id) left join coach c on (e.`type` = 'coach' , c.employee_id = e.id) left join manager m on (e.`type` = 'manager' , m.employee_id = e.id) t.team_id = 6