助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26622
問題を開く
PRODUCTS表とSALES表の構造を確認して下さい。
![<img src="/mondai3/img/jpg/48885.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19950/48885.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T024158Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=cbb4934e27545f6dbf156501df1d8288c5d8d2f56ef2d894101df725c370820c)
![<img src="/mondai3/img/jpg/49054.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19878/49054.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T024158Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=48603d26142c0b2771784368fa4188745661c45c083e528c108ad8884c0a1f41)
以下の問合せと同じ結果になるSQL文はどれですか。
SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales;
![<img src="/mondai3/img/jpg/48885.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19950/48885.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T024158Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=cbb4934e27545f6dbf156501df1d8288c5d8d2f56ef2d894101df725c370820c)
![<img src="/mondai3/img/jpg/49054.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19878/49054.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T024158Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=48603d26142c0b2771784368fa4188745661c45c083e528c108ad8884c0a1f41)
以下の問合せと同じ結果になるSQL文はどれですか。
SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales;
正解
SELECT DISTINCT prod_id FROM products p
JOIN sales s USING(prod_id);
解説
INTERSECT演算子を用いた複合問合せでは、2つの問合せの結果の共通する行を表示します。また、重複した行は排除されます。
設問のSQL文の実行結果は次のようになります。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19951/k49289.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T024158Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=242cc620b425de14009cba72fa32df230d5d318cd6369adfe37aeb9d94ac4b73)
選択肢を1つずつ確認してみましょう。
・SELECT prod_id FROM products
UNION
SELECT prod_id FROM sales;
UNION演算子を用いた複合問合せでは、2つの問合せの結果を連結し、重複行を排除して表示します。誤ったSQL文です。
・SELECT prod_id FROM products
MINUS
SELECT prod_id FROM sales;
MINUS演算子を用いた複合問合せでは、1つ目の問合せ結果から2つ目の問合せ結果にない行を表示します。誤ったSQL文です。
・SELECT p.prod_id FROM products p
JOIN sales s ON p.prod_id = s.prod_id;
ON句を使用した等価結合です。結合条件p.prod_id = s.prod_idに合致する行は重複行も含め全て表示されます。誤ったSQL文です。
・SELECT distinct prod_id FROM products p
JOIN sales s USING(prod_id);
USING句を使用した等価結合です。ON句と同様に両表のprod_idの共通した行は全て取り出されますが、SELECT句の後にDISTINCTキーワードがあるので、重複した行を排除して表示します。正しいSQL文です。
設問のSQL文と同じ実行結果になります。
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19952/kk49289.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T024158Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=57e5f3f231739b626f681128b15f0e8ec6885e71ede8e7efc61dc202443d0ac7)
設問のSQL文の実行結果は次のようになります。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19951/k49289.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T024158Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=242cc620b425de14009cba72fa32df230d5d318cd6369adfe37aeb9d94ac4b73)
SQLを表示
SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales;
INTERSECT
SELECT prod_id FROM sales;
選択肢を1つずつ確認してみましょう。
・SELECT prod_id FROM products
UNION
SELECT prod_id FROM sales;
UNION演算子を用いた複合問合せでは、2つの問合せの結果を連結し、重複行を排除して表示します。誤ったSQL文です。
・SELECT prod_id FROM products
MINUS
SELECT prod_id FROM sales;
MINUS演算子を用いた複合問合せでは、1つ目の問合せ結果から2つ目の問合せ結果にない行を表示します。誤ったSQL文です。
・SELECT p.prod_id FROM products p
JOIN sales s ON p.prod_id = s.prod_id;
ON句を使用した等価結合です。結合条件p.prod_id = s.prod_idに合致する行は重複行も含め全て表示されます。誤ったSQL文です。
・SELECT distinct prod_id FROM products p
JOIN sales s USING(prod_id);
USING句を使用した等価結合です。ON句と同様に両表のprod_idの共通した行は全て取り出されますが、SELECT句の後にDISTINCTキーワードがあるので、重複した行を排除して表示します。正しいSQL文です。
設問のSQL文と同じ実行結果になります。
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19952/kk49289.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T024158Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=57e5f3f231739b626f681128b15f0e8ec6885e71ede8e7efc61dc202443d0ac7)
SQLを表示
SELECT DISTINCT prod_id FROM products p
JOIN sales s USING(prod_id);
JOIN sales s USING(prod_id);
INTERSECTの解釈について
公開日 2023/07/19
質問失礼いたします。
当該問題につきまして、両表のPROD_IDにPRIMARY KEYやUNIQUEの制約が設定されていないのですが、
仮にこの両表にPROD_IDが1001のデータが複数存在する場合でも
1001は1つしか戻らないのでしょうか?
例を挙げると
Products
PROD_ID PROD_NAME
1001 Alpha
1001 Bravo
1001 Charlie
1002 Delta
Sales
PROD_ID CUST_ID
1001 2001
1001 2002
1001 2003
1002 2004
上記の場合でも、1001は一つしか戻されない(=DISTINCT prod_id の状態になる)のでしょうか?
実用面などから基本はPROD_IDに主キーやUNIQUEが入っている前提だと思うのですが特にその記述も無いことと、
仮にこの状況が発生している場合にどのような結果が戻るかが知りたかったのですが、
検索してみた限りで見つからず質問させていただいた次第です。
よろしければご教示いただければ幸いです。
r
rink_rew
2023/07/24 18:07
例として挙げておられるデータで実際に試してみました。
INTERSECTは積をとり、重複行は含まれないのでこのような結果となるようです。
SQL> select * from test_prod;
PROD_ID PROD_NAME
---------- -----------
1001 Alpha
1001 Blavo
1001 Charlie
1002 Delta
SQL> select * from test_sales;
PROD_ID CUST_ID
---------- ----------
1001 2001
1001 2002
1001 2003
1002 2004
SQL> select prod_id from test_prod intersect select prod_id from test_sales;
PROD_ID
----------
1001
1002
SQL> select distinct prod_id from test_prod p join test_sales s using(prod_id);
PROD_ID
----------
1001
1002
SQL>
コメント
この投稿に対して返信しませんか?
k konomiyuduki
2023/07/28 09:32
検証いただきありがとうございます。 この問題でPRIMARY KEYが設定されていないのが気になっていましたが、 設定せずとも正解の状態になるのですね。ありがとうございました。