sql - 3个表之间的左外连接

Question

我有 3 张 table 。

1. 座位表
2. 本地员工
3. 外籍员工。

Seat 和 Local 之间有一个共同的列 person_id，它表明哪个人坐在哪个座位上。

Seat和Foreign table之间同理，也是person_id。

我能够使用 left outer join 获取 Seat 和 Local ...以及 Seat 和 Foreign 之间所需的数据，为空缺席位获取空席位，为已占用席位获取 personid。

我的问题是，我可以得到一个包含两个表的查询映射席位表并得到一个合并报告吗？

我现在使用的查询是:

select seat.apeh05_person_id_k
      ,seat.apeh18_seat_r seatNo
      , seat.apeh17_floor_k  seatFloor
      ,vendor.apeh15_cds_d cdsid
      , vendor.apeh15_first_n firstname
      , vendor.apeh15_last_n lastname
      ,vendor.apeh15_supervisor_cds_d ll6cdsid
      ,vendor.apeh15_ll5_cds_d ll5cdsid
      , vendor.apeh15_ll4_cds_d ll4cdsid 
 from iapeh18_seat seat ,
       IAPEH15_VENDOR_EMPLOYEE  vendor
 where seat.apeh05_person_id_k = vendor.apeh15_vendor_employee_k (+) 
 order by   seat.apeh05_person_id_k asc

另一个查询是:

select seat.apeh05_person_id_k
      ,seat.apeh18_seat_r seatNo
      , seat.apeh17_floor_k  seatFloor
      ,local.apeh09_cds_d cdsid
      ,local.apeh09_first_n firstname
      , local.apeh09_last_n lastname
      ,local.apeh09_supervisor_cds_d ll6cdsid
      ,local.apeh09_ll5_cds_d ll5cdsid
      ,  local.apeh09_ll4_cds_d ll4cdsid 
 from iapeh18_seat seat 
        , IAPEH09_LOCAL_EMPLOYEE local
 where seat.apeh05_person_id_k = local.apeh05_candidate_k (+)
 order by seat.apeh05_person_id_k asc

Answer 1

可能是 Remko Jansen 答案的更有效版本

select seat.apeh05_person_id_k person_id
      ,seat.apeh18_seat_r seatNo
      ,seat.apeh17_floor_k  seatFloor
      ,employee.apeh15_cds_d cdsid
      ,employee.apeh15_first_n firstname
      ,employee.apeh15_last_n lastname
      ,employee.apeh15_supervisor_cds_d ll6cdsid
      ,employee.apeh15_ll5_cds_d ll5cdsid
      ,employee.apeh15_ll4_cds_d ll4cdsid 
  from iapeh18_seat seat ,
    (select * from IAPEH15_VENDOR_EMPLOYEE  
      union all
     select * from IAPEH09_LOCAL_EMPLOYEE
    ) employee
   where seat.apeh05_person_id_k = employee.apeh05_candidate_k (+)
   order by apeh05_person_id_k

首先合并，然后加入 - 省去了执行不同行的麻烦(相当昂贵的操作)。