SQLで期待通りの結果が返ってこない。また、CASE式におけるELSEが機能していない？

Question

前提・実現したいこと

後述のテーブルで、以下ようなSQLを実行した際に、期待通りの結果を返してほしい

with rows as (
	select test.id, count(*) cnt
	from test
	join test2 on test.id = test2.id2
	where test.foo = 値1
	group by test.id
),
temp as (
	select id
	from rows r2
	where r2.cnt = (select max(r1.cnt)
		from rows r1)
),
temp2 as (
	select test.id
	from test
	where test.id = 値2
)

select case
	when temp.id is not null then temp.id
	when temp2.id is not null then temp2.id
	else 99
end as result
from temp, temp2

test.idとtest2.id2の部分で内部結合させ、test.idごとのレコード数をカウントし、そのレコード数が最大のものがある場合は、そのid(temp.id)を、次にtest.idにはあるがtest2.id2にそれと同じ値がない場合は、そのtest.id(temp2.id)を、これら以外の場合(想定外、テスト目的なので本来は不必要)の場合は99を出力したいです

また、

• 値1と値2の組み合わせは、testテーブルのレコードに基づいて指定されます(具体的には、testテーブルのレコードのそれぞれの値が、ある1つのURIのパラメータに含まれているようなイメージです)
• test.idに存在しない値2は指定しません(temp2が最後の受け皿なので)

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

• test2.id2に存在しない値2を指定した組み合わせの場合の結果が期待通りでない
• CASE式におけるELSEが機能していない?

該当のソースコード

testテーブル

id	foo
1	1
2	1
3	1
4	2

CREATE文

CREATE TABLE `test` ( `id` INTEGER, `foo` INTEGER NOT NULL, PRIMARY KEY(`id`) )

---

test2テーブル

id	id2
1	1
2	2
3	2
4	3
5	3
6	3

CREATE文

CREATE TABLE `test2` ( `id` INTEGER, `id2` INTEGER NOT NULL, PRIMARY KEY(`id`) )

試したこと

• testテーブルとtest2テーブルを内部結合した結果のテーブルにレコードが存在する場合のtest.idを値1にしていした場合

with rows as (
	select test.id, count(*) cnt
	from test
	join test2 on test.id = test2.id2
	where test.foo = 1
	group by test.id
),
temp as (
	select id
	from rows r2
	where r2.cnt = (select max(r1.cnt)
		from rows r1)
),
temp2 as (
	select test.id
	from test
	where test.id = 1
)

select case
	when temp.id is not null then temp.id
	when temp2.id is not null then temp2.id
	else 99
end as result
from temp, temp2

結果(期待通り)

result: 3

---

• test2.id2に存在しない値2を指定した組み合わせの場合

with rows as (
	select test.id, count(*) cnt
	from test
	join test2 on test.id = test2.id2
	where test.foo = 2
	group by test.id
),
temp as (
	select id
	from rows r2
	where r2.cnt = (select max(r1.cnt)
		from rows r1)
),
temp2 as (
	select test.id
	from test
	where test.id = 4
)

select case
	when temp.id is not null then temp.id
	when temp2.id is not null then temp2.id
	else 99
end as result
from temp, temp2

結果(期待通りではない)

結果なし(0レコード)

期待する結果

result: 4

• 最後の受け皿なので、絶対にここで結果を返してほしい

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

Microsoft Windows10 バージョン 1903(OSビルド 18362,1016)
SQLite 3.22.0

---

よろしくお願いいたします

Answer 1

結果が却って来ないのは、直積の片側が0件なら、結果(0との積算)は0件だからです。
full join すれば結果は却ります。

select case
    when temp.id is not null then temp.id
    when temp2.id is not null then temp2.id
    else 99
end as result
from temp full join temp2
     on temp.id=temp2.id

やりたいことがいまいち理解できませんが、組み替えてみました。

with rows as (
  select id2, count(*) cnt from test2
  group by id2
), temp as (
   select id2 from rows
   where cnt = (select max(cnt) from rows)
)
select *
from test
where (
         id in (select ID2 from temp) -- レコード数が最大のものがある場合
     or not exists( -- 同じ値がない場合
           select 1 from test2 where id2=test.id
         )
     )
 and (foo = 値1 or id = 値2)

Comments

[defeatist]

ありがとうございます