Is there a more efficient way to write this simple SQL query? -

seems lot of redundancy here. there more efficient way, or unions need used? frist inner join there because players table not have hotel information , has retrieved teams.

select players.name players inner join teams on players.team = teams.name teams.staysat = 'ambassador' , teams.checkin not null , teams.checkin < '18-aug-07' , teams.checkout > '18-aug-07' -- union select officials.name officials officials.staysat = 'ambassador' , officials.checkin not null , officials.checkin < '18-aug-07' , officials.checkout > '18-aug-07' -- union select fans.name fans fans.staysat = 'ambassador' , fans.checkin not null , fans.checkin < '18-aug-07' , fans.checkout > '18-aug-07'; 

yes, because retrieve data different tables (players, fans, officials) union necessary here, can imagine shorten if provide function pulls desired information table specify string, lead building querys out of strings concatenation or other headachy stuff maybe better stick union select ;)

besides, if tables not differ in other columns might consider putting them 1 table , distinguish them on flag or that.