サブクエリでおおもとのクエリのエイリアスを使った時のエラーを説明して下さい。

Question

select teamName , positionName ,uniformNo ,playerName
from player p
join team using(teamNo)
join position1 using(PositionNo)
where positionNo =
(
	select positionNo
from player 
where p.teamNo = 2  and p.uniformNo=5
);

上記のクエリを実行すると、サブクエリの結果が2つ以上ありますとでました。

そこで、5行目の＝をinに変えたところ結果はサブクエリでの条件がおおもとのクエリの条件になった場合（下のコード）と一致しました。

内部でどのような処理が行われて、サブクエリでどのようなテーブルが呼び出されたのでしょうか。

自分の予想では、サブクエリで呼び出されたテーブルはplayerそのものとおおもとのクエリでjoinしたplayerテーブルを下のwhere句で絞ったものが
呼び出されて、playerテーブルにはteamNameとpositionNameのカラムがないのでjoinされたテーブルのみ該当してinにしたとき、playerテーブルのみと一致したものは除外されたと考えたのですが、そうするとinにしたときの結果が1件なのが説明できません。

select teamName , positionName ,uniformNo ,playerName
from player p
join team using(teamNo)
join position1 using(PositionNo)
where p.teamNo = 2  and p.uniformNo=5;

playerにはteamNo,positionNo,uniformNo,playerName　、
teamには teamNo , teamName 、
position1には　positionNo, positionName　のカラムがあります。

追記

CREATE TABLE Position1(
		SportNo			VARCHAR(2)	 NOT NULL,
		PositionNo		VARCHAR(2)	 NOT NULL,
		PositionName	VARCHAR(20)	 NOT NULL,
	PRIMARY KEY(SportNo,PositionNo)) CHARSET=SJIS;
CREATE TABLE Player(
		TeamNo		VARCHAR(2)	NOT NULL,
		PositionNo	VARCHAR(2)	NOT NULL,
		UniformNo	VARCHAR(2)	NOT NULL,
		BirthDay	DATE		NOT NULL,
		PlayerName	VARCHAR(20) 	NOT NULL,
	PRIMARY KEY(TeamNo,UniformNo)) CHARSET=SJIS;
CREATE TABLE Team(
		SportNo		VARCHAR(2) NOT NULL,
		LeagueNo	VARCHAR(2) NOT NULL,
		AreaNo		VARCHAR(2) NOT NULL,
		TeamNo		VARCHAR(2) NOT NULL,
		TeamName	VARCHAR(50) NOT NULL,
         PRIMEKEY(TeamNo)) CHARSET=SJIS;

やろうとしたことはチーム番号２、背番号５の人と同じポジションの人のチーム名、ポジション名、背番号、選手名を取得することです。

ちなみにこれは演習として作ったクエリです。

皆さんの回答を見て、勘違いされているようなので追記します。
私が知りたいのは「このクエリ文で呼びされたサブクエリの中身」から「クエリ文が実行されたときの内部処理の内容」であって、
エラーの直しかたではないです。サブクエリ文のエイリアスをとればいいのは分かってます。

質問は

内部でどのような処理が行われて、サブクエリでどのようなテーブルが呼び出されたのでしょうか。

ですから。

このエラーの直し方を教えてください

ではないです。

Comments

[Orlofsky]

テーブル定義はCREATE TABLEで提示しては？

[Y.H.]

あと、どういうデータが入っていて、どういう結果が欲しいのかも質問に記載ください。

[Y.H.]

Teamテーブルの主キーは何ですか？

[Y.H.]

>このエラーの直し方を教えてください
>ではないです。
であれば質問のタイトルを修正されることをお勧めします。

Answer 1

そもそもやる意味のないSQLなので挙動を確認する意味があまり感じられませんが
具体的にどうやって判断されているかを確認したいなら
もしphpMyAdminを導入していれば、explainをかけて
クエリ結果操作欄に表示されるオプティマイズされたSQL文を見比べてみるとよいでしょう

Answer 2

5行目の＝をinに変えたところ結果はサブクエリでの条件がおおもとのクエリの条件になった場合（下のコード）と一致しました。

条件での=は単一の結果を期待するもので、inは集合です。

なので、サブクエリーでの結果が複数である場合、=だとエラーになります。
これはselectに記述するサブクエリーも=の結果を期待するので、同様にエラーになります。

そうするとinにしたときの結果が1件なのが説明できません。

サブクエリーで返却されるpositionNoの内容が１パターンしかないという単なる結果です。
サブクエリーにDISTINCT指定をすれば=にしてもエラーにはならないでしょう。

where positionNo =(
        select distinct positionNo
        from player 
        where p.teamNo = 2  and p.uniformNo=5
      )

但し、それはサブクエリーがpositionNoを１パターンしか返さない場合だけです。

追記

やろうとしたことはチーム番号２、背番号５の人と同じポジションの人のチーム名、ポジション名、背番号、選手名を取得することです。

それなら、サブクエリーが相関になっているのはおかしいです。
以下なら=でもエラーにならないと思います。

select teamName , positionName ,uniformNo ,playerName
from player p
join team using(teamNo)
join position1 using(PositionNo)
where positionNo =(
        select positionNo
        from player 
        where teamNo = 2  and uniformNo=5
     )

追記２

皆さんの回答を見て、勘違いされているようなので追記します。

「サブクエリでおおもとのクエリのエイリアスを使った時のエラーを説明して下さい。」
タイトルが、勘違いの元です。
そもそも、p.というのはエイリアスではなくて修飾子と言います。

内部でどのような処理が行われて、サブクエリでどのようなテーブルが呼び出されたのでしょうか。

サブクエリー内でfrom句に記述されたテーブル等を参照するものを、相関サブクエリー（相関副問合せ）と云います。

相関副問合せでは、参照される側(fromにある記述)が先実行されてから、参照する側が実行されます。
参照される側1件に対して都度実行されるのが基本です。
オプチマイザや内容によって事前に抽出が行われる事もあるでしょうし、どのような実行順序となっているかは、実行計画で確認する方が良いでしょう。

Answer 3

追記

(コメントより)

仕組みを理解したくて質問させていただきました
inにした場合の結果が一件だけなのでしょうか

サブクエリーの結果は全件取得されています。

実行計画（実行プラン）を確認してみないと正確な事は判りませんが、以下と同等な実行がなされたのではないでしょうか？
※本質問のような疑問に対しては実行計画（実行プラン）を確認すると解ることが多いです。使用方法および結果の見方を覚えておかれると役立つと思います。(また、SQLの性能改善には利用必須の機能となります)

親のselect文のテーブルplayer pに対してサブクエリーのwhere p.teamNo = 2 and p.uniformNo=5(キー項目による絞り込み)が評価され1件が抽出ます。
このレコードがサブクエリー（結果：player全件）に存在するため、sql全体として1件抽出される結果となります。

(
    select teamName , positionName ,uniformNo ,playerName
    from player 
    where teamNo = 2  and uniformNo=5
) p
join team using(teamNo)
join position1 using(PositionNo)
where positionNo in
(
    select positionNo
    from player
);

※ saziさんの回答（追記2）にも記載されていますが、このようなMySQLの動作に関する事柄についてはMySQLのマニュアル「8.2.1.18.4 EXISTS 戦略によるサブクエリーの最適化」に記載されています。
この部分来限らず、MySQLについての動作原理について疑問を解消（または理解を深める）ならば、公式のマニュアルを読まれることをお勧めします。

---

このサブクエリーですが、Playerとの結合がないのでplayerテーブルの全件が取得されます。

select positionNo
from player 
where p.teamNo = 2  and p.uniformNo=5

具体的なテーブル定義とデータの提示がないので何がやりたいのかわかりませんが、以下ではないでしょうか？

select positionNo
from player 
where player.teamNo = 2 and player.uniformNo = 5

Comments

[snowshink]

はい、そのクエリで実行したら確かに実行されました。
今回はエラーから仕組みを理解したくて質問させていただきました。

[Y.H.]

> where p.teamNo = 2 and p.uniformNo=5
だと、サブクエリー内でselect しているPlayerテーブルのteamNo, uniformNoに対して条件を与えていないので、サブクエリー内は(select positionNo from player)としてのと同じです。
よって、2件以上のpositionNoがサブクエリーの結果となります。
よって親のselect分のwhere条件は以下となります。
where positionNo = (2件以上のpositionNo)
ここで、= は、単一の値の比較を行う演算子であることはわかりますか？
右辺が(2件以上のpositionNo)となっているため「サブクエリの結果が2つ以上あります」のエラーとなります。
Whereの条件で回答記載のように、
> where Player.teamNo = 2 and Player.uniformNo=5
とすることで、サブクエリの結果が１件となり（データがわからないので結果として１件になっている可能性もある）=で比較可能となりました。

[snowshink]

ではなぜ、inにした場合の結果が一件だけなのでしょうか。
解答から推測すると全件出てくるように思えるのですが

[Y.H.]

回答に追記していますが、これでは回答になってませんでしょうか？