質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.04%

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

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 348

snowshink

score 62

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;

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

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

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

質問は

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

ですから。

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

ではないです。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Orlofsky

    2019/04/26 10:33

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

    キャンセル

  • Y.H.

    2019/04/26 10:42

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

    キャンセル

  • Y.H.

    2019/04/26 10:59

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

    キャンセル

  • Y.H.

    2019/04/27 15:23

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

    キャンセル

回答 3

+1

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

checkベストアンサー

0

追記

(コメントより)

仕組みを理解したくて質問させていただきました
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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/04/26 10:54

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

    キャンセル

  • 2019/04/26 11:10

    > 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
    とすることで、サブクエリの結果が1件となり(データがわからないので結果として1件になっている可能性もある)=で比較可能となりました。

    キャンセル

  • 2019/04/29 07:26

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

    キャンセル

  • 2019/05/06 03:23

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

    キャンセル

0

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

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

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

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

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

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


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

追記

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

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

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
     )

追記2

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

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

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.04%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる