I can't see why it would be useful to perform the
UNION you describe (and, assuming
airport.id are the respect primary keys of those tables, there's no reason not to do a
UNION ALL, avoiding checking for duplicates). To be specific, it seems like you'd be dumping the data from the two separate tables into one (in effect) separating them back out into
airport in the self-join.
To shift the sub-query into a
JOIN, we'd also need a way to select the highest ranked airport returned. We'd wind up with something like this:
,airport.id as airport_id
,@RowNum := CASE WHEN @PrevFlightID = flight.id AND @PrevAirportId = airport.id
THEN @RowNum + 1
END as rank
,@PrevFlightID := flight.id
,PrevAirportID := airport.id
INNER JOIN airport ON SQRT(
POW(69.1 * (airport.latitude - flight.lat), 2)
+ POW(69.1 * (flight.lon - airport.longitude) * COS(airport.latitude / 57.3), 2)
) < 10
CROSS JOIN (SELECT @RowNum := 0
,@PrevFlightID := 0
,@PrevAirportID := 0
WHERE flight.STATUS = 1
-ABS(atan2(TAN(airport.longitude - flight.lon), TAN(airport.latitude -flight.lat)) * (180 / (22 / 7)))
WHERE rank = 1
which, of course, still has a subquery, to filter out the rows we don't want. If I were you, I'd run some tests with both options, and see if one performs notably better than the other. If not, I suspect I'd use what you already have; in my opinion, it's a little easier to follow.
(Note: code untested)
FYI: given that you have to plug the values from the tables into a somewhat complex equation in order to match them up, it's unlikely that adding indexes or anything like that would help in the matching process. If either table is relatively wide (has many more columns than the ones needed in the query), you might get better performance by creating covering indexes (indexes that have the columns this query needs; the engine can use that instead of the table, and probably will if the index has notably more rows per page than the table itself).
If this information is required frequently, but the
airport tables are not updated constantly during the day, then you might save the results of the query to a table. This would work best if the
flight table is updated nightly (I assume the
airport table is basically static), for example. Note that if the
flight table is updated off the normal schedule, the saved results would need to be recomputed.