サブクエリを用いたデータの取得

Question

SELECT文で取得するカラム名を指定する箇所でサブクエリを使って、countriesテーブル内のすべての国について，対応するcountries.idをusers.country_idに持つユーザー数を取得したいです。

usersテーブル

id	name	country_id
1	brian	2
2	emma	2
3	aubrey	2
4	mitsuru	1
5	brandon	2
6	shunji	1

countriesテーブル

id	name
1	Japan
2	USA
3	China
4	India

以下のSQLを作成して実行したのですが、「Japan,2　USA,4　China 0」という結果になり、Indiaのデータが取得できません。

select name,
(
	select count(*)
	from users
	where country_id = c.id
) as user_count
from countries as c;

「Japan,2　USA,4　China 0　India 0」という結果を得るためには、上記のSQLをどのように修正すればよろしいでしょうか？
よろしくお願いいたします。

Comments

[Orlofsky]

質問のテーブル定義はCREATE TABLEに、テーブル中のデータはINSERTに https://teratail.com/help/question-tips#questionTips3-7 の [コード]
で見易さを考えて修正できた方が適切なコメントが付き易いです。
SELECTも上記のMarkdownに含めてね。

[Orlofsky]

同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで示したり、バージョンも明記した方が適切なコメントが付き易いです。SQLの観点から Oracle Database, PostgreSQL, MySQL の特徴を整理しよう！ https://www.ashisuto.co.jp/corporate/column/technical-column/detail/1197236_2274.html

[Orlofsky]

SQLでSELECTを実行する為にはCREATE TABLEが実行されてテーブルが存在していなければなりません。SELECTを実行した時にテーブル内に存在するデータを取得される為には、既にデータがINSERTされていなければなりません。(これを理解できなかったら職業を変えた方が人生失敗が少ないです)

CREATE TABLEやINSERTとは Markdown の表で提示しろ、って意味ではありません。
https://www.dbonline.jp/sqlite/table/index1.html
https://www.dbonline.jp/sqlite/insert/index1.html

Answer 1

ヒントで(動作確認していません)

select
    c.name
  , u.count(*) as counts  -- u.count(*) → count(*) で良いかも？
from countries as c
left outer join users as u
on c.id = u.coutry_id  -- キャプチャが coutry_id 
group by c.name
;

Comments

[退会済みユーザー]

ご回答ありがとうございます。
いただいたヒントを元に以下のSQLを実行したところ、
「Japan,2　USA,4　China 1　India 1」という結果が返ってきました。

select c.name, count(*) as counts
from countries as c
left outer join users as u on c.id = u.country_id
group by c.name;

countryテーブル内のidにusersテーブルのChinaとIndiaのcountry_idは存在しないので
0が出力されると思ったのですが、SQLにさらに変更を加える必要がありますでしょうか？

[Yasumichi]

count(u.name) に変えたらどうなりますか？

[m.ts10806]

「ヒント」なのでそのまま使えると思わない方が良いのではと・・・。
考え方を活かしてやってみてはどうでしょうか。

[Orlofsky]

はい、ヒントはあくまでもヒントです。
汗水流さないと身につかないこともありますから、
最近、皆さん甘やかし過ぎかと思うことも？

[kaikubo_ko]

上司でもないのに質問サイトで何言ってるんだか・・・
ここは技術指導する場所ではないですよ？？？？

[Orlofsky]

最近は目先のコストを最優先する会社が多く、若い技術者に大切な情報を伝承する機会が極端になくなりました。
たまにOracle Database のパフォーマンス・チューニング案件が来ると処理時間が半分以下に改善できるようなものばかりです。最初から呼んでもらえたら何億円も浮いただろうに。

Answer 2

SELECT文で取得するカラム名を指定する箇所でサブクエリを使って

という条件なら、CASE文でデータが取得できない場合（NULLの場合）は0とすれば良いのでは。
すでについてる他の回答はJOINを使ってるのでこの条件を満たしてないように思えますが。

Comments

[退会済みユーザー]

確かにそうですね。

Answer 3

ご提示していただいたサブクエリですとusersテーブルにIDがないためIndiaのデータを
カウントすることはできません。

理解を深めるために下記を参考にされるといいかと思います・・・。

Qiita SQL素人でも分かるテーブル結合(inner joinとouter join)

内部結合、外部結合について、インターネットで調べると色々出てきます。

Comments

[退会済みユーザー]

すみません。DB名とかテーブル名とか検証用にいじったので少しちがうかもしれないことをご了承ください。MariaDB （mysql）の例です。

MariaDB [kuni]> select c.name,count(u.contry_id) as '件数' from contry as c left outer join users as u on c.id = u.contry_id group by c.name;

+-------+------+
| name | 件数 |
+-------+------+
| CHINA | 0 |
| India | 0 |
| japan | 2 |
| USA | 4 |
+-------+------+
4 rows in set (0.00 sec)

create database kuni;

use kuni

create table users(
id int,
name varchar(20),
contry_id int
);

create table contry(
id int,
name varchar(20)
);

insert into users(id,name,contry_id) values('1','brian','2');
insert into users(id,name,contry_id) values('2','emma','2');
insert into users(id,name,contry_id) values('3','aubery','2');
insert into users(id,name,contry_id) values('4','mitsuru','1');
insert into users(id,name,contry_id) values('5','brandon','2');
insert into users(id,name,contry_id) values('6','shunji','1');

insert into contry(id,name) values('1','japan');
insert into contry(id,name) values('2','USA');
insert into contry(id,name) values('3','CHINA');
insert into contry(id,name) values('4','India');

[退会済みユーザー]

ご回答ありがとうございます。
テーブル結合について理解が不足していたので、
添付いただいたリンク等を参考に理解を深めていきます。

[m.ts10806]

ymakotoさん
テーブル名、カラム名はあわせてあげてください。
contry　はそもそも英語として存在しないような。

[退会済みユーザー]

そうですね。ありがとうございます。

[退会済みユーザー]

ymakotoさん
ご教授いただいた例で実行したところ、countriesテーブル内のすべての国について、対応するcountries.idをusers.country_idに持つユーザー数を取得することができました。
しかし、「Chine,0　India,0　Japan,2　USA,4」の順に取得されますが、今回は「Japan,2　USA,4　China,0　India,0」の順に取得する必要があります。
order by句を用いて並び替えを試みましたが、group by句と併用する際にはselect句の列を指定しなければならないとのことで行き詰まってしまいました。
もし可能であれば、並び替えについて手を貸していただけますと幸いです。