トップ MySQLに関する質問 UNION する場合だけ、厳密な GROUP BY が要求される理由

編集履歴

質問編集履歴

行番号を汎用的に変更

2022/04/29 09:51

投稿

nikuatsu

スコア177

test CHANGED Viewed

	@@ -1 +1 @@
1	- UNIONする場合だけ、厳密なGROUP BYが要求される理由
1	+ UNION する場合だけ、厳密な GROUP BY が要求される理由

test CHANGED Viewed

@@ -9,6 +9,7 @@
 なぜ➂の場合だけ、SELECTするカラムすべてにGROUP BYを付けなければエラーになるのか？理由を知りたいです。
 ### CREATE、INSERT
+対象レコードです。
 ```SQL
 -- コメント
 CREATE TABLE my_comments(
@@ -48,18 +49,18 @@
 ```
 ### ➀：「コメントの本文」をSELECT
-まずこちらは、3行目から10行目の５つのカラムに対し、22行目で「IDだけでまとめる」よう指定していますが、エラーなく取得できます。
+まずこちらは、`# ➀-1`から`# ➀-2`行目の５つのカラムに対し、`# ➀-3`行目で「IDだけでまとめる」よう指定していますが、エラーは起こりません。
 ```SQL
 		-- ➀：「コメントの本文」をSELECT
 		SELECT
-			 comments.ID AS comment_id      -- 3行目
+			 comments.ID AS comment_id      # ➀-1
 			,comments.lang_id
 			,comments.comment
 			,comments.count_likes
 			,GROUP_CONCAT(
 				tags.ID
 				SEPARATOR '__SEPARATOR__'
-			) AS tag_ids                    -- 10行目
+			) AS tag_ids                    # ➀-2
 		FROM
 			my_comments comments
@@ -71,24 +72,24 @@
 		-- '指導'を持つコメントを指定
 		WHERE MATCH (comments.comment) AGAINST ('指導' IN BOOLEAN MODE)
-		GROUP BY comments.ID                -- 22行目
+		GROUP BY comments.ID                # ➀-3
 		ORDER BY comments.count_likes DESC, comments.ID DESC
 		LIMIT 0, 20
 ```
 ### ②：「コメントについたタグ」をSELECT
-またこちらは、3行目から10行目の５つのカラムに対し、28行目で「IDだけでまとめる」よう指定していますが、エラーなく取得できます。
+またこちらは、`# ➁-1`から`# ➁-2`行目の５つのカラムに対し、`# ➁-3`行目で「IDだけでまとめる」よう指定していますが、エラーは起こりません。
 ```SQL
 		-- ➁：「コメントについたタグ」をSELECT
 		SELECT
-			 comments.ID AS comment_id      -- 3行目
+			 comments.ID AS comment_id      # ➁-1
 			,comments.lang_id
 			,comments.comment
 			,comments.count_likes
 			,GROUP_CONCAT(
 				tags.ID
 				SEPARATOR '__SEPARATOR__'
-			) AS tag_ids                    -- 10行目
+			) AS tag_ids                    # ➁-2
 		FROM
 			my_comments comments
@@ -106,16 +107,21 @@
 		-- '指導'を持つタグを指定
 		WHERE tags2.tag_name LIKE '%指導%'
-		GROUP BY comments.ID                -- 28行目
+		GROUP BY comments.ID                # ➁-3
 		ORDER BY comments.count_likes DESC, comments.ID DESC
 		LIMIT 0, 20
 ```
 ### ➂： ➀と➁をUNIONしてSELECT
-疑問なのがこちらで、➀と➁と同じ５つのカラムに対し、同じように66行目で「comment_idだけでまとめる」よう指定しているつもりなのですが、この場合にエラーになります。
+疑問なのがこちらで、➀と➁と同じように`# ➂-1`から`# ➂-2`行目の５つのカラムに対し、同じように`# ➂-3`行目で「comment_idだけでまとめる」よう指定しているつもりなのですが、この場合にエラーになります。
 ```SQL
 -- ➂： ➀と➁をUNIONしてSELECT
-SELECT *
+SELECT
+	 x.comment_id                          # ➂-1
+	,x.lang_id
+	,x.comment
+	,x.count_likes
+	,x.tag_ids                             # ➂-2
 FROM (
 	(
@@ -127,9 +133,9 @@
 		-- ➁：「コメントについたタグ」をSELECT
 		-- 上と同じなので割愛
 	)
-)x
+) AS x
-GROUP BY x.comment_id                       -- 66行目
+GROUP BY x.comment_id                       # ➂-3
 ORDER BY x.count_likes DESC, x.comment_id DESC
 LIMIT 0, 20
 ```

MySQL phpMyAdmin

環境にサーバーを追加

2022/04/29 09:38

投稿

nikuatsu

スコア177

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -161,7 +161,8 @@
 言い換えるなら、③でSELECTするカラムすべてにGROUP BYを付けなければエラーになるのならば、➀➁だって`GROUP BY comments.ID`だけではエラーになるハズではないか？という疑問になります。
-### ツールのバージョン
+### 環境
+サーバー：ConoHa WING
-phpMyAdmin：`5.1.3-2.el7.remi`
+phpMyAdmin：5.1.3-2.el7.remi
-MySQL：`5.7.27-log`
+MySQL：5.7.27-log

MySQL phpMyAdmin

誤字などの修正

2022/04/28 10:16

投稿

nikuatsu

スコア177

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -135,7 +135,7 @@
 ```
 ### エラーメッセージ
-③でUNIONする場合のエラーがこちらです。（改行は質問者が追加）
+③の場合のエラーがこちらです。（改行は質問者が追加）
 ```SQL
 #1055 - Expression #2 of SELECT list is not in GROUP BY clause
@@ -155,7 +155,7 @@
 ```
 さらに、`SELECT @@global.sql_mode;`で確認される`ONLY_FULL_GROUP_BY`を解除すると、`GROUP BY x.comment_id`のままでもエラー解消できました。
-これらのことから、➀➁と③のエラーはGROUP BYに由来するものだと考えられます。
+これらのことから、➀➁で平気で、③がエラーになるその違いはGROUP BYに由来するものだと考えられます。
 でも私は➀➁③どれもが、『同じカラムに対して同じようにGROUP BYしている認識』なのですが、UNIONした場合は何が異なるのでしょうか？

MySQL phpMyAdmin

「：」を追加

2022/04/28 09:56

投稿

nikuatsu

スコア177

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -1,9 +1,9 @@
 ### 前提
 ➀と➁をSELECTする場合はIDだけにGROUP BYを付ければいいのですが、➂でUNIONする場合はSELECTするカラムすべてにGROUP BYを付けなければエラーになります。
-➀「コメントの本文」をSELECT
+➀：「コメントの本文」をSELECT
-➁「コメントについたタグ」をSELECT
+➁：「コメントについたタグ」をSELECT
-➂ ➀と②をUNIONしてSELECT
+➂： ➀と②をUNIONしてSELECT
 ### 知りたいこと
 なぜ➂の場合だけ、SELECTするカラムすべてにGROUP BYを付けなければエラーになるのか？理由を知りたいです。
@@ -47,10 +47,10 @@
     (3, 3);
 ```
-### ➀「コメントの本文」をSELECT
+### ➀：「コメントの本文」をSELECT
 まずこちらは、3行目から10行目の５つのカラムに対し、22行目で「IDだけでまとめる」よう指定していますが、エラーなく取得できます。
 ```SQL
-		-- ➀「コメントの本文」をSELECT
+		-- ➀：「コメントの本文」をSELECT
 		SELECT
 			 comments.ID AS comment_id      -- 3行目
 			,comments.lang_id
@@ -76,10 +76,10 @@
 		LIMIT 0, 20
 ```
-### ②「コメントについたタグ」をSELECT
+### ②：「コメントについたタグ」をSELECT
 またこちらは、3行目から10行目の５つのカラムに対し、28行目で「IDだけでまとめる」よう指定していますが、エラーなく取得できます。
 ```SQL
-		-- ➁「コメントについたタグ」をSELECT
+		-- ➁：「コメントについたタグ」をSELECT
 		SELECT
 			 comments.ID AS comment_id      -- 3行目
 			,comments.lang_id
@@ -111,20 +111,20 @@
 		LIMIT 0, 20
 ```
-### ➂ ➀と➁をUNIONしてSELECT
+### ➂： ➀と➁をUNIONしてSELECT
 疑問なのがこちらで、➀と➁と同じ５つのカラムに対し、同じように66行目で「comment_idだけでまとめる」よう指定しているつもりなのですが、この場合にエラーになります。
 ```SQL
--- ➂ ➀と➁をUNIONしてSELECT
+-- ➂： ➀と➁をUNIONしてSELECT
 SELECT *
 FROM (
 	(
-		-- ➀「コメントの本文」をSELECT
+		-- ➀：「コメントの本文」をSELECT
 		-- 上と同じなので割愛
 	) UNION (
-		-- ➁「コメントについたタグ」をSELECT
+		-- ➁：「コメントについたタグ」をSELECT
 		-- 上と同じなので割愛
 	)
 )x

MySQL phpMyAdmin

疑問を加筆

2022/04/28 09:46

投稿

nikuatsu

スコア177

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -1,12 +1,12 @@
 ### 前提
-➀と➁をSELECTする場合はIDだけにGROUP BYを付ければいいのですが、➂でUNIONする場合はSELECTするカラムすべてにGROUP BYを付けなければいけません。
+➀と➁をSELECTする場合はIDだけにGROUP BYを付ければいいのですが、➂でUNIONする場合はSELECTするカラムすべてにGROUP BYを付けなければエラーになります。
 ➀「コメントの本文」をSELECT
 ➁「コメントについたタグ」をSELECT
 ➂ ➀と②をUNIONしてSELECT
 ### 知りたいこと
-なぜ➂の場合だけ、SELECTするカラムすべてにGROUP BYを付けなければいけないのか？理由を知りたいです。
+なぜ➂の場合だけ、SELECTするカラムすべてにGROUP BYを付けなければエラーになるのか？理由を知りたいです。
 ### CREATE、INSERT
 ```SQL
@@ -159,6 +159,8 @@
 でも私は➀➁③どれもが、『同じカラムに対して同じようにGROUP BYしている認識』なのですが、UNIONした場合は何が異なるのでしょうか？
+言い換えるなら、③でSELECTするカラムすべてにGROUP BYを付けなければエラーになるのならば、➀➁だって`GROUP BY comments.ID`だけではエラーになるハズではないか？という疑問になります。
 ### ツールのバージョン
 phpMyAdmin：`5.1.3-2.el7.remi`
 MySQL：`5.7.27-log`

MySQL phpMyAdmin

コードの同じ部分を割愛

2022/04/28 09:38

投稿

nikuatsu

スコア177

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -120,62 +120,12 @@
 	(
 		-- ➀「コメントの本文」をSELECT
-		SELECT
-			 comments.ID AS comment_id
-			,comments.lang_id
-			,comments.comment
-			,comments.count_likes
-			,GROUP_CONCAT(
-				tags.ID
-				SEPARATOR '__SEPARATOR__'
-			) AS tag_ids
+		-- 上と同じなので割愛
-		FROM
-			my_comments comments
-			LEFT JOIN my_tag_holders th
-					ON th.comments_ID = comments.ID
-			LEFT JOIN my_tags tags
-					ON tags.ID = th.tags_ID
-		-- '指導'を持つコメントを指定
-		WHERE MATCH (comments.comment) AGAINST ('指導' IN BOOLEAN MODE)
-		GROUP BY comments.ID                -- 26行目
-		ORDER BY comments.count_likes DESC, comments.ID DESC
-		LIMIT 0, 20
 	) UNION (
 		-- ➁「コメントについたタグ」をSELECT
-		SELECT
-			 comments.ID AS comment_id
-			,comments.lang_id
-			,comments.comment
-			,comments.count_likes
-			,GROUP_CONCAT(
-				tags.ID
-				SEPARATOR '__SEPARATOR__'
-			) AS tag_ids
-		FROM
-			my_comments comments
-			LEFT JOIN my_tag_holders th
-					ON th.comments_ID = comments.ID
-			LEFT JOIN my_tags tags
-					ON tags.ID = th.tags_ID
-			-- タグ名を指定するためのJOIN
+		-- 上と同じなので割愛
-			LEFT JOIN my_tag_holders th2
-					ON th2.comments_ID = comments.ID
-			LEFT JOIN my_tags tags2
-					ON tags2.ID = th2.tags_ID
-		-- '指導'を持つタグを指定
-		WHERE tags2.tag_name LIKE '%指導%'
-		GROUP BY comments.ID
-		ORDER BY comments.count_likes DESC, comments.ID DESC
-		LIMIT 0, 20
 	)
 )x

MySQL phpMyAdmin

➁を修正

2022/04/28 09:35

投稿

nikuatsu

スコア177

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -111,10 +111,10 @@
 		LIMIT 0, 20
 ```
-### ➂ これらをUNIONしてSELECT
+### ➂ ➀と➁をUNIONしてSELECT
-疑問なのがこちらで、➀と②と同じ５つのカラムに対し、同じように66行目で「comment_idだけでまとめる」よう指定しているつもりなのですが、この場合にエラーになります。
+疑問なのがこちらで、➀と➁と同じ５つのカラムに対し、同じように66行目で「comment_idだけでまとめる」よう指定しているつもりなのですが、この場合にエラーになります。
-```SQL
+```SQL
--- ➂ ➀と②をUNIONしてSELECT
+-- ➂ ➀と➁をUNIONしてSELECT
 SELECT *
 FROM (
 	(

MySQL phpMyAdmin