SQLで複数のテーブルを選択した際の優先順位を知りたいです

Question

Aというテーブルがあったとします。

AにはId列のみ
1
2
3
4
というデータが入ってます。
そしてinfoテーブルにはID列とname列が
3 田中
2 高橋
のように入っているとします。
そして下記のようなSQLを実行したとします。

Select a.id,info.name from a,info where a.id=info.id;
これを実行すると
3 田中
2 高橋

が返されるのですが、

1 null
2 高橋
3 田中
4 null
のようなデータが欲しい場合はどうしたらいいのでしょうか？join句を使えばいいのはわかりますが、where句で上記のSQLをどのように並び替えても同じ結果しか表示されませんでした。
where句を使った際のテーブルの結合の優先順位、ロジックなどを交えて教えて欲しいです。

Answer 1

考え方

• 元データ

create table A(id int primary key);
insert into A values(1),(2),(3),(4);
create table info(id int primary key,name varchar(10));
insert into info values(3,'田中'),(2,'高橋');

left join

ご理解いただいていると思いますが普通にやるならleft join

select *from A left join info using(id);

joinしない

別にjoinしないでもこうすれば得られます

select A.id,(select name from info where A.id=info.id) as name from A

whereで結合

ではwhere句に列記したらなぜとれないのか？

select a.id as a_id,info.id as info_id,info.name from a,info;

aとinfoのデータを単純に結合するとこうなってしまうので
nullは発生しません。

a_id	info_id	name
1	2	高橋
1	3	田中
2	2	高橋
2	3	田中
3	2	高橋
3	3	田中
4	2	高橋
4	3	田中

これを応用して

select a.id,case when a.id=info.id then name else null end as name from a,info

とすれば、以下のようになります

id	name
1	NULL
1	NULL
2	高橋
2	NULL
3	NULL
3	田中
4	NULL
4	NULL

これも希望する解とは違います
あえてやるならこう

select  a.id,group_concat(case when a.id=info.id then name else null end )as name from a,info
group by id

しかし、こんなことをするくらいなら普通はleft joinで十分です

Answer 2

OUTER JOINを使用してください。

SELECT a.id,info.name
FROM a
LEFT OUTER JOIN info
ON a.id=info.id

Comments

[babbleman]

Where句のみを使って実行する事は出来ないのでしょうか？
なぜ自分が示した一通りの答えが出ないのかの理由が知りたいです。

[ryo_y]

from句で複数のテーブルを指定した場合、全ての行の組み合わせからデータを取得します。
例)
Aテーブルの1行目+infoテーブルの1行目
Aテーブルの1行目+infoテーブルの2行目
Aテーブルの2行目+infoテーブルの1行目
Aテーブルの2行目+infoテーブルの2行目
...

この時、info.idの値が1,4の行が存在しないため、a.idが1,4の行に対してa.id=info.idの条件を満たす行は存在しないので、取得できるデータはa.idが2,3の分のみとなります。

[babbleman]

回答どうもありがとうございます。
今の例ではAの値に対して対応するinfoを組み合わせるという事でしたがその逆でinfoにAを組み合わせる事は出来ないのでしょうか？
そうすると出力結果は
3 高橋
2 田中
になるはずなのですが

[ryo_y]

なぜその出力結果になると思ったのかはわかりませんが、複数指定の場合は全ての組み合わせを総当りで結合するので、どちらのテーブルが優先といったことはありません。

Answer 3

join句を使えばいいのはわかりますが、where句で上記のSQLをどのように並び替えても同じ結果しか表示されませんでした。

outer joinを使用する事は分っているのですね。
並びは、結合の際のアルゴリズムによって左右される部分がありますが、保証はされないのが普通です。
求めたい結果はID順なので、order byでIDを指定して下さい。

Answer 4

DBMSによって様々なので知ってる範囲で回答します。

Oracleなら外部結合演算子(+)が一応使えます。
https://docs.oracle.com/cd/E16338_01/server.112/b56299/queries006.htm#sthref2254

SQL Serverなら外部結合演算子(=, =)が互換モード90より前なら使えます。
https://docs.microsoft.com/ja-jp/sql/sql-server/install/outer-join-operators-and-are-not-supported-in-90-or-later-compatibility-modes?view=sql-server-2014

これ以外はわかりません。

Answer 5

表示順を指定したいときは ORDER BY 表示したい順のカラム を指定してください。

Comments

[babbleman]

すみません、知りたいのは表示順序ではなくwhere句を使った際のテーブルの結合のアルゴリズムです。

[Orlofsky]

実行計画を確認してください。実行計画の取得方法はデータベースによって違います。
> データベース名 実行計画の取得方法
でググってください。

[Orlofsky]

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