SQL文：行数が異なる結果の結合

Question

前提

SQLにてテーブル１結果とテーブル２結果の
IDをキーに両結果が全て表示できるように結合したい

【テーブル１】　　　　　　　【テーブル２】
| ID | kekka1　　　　　　　　　| ID | kekka2
|1　| りんご　　　　　　　　　 | 1 | 2021
|1　| みかん　　　　　　　　　 | 1 | 2022
|2　| りんご　　　　　　　　　 | 1 | 2023
|3　| りんご　　　　　　　　　 | 1 | 2024
|3　| みかん　　　　　　　　　 | 3 | 2022
|4　| りんご

実現したいこと

下記のような結果にしたい。

| ID | kekka1　| kekka2 |
|1　|りんご　 | 2021
|1　|みかん　 | 2022
|1　|　　　　 | 2023
|1　| 　　　　| 2024
|2　|りんご　 |
|3　|りんご　 | 2022
|3　|みかん　 |
|4　|りんご　 |

発生している問題・エラーメッセージ

なし

該当のソースコード

SELECT テーブル1.ID , テーブル1.kekka , Nen.kekka
  FROM テーブル1
LEFT JOIN (
　　　　　　SELECT テーブル2.ID , テーブル2.kekka
    　　　  　FROM テーブル2
　　　　　  ) AS Nen
on テーブル1.ID = Nen.ID

試したこと

LEFT JOIN、INNER JOIN、UNIONなど

補足情報（FW/ツールのバージョンなど）

Microsoft SQL Server management studio17

Comments

[yambejp]

MSのSQLSERVER環境であればMySQLタグは外してください

Answer 1

SQLにはデータのorder byを指定しないで優先順位というものがありませんので
今のデータ形式だけでは希望する結果を導くことは難しいでしょう。
それぞれのテーブルにauto_incrementな主キー設定をして明示的に優先順位をきめられるなら
対応できるかもしれません
※SQLSERVERと文法が多少異なるかもしれませんがサンプル

CREATE TABLE tbl1(
pid int primary key auto_increment,
id int,
kekka1 varchar(10)
);
insert into tbl1(id,kekka1) values
(1,'りんご'),
(1,'みかん'),
(2,'りんご'),
(3,'りんご'),
(3,'みかん'),
(4,'りんご');

CREATE TABLE tbl2(
pid int primary key auto_increment,
id int,
kekka2 varchar(10)
);
insert into tbl2(id,kekka2) values
(1,'2021'),
(1,'2022'),
(1,'2023'),
(1,'2024'),
(3,'2022');

select t3.id,t4.kekka1,t5.kekka2 from (
select id,(select count(*)+1 from tbl1 where id=t1.id and pid<t1.pid) as rank
from tbl1 as t1
union 
select id,(select count(*)+1 from tbl2 where id=t2.id and pid<t2.pid) as rank
from tbl2 as t2
) as t3
left join (
select kekka1,id,(select count(*)+1 from tbl1 where id=t1.id and pid<t1.pid) as rank
from tbl1 as t1
) as t4 on t3.id=t4.id and t3.rank=t4.rank
left join (
select kekka2,id,(select count(*)+1 from tbl2 where id=t2.id and pid<t2.pid) as rank
from tbl2 as t2
) as t5 on t3.id=t5.id and t3.rank=t5.rank
order by t3.id,t3.rank