用于组合两个表的MySQL查询

Question

我试图获得一个MySQL查询来组合两个表。两个表都有一个user_id和date列，其他列对于每个表都是唯一的。我想要完成的事情最好用一个图像（imgur）来解释。我试过各种连接和联合，但不能得到正确的组合。我也尝试过搜索stackoverflow，但是我可能没有使用正确的术语来进行搜索。

表格的文字说明:

tablel 
user_id date       animals 
1       9/15/2020  Lion 
2       8/11/2020  Tiger 
3       12/12/2020 Bear 

table2 
user_id date      fruit 
1       7/7/2020  apple 
2       6/5/2020  pear 
4       3/8/2020  peach

output 
user_id date        animals fruit 
1       9/15/2020   Lion 
1       7/7/2020            apple 
2       8/11/2020   Tiger 
2       6/5/2020            pear
3       12/12/2020  Bear 
4       3/8/2020            peach 

Answer 1

-- use test;
-- drop table if exists table1;
create table table1(user_id int, `date` date, animals varchar(10));
insert into table1 values(1,'2020/09/15','Lion');
insert into table1 values(2,'2020/11/08','Tiger');
insert into table1 values(3,'2020/12/12','Bear');

-- drop table if exists table2;
create table table2(user_id int, `date` date, fruit varchar(10));
insert into table2 values(1,'2020/07/07','apple');
insert into table2 values(2,'2020/05/06','pear');
insert into table2 values(4,'2020/08/03','peach');

select 
  user_id,
  `date`,
  animals,
  '' as fruit
from table1
union all
select
  user_id,
  `date`,
  '',
  fruit
from table2
order by user_id,`date`;

输出:

+ ------------ + --------- + ------------ + ---------- +
| user_id      | date      | animals      | fruit      |
+ ------------ + --------- + ------------ + ---------- +
| 1            | 2020-07-07 |              | apple      |
| 1            | 2020-09-15 | Lion         |            |
| 2            | 2020-05-06 |              | pear       |
| 2            | 2020-11-08 | Tiger        |            |
| 3            | 2020-12-12 | Bear         |            |
| 4            | 2020-08-03 |              | peach      |
+ ------------ + --------- + ------------ + ---------- +

Answer 2

您也可以尝试完全外部联接:

SELECT user_id, date, animals, '' as fruit
FROM table1
FULL OUTER JOIN table2
ON table1.user_id = table2.user_id AND table1.date = table2.date

Answer 3

有了工会，你就能做到这一点

SELECT user_id, date, animals, fruit FROM
(
      SELECT user_id, date, animals, '' as fruit
        FROM table1
      UNION ALL
      SELECT user_id, date, '', fruit
        FROM table2
 ) t
 ORDER BY user_id, animals