sql - 在 Oracle sql 中连接两个表

Question

我有2张 table 。其中一个表有 7 个值，另一个表有 5 个值。这些表具有共同的主键。我想以这种方式加入两个表:
如果我有一张 table

English              French
-------------------- --------------------
one                  Un
two                  Deux
three                Trois
four                 Quatre
four                 Quattro
five                 Cinq
five                 Cinco

还有一个:

English              French
-------------------- --------------------
one                  aaaaa
two                  bbbbb
three                ccccc
four                 
five

我想要一张这样的 table :

English              French
-------------------- --------------------
one                  Un
one                  aaaaa
two                  Deux
two                  bbbb
three                Trois
three                ccccc
four                 Quatre
four                 Quattro
four                 --------
five                 Cinq
five                 Cinco
five                 ----------

我尝试使用 join ，但它对 four 和 five 值进行了线性组合。我该怎么做呢？谢谢。

编辑 :SQL 查询:

SELECT l.date_location, l.duree,  r.km_restitution, r.km_parcouru 
FROM  locations l, restitutions r
UNION
SELECT l.num_client, l.date_location, l.duree,  r.km_restitution, r.km_parcouru
FROM  locations l, restitutions r


id_agence num_immatriculation num_client km_restitution km_parcouru state date_restitution 
1   406BON69    1002    30000   1000    BON 29-MAY-10
3   785CIM13    1001    56580   80  BON 09-AUG-08
5   800BBB75    1000    2020    20  BON 24-APR-11
4   307VXN78    1000    20040   40  BON 28-JAN-11
2   290UTT92    1004    30030   30  BON 01-AUG-10
5   777SET13    1005    4030    30  BON 26-APR-11
2   179CLV92    1004    15015   15  BON 03-FEB-11
5   400AAA75    1003    1020    20  BON 18-SEP-11
5   666NEF69    1004    3040    40  BON 15-APR-11
2   111AAA75    1001    20020   20  BON 21-DEC-09
1   333CCC78    1001    43250   40  BON 27-DEC-09
2   260CDE95    1003    79000   430 BON 10-SEP-09
4   307VXN78    1003    20090   90  BON 11-FEB-11
1   123ABC78    1003    10010   10  BON 04-OCT-10
1   222BBB77    1001    9050    50  BON 23-DEC-09


Locations
    id_agence num_immatricul  num_client duree   date_location
    2   406BON69    1002    20  10-MAY-10
    3   785CIM13    1001    3   07-AUG-08
    5   800BBB75    1000    7   18-APR-11
    4   307VXN78    1000    5   24-JAN-11
    1   290UTT92    1004    1   31-JUL-10
    5   777SET13    1005    4   23-APR-11
    1   179CLV92    1004    5   30-JAN-11
    5   400AAA75    1003    2   17-SEP-11
    2   123ABC78    1003    4   01-OCT-10
    5   666NEF69    1004    5   11-APR-11
    1   111AAA75    1001    2   20-DEC-09
    1   222BBB77    1001    2   22-DEC-09
    1   333CCC78    1001    3   25-DEC-09
    1   260CDE95    1003    10  01-SEP-09
    4   307VXN78    1003    13  30-JAN-11
    2   123ABC78    1003    8   20-NOV-11
    2   406BON69    1002    10  20-NOV-11

想要的结果

id_agence num_immatricul  num_client duree   date_location date_restitution
2   406BON69    1002    20  10-MAY-10     date_restitution
3   785CIM13    1001    3   07-AUG-08     date_restitution
5   800BBB75    1000    7   18-APR-11     date_restitution
4   307VXN78    1000    5   24-JAN-11     date_restitution
1   290UTT92    1004    1   31-JUL-10     date_restitution
5   777SET13    1005    4   23-APR-11     date_restitution
1   179CLV92    1004    5   30-JAN-11     date_restitution
5   400AAA75    1003    2   17-SEP-11     date_restitution
2   123ABC78    1003    4   01-OCT-10     date_restitution
5   666NEF69    1004    5   11-APR-11     date_restitution
1   111AAA75    1001    2   20-DEC-09      date_restitution
1   222BBB77    1001    2   22-DEC-09     date_restitution
1   333CCC78    1001    3   25-DEC-09     date_restitution
1   260CDE95    1003    10  01-SEP-09     date_restitution
4   307VXN78    1003    13  30-JAN-11     date_restitution
2   123ABC78    1003    8   20-NOV-11      ----------------
2   406BON69    1002    10  20-NOV-11      ---------------

除了列名，我放的 date_restitution 包含真实日期。

Answer 1

你可以使用联合:

select English, French from Table1
UNION ALL
select English, French from Table2

或完整的外连接

select distinct coalesce(T1.English, T2.English), coalesce(T1.French, T2.French)
from Table1 T1
full outer join Table2 T2 on T1.English = T2.English

编辑:

假设您希望 restitutions.date_restitution 代替 date_location 出现在恢复记录中 -

SELECT l.num_client, l.date_location, l.duree,  to_number(null) km_restitution, to_number(null) km_parcouru
FROM  locations l
UNION ALL
SELECT r.num_client, r.date_restitution date_location, 0 duree,  r.km_restitution, r.km_parcouru
FROM  restitutions r

进一步编辑(基于提供的结果):

select l.id_agence,
       l.num_immatricul,
       l.num_client,
       l.duree,
       l.date_location,
       decode(r.date_restitution, NULL,'----------------', 'date_restitution')
           as date_restitution -- or just r.date_restitution
from location l
left outer join restitution r
on l.id_agence = r.id_agence and 
   l.num_immatricul = r.num_immatricul and 
   l.num_client = r.num_client and 
   l.date_location <= r.date_restitution