回答編集履歴
3
要望に応えて動作の詳細を追記
test
CHANGED
|
File without changes
|
2
要望に応えて動作の詳細を追記
test
CHANGED
|
@@ -113,3 +113,381 @@
|
|
|
113
113
|
|
|
114
114
|
|
|
115
115
|
[https://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E4%BB%A3%E6%95%B0_(%E9%96%A2%E4%BF%82%E3%83%A2%E3%83%87%E3%83%AB)](https://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E4%BB%A3%E6%95%B0_(%E9%96%A2%E4%BF%82%E3%83%A2%E3%83%87%E3%83%AB))
|
|
116
|
+
|
|
117
|
+
|
|
118
|
+
|
|
119
|
+
### 相関副問合せをもつ exists / not exists （ここから、2018-03-02)
|
|
120
|
+
|
|
121
|
+
くどいかもしれませんが、動作を知りたいという要望に応えます。このあと、まとめとして、関係演算の積、商の解説、さらに、すべてを検索するのになぜexistsを使用するかを解説する予定ですが、そちらで、これについて別の質問をたてたほうがよいかも。(疲れたので休憩します）
|
|
122
|
+
|
|
123
|
+
|
|
124
|
+
|
|
125
|
+
**相関副問合せにおける exists/not exist の動作**
|
|
126
|
+
|
|
127
|
+
|
|
128
|
+
|
|
129
|
+
exists/not existの動作を if 構文に直して考えます。外側の検索で読まれたレコード1件ずつを判定の対象とします。
|
|
130
|
+
|
|
131
|
+
|
|
132
|
+
|
|
133
|
+
```SQL
|
|
134
|
+
|
|
135
|
+
<外側の検索で読まれたレコード> -- <-- 1件が判定の対象
|
|
136
|
+
|
|
137
|
+
if exists|not exists(<外側の検索で読まれたレコード>の項目の値を使うselectの結果)
|
|
138
|
+
|
|
139
|
+
<外側の検索で読まれたレコード>を採用する -- <-- 上の exists/ not existsがtrueの場合
|
|
140
|
+
|
|
141
|
+
else
|
|
142
|
+
|
|
143
|
+
<外側の検索で読まれたレコード>を採用しない
|
|
144
|
+
|
|
145
|
+
```
|
|
146
|
+
|
|
147
|
+
|
|
148
|
+
|
|
149
|
+
### SQL動作確認：（関係演算の除算）ホテルとタグの組がすべて存在するホテルを検索
|
|
150
|
+
|
|
151
|
+
|
|
152
|
+
|
|
153
|
+
explain を使ってSQLを調べます。(JSON 形式の出力を行う exlain format=json <SQL> ... もあります。こちらは詳細が表示されます)
|
|
154
|
+
|
|
155
|
+
|
|
156
|
+
|
|
157
|
+
```SQL
|
|
158
|
+
|
|
159
|
+
> explain select hotel_id from hotels a where not exists(
|
|
160
|
+
|
|
161
|
+
-> select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
|
|
162
|
+
|
|
163
|
+
-> select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
|
|
164
|
+
|
|
165
|
+
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+--------------------------+
|
|
166
|
+
|
|
167
|
+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
|
168
|
+
|
|
169
|
+
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+--------------------------+
|
|
170
|
+
|
|
171
|
+
| 1 | PRIMARY | a | index | NULL | hotel_id | 6 | NULL | 12 | Using where; Using index |
|
|
172
|
+
|
|
173
|
+
| 2 | DEPENDENT SUBQUERY | c | range | tag | tag | 302 | NULL | 2 | Using where; Using index |
|
|
174
|
+
|
|
175
|
+
| 3 | DEPENDENT SUBQUERY | b | index_subquery | hotel_id | hotel_id | 4 | func | 8 | Using index; Using where |
|
|
176
|
+
|
|
177
|
+
+------+--------------------+-------+----------------+---------------+----------+---------+------+------+--------------------------+
|
|
178
|
+
|
|
179
|
+
3 rows in set (0.001 sec)
|
|
180
|
+
|
|
181
|
+
```
|
|
182
|
+
|
|
183
|
+
|
|
184
|
+
|
|
185
|
+
explainからわかるのは select の入れ子です。select_type の意味は次のとおり。
|
|
186
|
+
|
|
187
|
+
- PRIMARY 主検索
|
|
188
|
+
|
|
189
|
+
- DEPENDENCY SUBQUERY 外側の検索結果を使用する検索（外側の検索が先に実行される）
|
|
190
|
+
|
|
191
|
+
|
|
192
|
+
|
|
193
|
+
上の検索 id.1,id.2,id.3 はすべて「選択」であり「結合」はありません。依存関係は、id.1 <- id.2 <- id.3。以下の疑似コードで動作を示します。
|
|
194
|
+
|
|
195
|
+
|
|
196
|
+
|
|
197
|
+
``` pseudo sql
|
|
198
|
+
|
|
199
|
+
hotels a を全件検索 <-- id.1
|
|
200
|
+
|
|
201
|
+
hotelsのレコードごとに以下を実行
|
|
202
|
+
|
|
203
|
+
tags c のレコード'朝食付き','キャンセル無料'を検索する。 <-- id.2
|
|
204
|
+
|
|
205
|
+
各レコード(2,'朝食付き'),(3,'キャンセル無料')のそれぞれに以下を実行する
|
|
206
|
+
|
|
207
|
+
hotels_tags b のレコードを検索する。(<ホテルid>,<タグid=2>)と(<ホテルid>,<タグid=3>)の2回 <-- id.3
|
|
208
|
+
|
|
209
|
+
```
|
|
210
|
+
|
|
211
|
+
|
|
212
|
+
|
|
213
|
+
ここまでで、SQLの動作の概要を理解してください。
|
|
214
|
+
|
|
215
|
+
|
|
216
|
+
|
|
217
|
+
### SQL: ホテルごとの動作の確認
|
|
218
|
+
|
|
219
|
+
|
|
220
|
+
|
|
221
|
+
ホテルと検索対象のタグの存在有無の組み合わせを調べます。検索で選択されるホテルは、すべてのタグを持つ「クラウン リージェンシー ホテル & タワーズ」だけです。これらの3つのホテルを確認用データとして使います。
|
|
222
|
+
|
|
223
|
+
|
|
224
|
+
|
|
225
|
+
|hotel_id|ホテル|「朝食付き」(tags_id:2)|「キャンセル無料」(tags_id:3)|
|
|
226
|
+
|
|
227
|
+
|:--:|:--|:--|:--|
|
|
228
|
+
|
|
229
|
+
|1|クラウン リージェンシー ホテル & タワーズ|○|○|
|
|
230
|
+
|
|
231
|
+
|5|レッド プラネット セブ|ー|○|
|
|
232
|
+
|
|
233
|
+
|8|パーム グラス ホテル|ー|ー|
|
|
234
|
+
|
|
235
|
+
|
|
236
|
+
|
|
237
|
+
まず、うえのSQLの検索条件に hotel_id を追加、1ホテルごとの検索の和に変形します。
|
|
238
|
+
|
|
239
|
+
|
|
240
|
+
|
|
241
|
+
```SQL
|
|
242
|
+
|
|
243
|
+
select hotel_id from hotels a where hotel_id = 1 and not exists( ...
|
|
244
|
+
|
|
245
|
+
union
|
|
246
|
+
|
|
247
|
+
...
|
|
248
|
+
|
|
249
|
+
union
|
|
250
|
+
|
|
251
|
+
select hotel_id from hotels a where hotel_id = 5 and not exists( ...
|
|
252
|
+
|
|
253
|
+
union
|
|
254
|
+
|
|
255
|
+
...
|
|
256
|
+
|
|
257
|
+
union
|
|
258
|
+
|
|
259
|
+
select hotel_id from hotels a where hotel_id = 8 and not exists( ...
|
|
260
|
+
|
|
261
|
+
union
|
|
262
|
+
|
|
263
|
+
...
|
|
264
|
+
|
|
265
|
+
```
|
|
266
|
+
|
|
267
|
+
|
|
268
|
+
|
|
269
|
+
**確認してください-1**
|
|
270
|
+
|
|
271
|
+
上の個別SQLの hotel_id を、1, 5, 8 のそれぞれで実行して動作を確認してください。
|
|
272
|
+
|
|
273
|
+
|
|
274
|
+
|
|
275
|
+
### SQL(id.2-1): 必要なタグを検索
|
|
276
|
+
|
|
277
|
+
タグの検索は全てのホテルで同じです。次の副問合せ(id.3)で使用する tags_id、2, 3 を取得します。すべてのホテルで使用するタグの定数レコードが二件。
|
|
278
|
+
|
|
279
|
+
|
|
280
|
+
|
|
281
|
+
```SQL
|
|
282
|
+
|
|
283
|
+
select c.tags_id from tags c where c.tag in ('朝食付き','キャンセル無料')
|
|
284
|
+
|
|
285
|
+
```
|
|
286
|
+
|
|
287
|
+
|
|
288
|
+
|
|
289
|
+
### SQL(id.3): ホテル、タグの組の検索
|
|
290
|
+
|
|
291
|
+
SQL(id.2-1)の各定数( tags_id: 2, 3 )を使って以下の問い合わせを実行します。not existsが二回実行されます。
|
|
292
|
+
|
|
293
|
+
|
|
294
|
+
|
|
295
|
+
```SQL
|
|
296
|
+
|
|
297
|
+
not exists(select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id)
|
|
298
|
+
|
|
299
|
+
```
|
|
300
|
+
|
|
301
|
+
|
|
302
|
+
|
|
303
|
+
**確認してください-2**
|
|
304
|
+
|
|
305
|
+
hotel_idが、1、 5、 8、tags_idが、2、3 の組み合わせで以下の動作を確認してください。MySQLの結果は、trueが 1、falseが 0 になります。
|
|
306
|
+
|
|
307
|
+
組み合わせは 6 とおりです。指定する(ホテル、タグ)の組が存在すれば false、存在しなければ trueになります。
|
|
308
|
+
|
|
309
|
+
|
|
310
|
+
|
|
311
|
+
```SQL
|
|
312
|
+
|
|
313
|
+
select not exists(select 1 from hotels_tags b where <タグid(2,3のいずれか)> = b.tags_id and b.hotel_id = <ホテルのid(1,5,8のいずれか)>);
|
|
314
|
+
|
|
315
|
+
```
|
|
316
|
+
|
|
317
|
+
|
|
318
|
+
|
|
319
|
+
### SQL(id.2-2): ホテル、タグ検索(id.3)の結果をタグ検索にまとめる
|
|
320
|
+
|
|
321
|
+
|
|
322
|
+
|
|
323
|
+
各ホテルでtags_id 2,3 の検索を実行したので、UNIONを使って結果をまとめます。select 1 の部分は、わかりやすくするためつぎのように置き換えておきます。
|
|
324
|
+
|
|
325
|
+
- select '朝食付きなし' from ...
|
|
326
|
+
|
|
327
|
+
- select 'キャンセル無料なし' from ...
|
|
328
|
+
|
|
329
|
+
|
|
330
|
+
|
|
331
|
+
```SQL
|
|
332
|
+
|
|
333
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = <ホテルのid>)
|
|
334
|
+
|
|
335
|
+
union
|
|
336
|
+
|
|
337
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = <ホテルのid>);
|
|
338
|
+
|
|
339
|
+
```
|
|
340
|
+
|
|
341
|
+
|
|
342
|
+
|
|
343
|
+
**確認してください-3**
|
|
344
|
+
|
|
345
|
+
hotel_idが、1、5、8 のそれぞれで、上のまとめ検索SQL(id.2-2)を実行してください。確認するのは次のことです。
|
|
346
|
+
|
|
347
|
+
- (ホテル、タグ)が存在しなければ、まとめ検索でレコードが存在する。
|
|
348
|
+
|
|
349
|
+
- (ホテル、タグ)が2つ揃っているなら、まとめ検索のレコードは存在しない。
|
|
350
|
+
|
|
351
|
+
|
|
352
|
+
|
|
353
|
+
### SQL(id.1-2): ホテル、タグ検索の結果をタグ検索にまとめたものSQL(id.2-2)に not exists を適用する。
|
|
354
|
+
|
|
355
|
+
|
|
356
|
+
|
|
357
|
+
最後のホテルの判定処理は、ホテル、タグ検索の結果をタグ検索にまとめたものを使って、ホテルごとになされます。
|
|
358
|
+
|
|
359
|
+
|
|
360
|
+
|
|
361
|
+
```SQL
|
|
362
|
+
|
|
363
|
+
select hotel_id from hotels a where a.hotel_id = <ホテルのid> and not exists(
|
|
364
|
+
|
|
365
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = <ホテルのid>)
|
|
366
|
+
|
|
367
|
+
union
|
|
368
|
+
|
|
369
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = <ホテルのid>)
|
|
370
|
+
|
|
371
|
+
);
|
|
372
|
+
|
|
373
|
+
```
|
|
374
|
+
|
|
375
|
+
|
|
376
|
+
|
|
377
|
+
**確認してください-4**
|
|
378
|
+
|
|
379
|
+
hotel_idが、1、5、8 のそれぞれで、上のまとめ検索を実行してください。確認するのは次のことです。
|
|
380
|
+
|
|
381
|
+
- (ホテル、タグ)がひとつでも存在しなければ、ホテルが選択されない。
|
|
382
|
+
|
|
383
|
+
- (ホテル、タグ)が2つ揃っているときに限りホテルが選択される。
|
|
384
|
+
|
|
385
|
+
|
|
386
|
+
|
|
387
|
+
以上が動作の詳細です。プログラマーはこの動作を無意識のうちに（直観的に）考えながらSQLを組み立てています。
|
|
388
|
+
|
|
389
|
+
|
|
390
|
+
|
|
391
|
+
### 確認してください sql 一覧
|
|
392
|
+
|
|
393
|
+
確認してください-1
|
|
394
|
+
|
|
395
|
+
```SQL
|
|
396
|
+
|
|
397
|
+
select hotel_id from hotels a where hotel_id = 1 and not exists(
|
|
398
|
+
|
|
399
|
+
select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
|
|
400
|
+
|
|
401
|
+
select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
|
|
402
|
+
|
|
403
|
+
select hotel_id from hotels a where hotel_id = 5 and not exists(
|
|
404
|
+
|
|
405
|
+
select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
|
|
406
|
+
|
|
407
|
+
select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
|
|
408
|
+
|
|
409
|
+
select hotel_id from hotels a where hotel_id = 8 and not exists(
|
|
410
|
+
|
|
411
|
+
select 1 from tags c where c.tag in ('朝食付き','キャンセル無料') and not exists(
|
|
412
|
+
|
|
413
|
+
select 1 from hotels_tags b where c.tags_id = b.tags_id and b.hotel_id = a.hotel_id));
|
|
414
|
+
|
|
415
|
+
```
|
|
416
|
+
|
|
417
|
+
確認してください-2
|
|
418
|
+
|
|
419
|
+
```SQL
|
|
420
|
+
|
|
421
|
+
select not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 1);
|
|
422
|
+
|
|
423
|
+
select not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 1);
|
|
424
|
+
|
|
425
|
+
select not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 5);
|
|
426
|
+
|
|
427
|
+
select not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 5);
|
|
428
|
+
|
|
429
|
+
select not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 8);
|
|
430
|
+
|
|
431
|
+
select not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 8);
|
|
432
|
+
|
|
433
|
+
```
|
|
434
|
+
|
|
435
|
+
確認してください-3
|
|
436
|
+
|
|
437
|
+
```SQL
|
|
438
|
+
|
|
439
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 1)
|
|
440
|
+
|
|
441
|
+
union
|
|
442
|
+
|
|
443
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 1);
|
|
444
|
+
|
|
445
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 5)
|
|
446
|
+
|
|
447
|
+
union
|
|
448
|
+
|
|
449
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 5);
|
|
450
|
+
|
|
451
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 8)
|
|
452
|
+
|
|
453
|
+
union
|
|
454
|
+
|
|
455
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 8);
|
|
456
|
+
|
|
457
|
+
```
|
|
458
|
+
|
|
459
|
+
確認してください-4
|
|
460
|
+
|
|
461
|
+
```SQL
|
|
462
|
+
|
|
463
|
+
select hotel_id from hotels a where a.hotel_id = 1 and not exists(
|
|
464
|
+
|
|
465
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 1)
|
|
466
|
+
|
|
467
|
+
union
|
|
468
|
+
|
|
469
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 1)
|
|
470
|
+
|
|
471
|
+
);
|
|
472
|
+
|
|
473
|
+
select hotel_id from hotels a where a.hotel_id = 5 and not exists(
|
|
474
|
+
|
|
475
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 5)
|
|
476
|
+
|
|
477
|
+
union
|
|
478
|
+
|
|
479
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 5)
|
|
480
|
+
|
|
481
|
+
);
|
|
482
|
+
|
|
483
|
+
select hotel_id from hotels a where a.hotel_id = 8 and not exists(
|
|
484
|
+
|
|
485
|
+
select '朝食付きなし' from tags c where not exists(select 1 from hotels_tags b where 2 = b.tags_id and b.hotel_id = 8)
|
|
486
|
+
|
|
487
|
+
union
|
|
488
|
+
|
|
489
|
+
select 'キャンセル無料なし' from tags c where not exists(select 1 from hotels_tags b where 3 = b.tags_id and b.hotel_id = 8)
|
|
490
|
+
|
|
491
|
+
);
|
|
492
|
+
|
|
493
|
+
```
|
1
補集合の補集合を削除、二重否定に変更する。
test
CHANGED
|
@@ -108,7 +108,7 @@
|
|
|
108
108
|
|
|
109
109
|
### 説明資料
|
|
110
110
|
|
|
111
|
-
SQL全体でやりたいのは、Coddの関係演算（代数）の「商」です。まず資料を読んで考えてください。「商」は「積」の反対演算です。hotels_tags ÷ tgasをやりたいのです。not existsを2回使う意味は、補集合の補集合をとるためですが、今はわからなくてよいです。
|
|
111
|
+
SQL全体でやりたいのは、Coddの関係演算（代数）の「商」です。まず資料を読んで考えてください。「商」は「積」の反対演算です。hotels_tags ÷ tgasをやりたいのです。not existsを2回使う意味は、~~補集合の補集合~~二重否定をとるためですが、今はわからなくてよいです。
|
|
112
112
|
|
|
113
113
|
|
|
114
114
|
|