在Oracle sql中连接两个表

Question

我有两张桌子。其中一个表有7个值，另一个表有5个值。这些表有其共同的主键。我想用这种方式连接两个表:如果我有一个表

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

我想要一张这样的桌子

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

Answer 2

你真的需要一个工会：

SELECT English, French FROM T1
UNION
SELECT English, French FROM T2

如果您不关心副本，则可以使用UNION

在OP的评论之后编辑：

SELECT l.num_client, l.id_agence, l.num_immatricul
FROM  locations l
UNION
SELECT r.num_client, r.id_agence, r.num_immatriculation
FROM  restitutions r

Answer 3

以下几点应该可以做到。

SELECT tab1.English, tab1.French
 UNION
SELECT tab2.English, tab2.French