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 ...