Closed11日前にクローズ1

ログ：思ったより奥の深いサブクエリの世界

MySQL

SQL

読書ログ

サブクエリ

もりた 11日前

これはなに

相関サブクエリについて調べてたらちょっとよくわかんなくて、そもそもサブクエリのことあんまり知らないかもってなって調べるやつ

調べるもの

以下のものを調べようかなと思ってる

対象
- MySQL公式 13.2.11 サブクエリー
  - https://dev.mysql.com/doc/refman/8.0/ja/subqueries.html
- 『プログラマのためのSQL』
  - これも

MySQL公式

13.2.11 サブクエリー

メリット
- 読みやすい
サブクエリーの種類
- スカラー、カラム、行、およびテーブルサブクエリー
使用できるステートメントのタイプ
- 「サブクエリーを使用できるステートメントのタイプに関する制限はほとんどありません。サブクエリーには、DISTINCT、GROUP BY、ORDER BY、LIMIT、結合、インデックスヒント、UNION 構造構文、コメント、関数などの、通常の SELECT に含めることのできる多くのキーワードや句を含めることができます。」
  - これサブクエリ内にってこと？　それともサブクエリを置きたいステートメント？
  - いや多分これ詳細に書いてるんじゃなくて別の文章として書いてるのか。どこにでも書けるし、なんでも含められる。
MySQL 8.0.19 以降、TABLE および VALUES ステートメントをサブクエリーで使用できます。
- ？？？　わからん

13.2.11.1 スカラーオペランドとしてのサブクエリー

スカラー値をとるサブクエリ
公式ドキュメントでは１レコードしかないテーブルを作ってそれでスカラー値を取って、SELECTのところで使ってる
- けど多分これはサブクエリでMAXとかそういうのを作ってやるケースが多いのでは？
- ちなみに複数レコード、つまりカラムが渡されて、それが関数に入れられてたらどう動くの？
  - これはだめ。サブクエリの方でMAXとか書かないとだめ。

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(100, 'abcde');
INSERT INTO t1 VALUES(200, 'abcde');
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);

SELECT (TABLE t2) FROM t1;
-- これはできる
select MAX(s1) from t1;
-- これもできる
SELECT (select MAX(s1) from t1) FROM t1;
-- これもできる。2レコード返されたのは、t1が2レコードあるから。全てのレコードに対してスカラー値の200を返している。
SELECT MAX((select s1 from t1)) FROM t1;
-- Error Code: 1241. Operand should contain 1 column(s)
SELECT (table t1) FROM t1;
-- Error Code: 1241. Operand should contain 1 column(s)

そのほかにはWHEREでの比較とかにも使えるんだろうな
- FROMは使えるか？
  - できる。単純にそれをテーブルとみなすだけ。まあそうか。スカラーはテーブルになりうる。テーブルはスカラーになれないけど。

13.2.11.2 サブクエリーを使用した比較

サブクエリとの比較
- = > < >= <= <> != <⇒ like
- <>じゃないんだ？
結合では同じことできないやつ
- 「これもまた、いずれかのテーブルに対する集約が含まれているため、結合では実行できません。」
- そこが目安なんだ。なんでだろ？
  - 結合する前に集約しないといけないからか。だからサブクエリ作らないといけないのね
  - 同じことをいうと、集合演算以外の操作をしようとすると結合だけじゃあダメってことかな？

SELECT * FROM t1
  WHERE column1 = (SELECT MAX(column2) FROM t2);

「スカラーに対するサブクエリーの比較の場合、サブクエリーはスカラーを返す必要があります。行コンストラクタに対するサブクエリーの比較の場合、サブクエリーは、その行コンストラクタと同じ数の値を含む行を返す行サブクエリーである必要があります。」
- なるほどね。
- あとSELECTにはスカラーだけか？　他は割と自由にできそうな気がするが。

13.2.11.3 ANY、IN、または SOME を使用したサブクエリー

ANY, IN, SOME
- = ANYはINのエイリアス
- 逆にいうとANYはINみたいな感じで使えるやつ
比較対象に単一カラムのテーブルを取った時のことが書いてある
- 単一カラムならテーブルでも扱えるみたい。論理包含の逆の動きができるんだな
つまりここでは、単一カラムを返すサブクエリが想定されているってことかな
- 同じ型の絡むしかない行だったらどうなる？
  - ダメだね。テーブルだもんね。
- けどこれ明示的に１行だけってなってたらどうなんだろう。
  - ほえ〜なんか違う結果になった。本当はこれもOKにしたいけど、このバージョンではダメってこと？

CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);
create table numbers_table (n1 int, n2 int, n3 int);
insert into numbers_table (n1, n2, n3) value (10, 200, 3000);
insert into numbers_table (n1, n2, n3) value (20, 400, 6000);
insert into numbers_table (n1, n2, n3) value (30, 600, 9000);

select * from t2 where t2.s1 < any (select n1 from numbers_table);
-- OK
select * from t2 where t2.s1 < any (select * from numbers_table where n1 = 10);
-- Error Code: 1241. Operand should contain 1 column(s)
select * from t2 where t2.s1 < any (select * from numbers_table where n1 = 10 limit 1);
-- Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

13.2.11.4 ALL を使用したサブクエリー

ALLの使い方
テーブルが空である時の挙動が難しい。論理学的な何かなんだろうけど理解できてない
- けどこれはエッジケースで障害を生みやすいので確認しろと書いてある

-- t2が空

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);
-- true
SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);
-- null
SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);
-- null

NOT INと<> ALLはエイリアス

13.2.11.5 行サブクエリー

単一行を返すサブクエリ

SELECT * FROM t1
  WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1
  WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

サブクエリの結果が空白の場合NULLが返り、複数行が選択される場合はエラーになる。

select * from numbers_table as nt where (nt.n1, nt.n2) = (select n1, n2 from numbers_table where n1 = 10);
/*
# n1, n2, n3
10, 200, 3000
*/
select * from numbers_table as nt where (nt.n1, nt.n2) = (select n1, n2 from numbers_table where n1 in (10, 20));
-- Error Code: 1242. Subquery returns more than 1 row
select * from numbers_table as nt where (nt.n1, nt.n2) = (select n1, n2 from numbers_table where n1 in (100, 200));
/*
# n1, n2, n3
空白
*/

ROW(n1, n2)みたいな書き方を行コンストラクタと呼ぶ。これを分解してひとつずつ比較しても良い。パフォーマンス的にも問題ない。
感想
- 同じ値の行を出す、みたいなのはこれだと確かにシンプルかも
- けど結合でやるともっとシンプルに考えられる気もするが、どうなんだろう。これ実行計画的にはどうなるんだろ？？？

explain;
select * from numbers_table as nt where (n1, n2, n3) IN (select n1, n2, n3 from numbers_table);
/*
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, nt, , ALL, , , , , 3, 100.00, 
1, SIMPLE, numbers_table, , ALL, , , , , 3, 33.33, Using where; FirstMatch(nt); Using join buffer (hash join)
*/

explain;
select nt1.n1, nt1.n2, nt1.n3 from numbers_table as nt1 left join numbers_table as nt2 on nt1.n1 = nt2.n1 and nt1.n2 = nt2.n2 and nt1.n3 = nt2.n3;
/*
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, nt1, , ALL, , , , , 3, 100.00, 
1, SIMPLE, nt2, , ALL, , , , , 3, 100.00, Using where; Using join buffer (hash join)
*/

ほぼ変わらないけど何度か実行すると後者の方がちょっとはやいとわかる。FirstMatchがどう影響してるのか。
- 関連してそうな記事を漁る
  - https://tech.bm-sms.co.jp/entry/2022/12/13/120000
  - https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0043
- なるほど。内部的には結合みたいな動きをするのね
- でまあ基本的には結合したほうが早いってことかな？　ちゃんと調べてないけど

13.2.11.6 EXISTS または NOT EXISTS を使用したサブクエリー

これは多分相関サブクエリで使われることを想定してる
- サブクエリ内の条件が成立するような外部クエリを表示する
- EXITSだと結合でできそうだけど、NOT EXISTだと差集合とか使わないとできないかな？
  - MySQLには差集合がない…
  - 参考
    - https://qiita.com/Hiraku/items/71873bf31e503eb1b4e1
そして入れ子のNOT EXISTがだいぶ難しい

SELECT DISTINCT store_type FROM stores s1
  WHERE NOT EXISTS (
    SELECT * FROM cities WHERE NOT EXISTS (
      SELECT * FROM cities_stores
       WHERE cities_stores.city = cities.city
       AND cities_stores.store_type = stores.store_type));

これ理解できる？？？
- これは多対多の中間テーブルを使ってるのか
- そんで一行ずつ見ると、
  - 二重ループの中で、特定のストアタイプ、特定の市の店舗を見ていって、
  - 特定のストアタイプでそこに含まれない市を出して、
  - 含まれない市がないストアタイプを出す
- 二重ループであることを意識して、どのサブクエリでどのパラメータが固定されているのかを意識しないときついな、けど落ち着いて考えればわかる
  - かつ相関サブクエリの利用例が見れてよかった

13.2.11.7 相関サブクエリー

外部クエリーのテーブルへの参照を含むサブクエリー
- まあ、そやな！
これがどうメリットのある書き方なのか？　というのを本当は知りたいんだよなあ

13.2.11.8 導出テーブル

FROM句でテーブルを生成するサブクエリを導出テーブルと呼ぶっぽい？
- これは割と一番馴染みのあるサブクエリな気がするな
- 本当にFROM句だけか？
書き方
- サブクエリー内のカラム名は外部クエリーで認識される
- カラム名を指定することも可能（SELECT * FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d);）。
「導出テーブルは、スカラー、カラム、行またはテーブルを戻すことができます。」
- どういうこと？
- 「A derived table can return a scalar, column, row, or table.」
  - なるほどね。別にテーブルじゃなくてもいいよ（テーブルとして読み取るから）ってことね
制限事項
- 同じ階層（？）のクエリ内で他テーブルへの参照を含められない
  - これは一般的なサブクエリが外部クエリの参照ができることを意識してるんだろうな
  - 例えば同じFROM句に書いてある別のテーブルをサブクエリ内に登場させるのとかはだめ
    - LATERAL導出テーブルにする必要がある
- （ちょっと分かりにくいけど）さらに上の階層への外部参照は8.0.14からできるようになった
ちょっと関係性がわかんないけど、EXPALIN SELECT f(c);としたとき、本来は実際には関数が実行されないんだけど、多層なクエリにして外部クエリで何かのテーブルにアクセスすると関数が実際に実行されてしまう
- これがダメEXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2\G

13.2.11.9 ラテラル導出テーブル

導出テーブルは、通常、同じ FROM 句内の前述のテーブルのカラムを参照 (依存) することはできません。
- が、以下の制限事項の範囲で、LATERALをつけるとそれができるようになる
制限事項
- FROM句でのみ使える
- 「ラテラル導出テーブルが結合句の右オペランドにあり、左オペランドへの参照が含まれている場合、結合操作は INNER JOIN、CROSS JOIN または LEFT [OUTER] JOIN である必要があります。」
  - 左側が絶対にあるケース、比較対象があるケースってことね
- 「ラテラル導出テーブルが集計関数を参照する場合、関数集計クエリーを、ラテラル導出テーブルが発生する FROM 句を所有するクエリーにすることはできません。」
  - どういうことだ。。
  - LATERAL導出クエリが書かれてるFROM句を持ってるSELECTのところから集計関数の結果を引っ張ってこれないってこと？？？　わからんな
- 「 SQL 標準に従って、テーブル関数には暗黙的な LATERAL があるため、8.0.14 より前の MySQL 8.0 バージョンと同様に動作します。ただし、標準に従って、LATERAL ワードは暗黙的であっても JSON_TABLE() の前には許可されません。」
  - 暗黙的につくんだ
  - けどJSON_TABLE()関数の前ではできない
    - なにこの関数
    - JSONからテーブルを作れるやつか
読み方
- 制限（「ラテラル導出テーブルが結合句の右オペランドにあり…」）にある通り、LATERAL句はどこに対して依存しているのか？　を明示している。それを意識して読むと良い
メリット
- 相関サブクエリを導出クエリーでやるってケースなわけだから、相関サブクエリ的なメリットがある
- 例えば参照先ごとのグルーピングと集計関数での計算とか？

13.2.11.10 サブクエリーのエラー

エラー
- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"
  - あこれあったな、INとかのサブクエリの中でLIMIT使えないんよな
- Operand should contain 1 column(s)
  - 行が返っちゃってるやつ
  - 「ほかのコンテキストでは、サブクエリーはスカラーオペランドである必要があります。」
    - スカラーだけじゃなくね？
- Subquery returns more than 1 row
  - 複数行返ってきてる
  - ANYとかで対応する
- "You can't specify target table 'x' for update in FROM clause"
  - UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
    - これダメなんだ
  - 導出テーブルか共通テーブル式でできるらしい
「トランザクションストレージエンジンの場合は、サブクエリーが失敗するとステートメント全体が失敗します。非トランザクションストレージエンジンの場合は、エラーが検出される前に行われたデータ変更が保持されます。」
- innodbならいいけどMySAMみたいなやつだと保持されちゃう

13.2.11.11 サブクエリーの最適化

サブクエリの中で演算とかやっちゃう方がいい
- 一度にできるからってことだろうな
SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);
- こういうの。UNIONを使わずふたつのサブクエリに分解することも可能だけど、やらない

8.2.2 サブクエリー、導出テーブル、ビュー参照および共通テーブル式の最適化

13.2.11.12 サブクエリーの制約

できないこと
- テーブルの変更とか
  - DELETE, UPDATEをやるときに外部クエリと同じテーブルをサブクエリで選択することはできない
- 行比較演算は一部のみサポート
  - ？？？
  - ああ、行比較ね！
    - 行比較演算子テーブルサブクエリみたいな書き方
    - (*expr_1*, ..., *expr_n*) [NOT] IN *table_subquery*
  - これINなら使えるけど、ANYとかSOMEとかだと使えないって
- 「MySQL 8.0.14 より前は、FROM 句のサブクエリーを相関サブクエリーにすることはできません。」
  - へー
- LIMIT
  - IN, ANY, SOME, ALLでは使えない
- 「MySQL では、サブクエリーで行をテーブルに挿入するなどのデータ変更の副作用があるストアドファンクションを参照できます。たとえば、f() が行を挿入する場合、次のクエリーはデータを変更できます。」
  - そうなのかもしれんけど、絶対にやりたくないし、そもそもSQLで関数を作るのってあんまりそうしたい理由がわかんないかも
感想
- 知ったもの
  - LATERAL導出テーブル
  - IN, ANY, SOME, ALL
  - EXIST
    - これは相関サブクエリに向いている
- サブクエリのメリット
  - サブクエリの中で集計するやつもサブクエリでしかできない
  - これは結合ではできないという意味なので、結合はできず相関サブクエリにするしかないケースでもこれが言えるはず
- 相関サブクエリのメリット
  - EXISTが使える
- かけそうなこと
  - どこにどういうレベルのサブクエリが書けるかってのは整理できるかも？
    - SELECTは多分スカラーだけ、行はむりだし列も無理だしテーブルも無理
    - FROMはなんでも書けるけど全てテーブルとして解釈される
    - WHEREは色々書けるが適切な書き方がある
      - スカラはできる比較対象として
      - カラムはINとかつければできる
      - 行はこれえーっと、比較対象が行だったら確かできるような、、
      - テーブルは行 IN TABLE_SUBQUERYでできる
この章の論点
- スカラー、カラムの比較演算子、相関サブクエリ

このスクラップは11日前にクローズされました

これはなに

調べるもの

MySQL公式

13.2.11 サブクエリー

13.2.11.1 スカラーオペランドとしてのサブクエリー

13.2.11.2 サブクエリーを使用した比較

13.2.11.3 ANY、IN、または SOME を使用したサブクエリー

13.2.11.4 ALL を使用したサブクエリー

13.2.11.5 行サブクエリー

13.2.11.6 EXISTS または NOT EXISTS を使用したサブクエリー

13.2.11.7 相関サブクエリー

13.2.11.8 導出テーブル

13.2.11.9 ラテラル導出テーブル

13.2.11.10 サブクエリーのエラー

13.2.11.11 サブクエリーの最適化

8.2.2 サブクエリー、導出テーブル、ビュー参照および共通テーブル式の最適化

13.2.11.12 サブクエリーの制約

感想