mysql - 如何在连接两个表时使用 "where"？

Question

主表:

(旅行)

id | name
1  | USA
2  | Europe
3  | Asia

children :

(plane)
id | trip_id | name | other
1  |    1    | aaa  |  w
2  |    1    | bbb  |  e
3  |    3    | ccc  |  rr

(boat)
id | trip_id | name
1  |    2    | jjj
2  |    2    | kkk
3  |    3    | lll

如果我想乘飞机旅行，那么我可以:

SELECT trip.* FROM trip INNER JOIN plane ON plane.trip_id = trip.id

如果我想乘船旅行，那么我可以:

SELECT trip.* FROM trip INNER JOIN boat ON boat.trip_id = trip.id

但是如何在一个查询中获取所有乘坐飞机或轮船(或两者)的旅行？

Answer 1

只需使用UNION ALL:

SELECT trip.* FROM trip INNER JOIN plane ON plane.trip_id = trip.id
UNION ALL
SELECT trip.* FROM trip INNER JOIN boat ON boat.trip_id = trip.id