助け合いフォーラム
![<img src="/mondai3/img/jpg/48861.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19316/48861.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T031129Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAgaDmFwLW5vcnRoZWFzdC0xIkgwRgIhANHJuOowIP70c5JvzZjwVv5qS91hqIWdQFZzYJWrf%2BvoAiEAvoVVFMmtRzvvs5kU35dlS89gfzMOt4V6dNlhGoNwtKIqsQQI4v%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDH%2FzNF8qx%2BnHRiOCJSqFBAQWQnV37gSoyaGHp1Jz5xS766UNOpZxuBfnriUqNRkTRQvse4S%2BUx5wWq3Cq1N85EgDWHpTUdk0z0onEOaGztMin1WP6QWXrlX8SmK5wxvk8von2NK6jFMcwAQfaEH7twN%2BjyrnhKNxNFO5vt8vs0H%2FJXTjGK6ePeLRthQ4%2FxqLEtmuQ%2BYoUojAqQBWCEjP5KpAAje2xJqxViGg9iqYP0STI7gWVAYeEmv1nzs4AxvJNdf1fYRBilxZ2PIgn1mvR%2FK7dxbz2smHtg2HhzsZjaJ5H8WHqRLQINQrr714HX9u4k1RvZzzuCtmNDvgC93ASD8mKQlAuaAA1SGfG60tXKUayewaXZFPlGcR%2B6A1j7d5INESj%2BLzK0Jp2dP10GooOXm8UnTCLj2nkFFxMh9VIE64CY1avR%2F0kV72Po7FoTcVcuzoH2IbOrSgIyT8N4Ij8F5fOqcqbDdyhmNRfLmd0N7ZP25qoU6K3rIMeMWKgPzaeT54s2i%2Bnhszui3m0Sy9he9RwbFlf%2FMmaHob3lafQzjThxSkzqFbWm1mogY3tfKExSH1iRtyX1Y%2BAim4wtO1ZizqEDqMkW8SAzD0xxIneYuT3GPr66KWSCEHKHW6t20NRYGyXTEr%2FwELefBQKzfDPLRwL4OPSeHHkEUiypb763zevR2RhLks%2B4EKNse0Py4eASWiTbwwuP%2BQtQY6pQHuYZCSXCZiYnz1Mbqq4CBIMnYufh%2FzzOnVAmaVI9V%2FHpM72SD2qm%2Bk6YSrekCCOcmaa5EwsV9AQDxwXatoy96nbNYBSbjYNXQIq5ygpCHI03EDC%2FTaH%2B9sanKP4qnQopFS5G9SAJl1OdRKmgycdC3TW8IYhWC1%2BFhsTJeNsF824TVcPR4VGJbPfwr75zlJD9Jhh6YhJhq1UMNJaqEm5AFczAffBV0%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFRAGVRWFZ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=a82d588c160cefee6b764920ffd59bbd7fa575e7eaa0d0c412a09241c179ba68)
従業員名とその上司の氏名を表示するには、どの問い合わせを実行しますか(該当するものを全て選択して下さい)。
正解
SELECT emp.employee_name, mgr.employee_name FROM employees emp, employees mgr WHERE emp.manager_id = mgr.employee_id;
SELECT emp.employee_name, mgr.employee_name FROM employees emp JOIN employees mgr ON emp.manager_id = mgr.employee_id;
解説
しかし上司の氏名を取り出すには、EMPLOYEES表のMANAGER_ID列の値でEMPLOYEES表を検索し、EMPLOYEE_NAME列の値を取り出す必要があります。
このようなデータの取り出しを行うには、従業員の氏名を持つEMPLOYEES表と、MANAGER_IDを持つEMPLOYEES表があると見立てて、2つの表を結合します(自己結合といいます)。
自己結合はON句もしくはOracle独自の結合構文で行えます。
以上より、
・SELECT emp.employee_name, mgr.employee_name FROM employees emp, employees mgr WHERE emp.manager_id = mgr.employee_id;
・SELECT emp.employee_name, mgr.employee_name FROM employees emp JOIN employees mgr ON emp.manager_id = mgr.employee_id;
が正解となります。
正解のSQL文の実行結果は次のようになります。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19592/k49002.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T031129Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAgaDmFwLW5vcnRoZWFzdC0xIkgwRgIhANHJuOowIP70c5JvzZjwVv5qS91hqIWdQFZzYJWrf%2BvoAiEAvoVVFMmtRzvvs5kU35dlS89gfzMOt4V6dNlhGoNwtKIqsQQI4v%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDH%2FzNF8qx%2BnHRiOCJSqFBAQWQnV37gSoyaGHp1Jz5xS766UNOpZxuBfnriUqNRkTRQvse4S%2BUx5wWq3Cq1N85EgDWHpTUdk0z0onEOaGztMin1WP6QWXrlX8SmK5wxvk8von2NK6jFMcwAQfaEH7twN%2BjyrnhKNxNFO5vt8vs0H%2FJXTjGK6ePeLRthQ4%2FxqLEtmuQ%2BYoUojAqQBWCEjP5KpAAje2xJqxViGg9iqYP0STI7gWVAYeEmv1nzs4AxvJNdf1fYRBilxZ2PIgn1mvR%2FK7dxbz2smHtg2HhzsZjaJ5H8WHqRLQINQrr714HX9u4k1RvZzzuCtmNDvgC93ASD8mKQlAuaAA1SGfG60tXKUayewaXZFPlGcR%2B6A1j7d5INESj%2BLzK0Jp2dP10GooOXm8UnTCLj2nkFFxMh9VIE64CY1avR%2F0kV72Po7FoTcVcuzoH2IbOrSgIyT8N4Ij8F5fOqcqbDdyhmNRfLmd0N7ZP25qoU6K3rIMeMWKgPzaeT54s2i%2Bnhszui3m0Sy9he9RwbFlf%2FMmaHob3lafQzjThxSkzqFbWm1mogY3tfKExSH1iRtyX1Y%2BAim4wtO1ZizqEDqMkW8SAzD0xxIneYuT3GPr66KWSCEHKHW6t20NRYGyXTEr%2FwELefBQKzfDPLRwL4OPSeHHkEUiypb763zevR2RhLks%2B4EKNse0Py4eASWiTbwwuP%2BQtQY6pQHuYZCSXCZiYnz1Mbqq4CBIMnYufh%2FzzOnVAmaVI9V%2FHpM72SD2qm%2Bk6YSrekCCOcmaa5EwsV9AQDxwXatoy96nbNYBSbjYNXQIq5ygpCHI03EDC%2FTaH%2B9sanKP4qnQopFS5G9SAJl1OdRKmgycdC3TW8IYhWC1%2BFhsTJeNsF824TVcPR4VGJbPfwr75zlJD9Jhh6YhJhq1UMNJaqEm5AFczAffBV0%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFRAGVRWFZ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=2cdfafcdbf34577bd25312fcbc2ce38f9d05356a1f948652ed6eeaa14ea7f9f1)
FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id;
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19593/kk49002.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T031129Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAgaDmFwLW5vcnRoZWFzdC0xIkgwRgIhANHJuOowIP70c5JvzZjwVv5qS91hqIWdQFZzYJWrf%2BvoAiEAvoVVFMmtRzvvs5kU35dlS89gfzMOt4V6dNlhGoNwtKIqsQQI4v%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDH%2FzNF8qx%2BnHRiOCJSqFBAQWQnV37gSoyaGHp1Jz5xS766UNOpZxuBfnriUqNRkTRQvse4S%2BUx5wWq3Cq1N85EgDWHpTUdk0z0onEOaGztMin1WP6QWXrlX8SmK5wxvk8von2NK6jFMcwAQfaEH7twN%2BjyrnhKNxNFO5vt8vs0H%2FJXTjGK6ePeLRthQ4%2FxqLEtmuQ%2BYoUojAqQBWCEjP5KpAAje2xJqxViGg9iqYP0STI7gWVAYeEmv1nzs4AxvJNdf1fYRBilxZ2PIgn1mvR%2FK7dxbz2smHtg2HhzsZjaJ5H8WHqRLQINQrr714HX9u4k1RvZzzuCtmNDvgC93ASD8mKQlAuaAA1SGfG60tXKUayewaXZFPlGcR%2B6A1j7d5INESj%2BLzK0Jp2dP10GooOXm8UnTCLj2nkFFxMh9VIE64CY1avR%2F0kV72Po7FoTcVcuzoH2IbOrSgIyT8N4Ij8F5fOqcqbDdyhmNRfLmd0N7ZP25qoU6K3rIMeMWKgPzaeT54s2i%2Bnhszui3m0Sy9he9RwbFlf%2FMmaHob3lafQzjThxSkzqFbWm1mogY3tfKExSH1iRtyX1Y%2BAim4wtO1ZizqEDqMkW8SAzD0xxIneYuT3GPr66KWSCEHKHW6t20NRYGyXTEr%2FwELefBQKzfDPLRwL4OPSeHHkEUiypb763zevR2RhLks%2B4EKNse0Py4eASWiTbwwuP%2BQtQY6pQHuYZCSXCZiYnz1Mbqq4CBIMnYufh%2FzzOnVAmaVI9V%2FHpM72SD2qm%2Bk6YSrekCCOcmaa5EwsV9AQDxwXatoy96nbNYBSbjYNXQIq5ygpCHI03EDC%2FTaH%2B9sanKP4qnQopFS5G9SAJl1OdRKmgycdC3TW8IYhWC1%2BFhsTJeNsF824TVcPR4VGJbPfwr75zlJD9Jhh6YhJhq1UMNJaqEm5AFczAffBV0%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFRAGVRWFZ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=af99b4d4e3fbe1d529f3ffcadb731877e36f84bceaf2210cd51598cbe807712e)
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;
その他の選択肢については次のとおりです。
・SELECT emp.employee_name, mgr.employee_name FROM employees emp NATURAL JOIN employees mgr;
結合列に表接頭辞を使用しているため、エラーとなります。
![【図を表示3】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19594/kkk49002.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T031129Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAgaDmFwLW5vcnRoZWFzdC0xIkgwRgIhANHJuOowIP70c5JvzZjwVv5qS91hqIWdQFZzYJWrf%2BvoAiEAvoVVFMmtRzvvs5kU35dlS89gfzMOt4V6dNlhGoNwtKIqsQQI4v%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDH%2FzNF8qx%2BnHRiOCJSqFBAQWQnV37gSoyaGHp1Jz5xS766UNOpZxuBfnriUqNRkTRQvse4S%2BUx5wWq3Cq1N85EgDWHpTUdk0z0onEOaGztMin1WP6QWXrlX8SmK5wxvk8von2NK6jFMcwAQfaEH7twN%2BjyrnhKNxNFO5vt8vs0H%2FJXTjGK6ePeLRthQ4%2FxqLEtmuQ%2BYoUojAqQBWCEjP5KpAAje2xJqxViGg9iqYP0STI7gWVAYeEmv1nzs4AxvJNdf1fYRBilxZ2PIgn1mvR%2FK7dxbz2smHtg2HhzsZjaJ5H8WHqRLQINQrr714HX9u4k1RvZzzuCtmNDvgC93ASD8mKQlAuaAA1SGfG60tXKUayewaXZFPlGcR%2B6A1j7d5INESj%2BLzK0Jp2dP10GooOXm8UnTCLj2nkFFxMh9VIE64CY1avR%2F0kV72Po7FoTcVcuzoH2IbOrSgIyT8N4Ij8F5fOqcqbDdyhmNRfLmd0N7ZP25qoU6K3rIMeMWKgPzaeT54s2i%2Bnhszui3m0Sy9he9RwbFlf%2FMmaHob3lafQzjThxSkzqFbWm1mogY3tfKExSH1iRtyX1Y%2BAim4wtO1ZizqEDqMkW8SAzD0xxIneYuT3GPr66KWSCEHKHW6t20NRYGyXTEr%2FwELefBQKzfDPLRwL4OPSeHHkEUiypb763zevR2RhLks%2B4EKNse0Py4eASWiTbwwuP%2BQtQY6pQHuYZCSXCZiYnz1Mbqq4CBIMnYufh%2FzzOnVAmaVI9V%2FHpM72SD2qm%2Bk6YSrekCCOcmaa5EwsV9AQDxwXatoy96nbNYBSbjYNXQIq5ygpCHI03EDC%2FTaH%2B9sanKP4qnQopFS5G9SAJl1OdRKmgycdC3TW8IYhWC1%2BFhsTJeNsF824TVcPR4VGJbPfwr75zlJD9Jhh6YhJhq1UMNJaqEm5AFczAffBV0%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFRAGVRWFZ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=c6e6720d5e7a1cee75a6826e947772f821f8c5cceb110a98fac8195a2569c3bb)
・SELECT emp.employee_name, mgr.employee_name FROM employees emp JOIN employees mgr USING (employee_id);
従業員名とその上司の氏名を表示するには、EMPLOYEE_ID列とMANAGER_ID列で結合しなければなりません。
![【図を表示4】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19595/kkkk49002.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T031129Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAgaDmFwLW5vcnRoZWFzdC0xIkgwRgIhANHJuOowIP70c5JvzZjwVv5qS91hqIWdQFZzYJWrf%2BvoAiEAvoVVFMmtRzvvs5kU35dlS89gfzMOt4V6dNlhGoNwtKIqsQQI4v%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDH%2FzNF8qx%2BnHRiOCJSqFBAQWQnV37gSoyaGHp1Jz5xS766UNOpZxuBfnriUqNRkTRQvse4S%2BUx5wWq3Cq1N85EgDWHpTUdk0z0onEOaGztMin1WP6QWXrlX8SmK5wxvk8von2NK6jFMcwAQfaEH7twN%2BjyrnhKNxNFO5vt8vs0H%2FJXTjGK6ePeLRthQ4%2FxqLEtmuQ%2BYoUojAqQBWCEjP5KpAAje2xJqxViGg9iqYP0STI7gWVAYeEmv1nzs4AxvJNdf1fYRBilxZ2PIgn1mvR%2FK7dxbz2smHtg2HhzsZjaJ5H8WHqRLQINQrr714HX9u4k1RvZzzuCtmNDvgC93ASD8mKQlAuaAA1SGfG60tXKUayewaXZFPlGcR%2B6A1j7d5INESj%2BLzK0Jp2dP10GooOXm8UnTCLj2nkFFxMh9VIE64CY1avR%2F0kV72Po7FoTcVcuzoH2IbOrSgIyT8N4Ij8F5fOqcqbDdyhmNRfLmd0N7ZP25qoU6K3rIMeMWKgPzaeT54s2i%2Bnhszui3m0Sy9he9RwbFlf%2FMmaHob3lafQzjThxSkzqFbWm1mogY3tfKExSH1iRtyX1Y%2BAim4wtO1ZizqEDqMkW8SAzD0xxIneYuT3GPr66KWSCEHKHW6t20NRYGyXTEr%2FwELefBQKzfDPLRwL4OPSeHHkEUiypb763zevR2RhLks%2B4EKNse0Py4eASWiTbwwuP%2BQtQY6pQHuYZCSXCZiYnz1Mbqq4CBIMnYufh%2FzzOnVAmaVI9V%2FHpM72SD2qm%2Bk6YSrekCCOcmaa5EwsV9AQDxwXatoy96nbNYBSbjYNXQIq5ygpCHI03EDC%2FTaH%2B9sanKP4qnQopFS5G9SAJl1OdRKmgycdC3TW8IYhWC1%2BFhsTJeNsF824TVcPR4VGJbPfwr75zlJD9Jhh6YhJhq1UMNJaqEm5AFczAffBV0%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFRAGVRWFZ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=ebeb68e552aba1f65bec94656e3fe4d93d742f74ad06a32c5b44b0d924191bed)
・SELECT employee_name, mgr.employee_name FROM employees JOIN employees mgr ON manager_id = mgr.employee_id;
自己結合を行う場合は、表に対して必ず表別名を指定します。
![【図を表示5】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19596/kkkkk49002.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T031129Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAgaDmFwLW5vcnRoZWFzdC0xIkgwRgIhANHJuOowIP70c5JvzZjwVv5qS91hqIWdQFZzYJWrf%2BvoAiEAvoVVFMmtRzvvs5kU35dlS89gfzMOt4V6dNlhGoNwtKIqsQQI4v%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDH%2FzNF8qx%2BnHRiOCJSqFBAQWQnV37gSoyaGHp1Jz5xS766UNOpZxuBfnriUqNRkTRQvse4S%2BUx5wWq3Cq1N85EgDWHpTUdk0z0onEOaGztMin1WP6QWXrlX8SmK5wxvk8von2NK6jFMcwAQfaEH7twN%2BjyrnhKNxNFO5vt8vs0H%2FJXTjGK6ePeLRthQ4%2FxqLEtmuQ%2BYoUojAqQBWCEjP5KpAAje2xJqxViGg9iqYP0STI7gWVAYeEmv1nzs4AxvJNdf1fYRBilxZ2PIgn1mvR%2FK7dxbz2smHtg2HhzsZjaJ5H8WHqRLQINQrr714HX9u4k1RvZzzuCtmNDvgC93ASD8mKQlAuaAA1SGfG60tXKUayewaXZFPlGcR%2B6A1j7d5INESj%2BLzK0Jp2dP10GooOXm8UnTCLj2nkFFxMh9VIE64CY1avR%2F0kV72Po7FoTcVcuzoH2IbOrSgIyT8N4Ij8F5fOqcqbDdyhmNRfLmd0N7ZP25qoU6K3rIMeMWKgPzaeT54s2i%2Bnhszui3m0Sy9he9RwbFlf%2FMmaHob3lafQzjThxSkzqFbWm1mogY3tfKExSH1iRtyX1Y%2BAim4wtO1ZizqEDqMkW8SAzD0xxIneYuT3GPr66KWSCEHKHW6t20NRYGyXTEr%2FwELefBQKzfDPLRwL4OPSeHHkEUiypb763zevR2RhLks%2B4EKNse0Py4eASWiTbwwuP%2BQtQY6pQHuYZCSXCZiYnz1Mbqq4CBIMnYufh%2FzzOnVAmaVI9V%2FHpM72SD2qm%2Bk6YSrekCCOcmaa5EwsV9AQDxwXatoy96nbNYBSbjYNXQIq5ygpCHI03EDC%2FTaH%2B9sanKP4qnQopFS5G9SAJl1OdRKmgycdC3TW8IYhWC1%2BFhsTJeNsF824TVcPR4VGJbPfwr75zlJD9Jhh6YhJhq1UMNJaqEm5AFczAffBV0%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFRAGVRWFZ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=9d7b3cfd947dd214ad54d31af1ab7d4c7a305224e7380d17c5a43ea073b2647a)
参考
自己結合を行うには、次のように記述します。
SELECT 表別名.列名 [,表別名.列名 ...]
FROM 表名1 表別名1 JOIN 表名1 表別名2
ON 表別名1.列名 = 表別名2.列名 ;
自己結合を行う場合は、表に対して必ず表別名を指定します。
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19593/kk49002.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T031129Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAgaDmFwLW5vcnRoZWFzdC0xIkgwRgIhANHJuOowIP70c5JvzZjwVv5qS91hqIWdQFZzYJWrf%2BvoAiEAvoVVFMmtRzvvs5kU35dlS89gfzMOt4V6dNlhGoNwtKIqsQQI4v%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDH%2FzNF8qx%2BnHRiOCJSqFBAQWQnV37gSoyaGHp1Jz5xS766UNOpZxuBfnriUqNRkTRQvse4S%2BUx5wWq3Cq1N85EgDWHpTUdk0z0onEOaGztMin1WP6QWXrlX8SmK5wxvk8von2NK6jFMcwAQfaEH7twN%2BjyrnhKNxNFO5vt8vs0H%2FJXTjGK6ePeLRthQ4%2FxqLEtmuQ%2BYoUojAqQBWCEjP5KpAAje2xJqxViGg9iqYP0STI7gWVAYeEmv1nzs4AxvJNdf1fYRBilxZ2PIgn1mvR%2FK7dxbz2smHtg2HhzsZjaJ5H8WHqRLQINQrr714HX9u4k1RvZzzuCtmNDvgC93ASD8mKQlAuaAA1SGfG60tXKUayewaXZFPlGcR%2B6A1j7d5INESj%2BLzK0Jp2dP10GooOXm8UnTCLj2nkFFxMh9VIE64CY1avR%2F0kV72Po7FoTcVcuzoH2IbOrSgIyT8N4Ij8F5fOqcqbDdyhmNRfLmd0N7ZP25qoU6K3rIMeMWKgPzaeT54s2i%2Bnhszui3m0Sy9he9RwbFlf%2FMmaHob3lafQzjThxSkzqFbWm1mogY3tfKExSH1iRtyX1Y%2BAim4wtO1ZizqEDqMkW8SAzD0xxIneYuT3GPr66KWSCEHKHW6t20NRYGyXTEr%2FwELefBQKzfDPLRwL4OPSeHHkEUiypb763zevR2RhLks%2B4EKNse0Py4eASWiTbwwuP%2BQtQY6pQHuYZCSXCZiYnz1Mbqq4CBIMnYufh%2FzzOnVAmaVI9V%2FHpM72SD2qm%2Bk6YSrekCCOcmaa5EwsV9AQDxwXatoy96nbNYBSbjYNXQIq5ygpCHI03EDC%2FTaH%2B9sanKP4qnQopFS5G9SAJl1OdRKmgycdC3TW8IYhWC1%2BFhsTJeNsF824TVcPR4VGJbPfwr75zlJD9Jhh6YhJhq1UMNJaqEm5AFczAffBV0%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFRAGVRWFZ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=af99b4d4e3fbe1d529f3ffcadb731877e36f84bceaf2210cd51598cbe807712e)
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;
また、Oracle独自の結合構文で自己結合を行うには、次のように記述します。
SELECT 表別名.列名 [,表別名.列名 ...]
FROM 表名1 表別名1, 表名1 表別名2
WHERE 表別名1.列名 = 表別名2.列名 ;
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19592/k49002.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T031129Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAgaDmFwLW5vcnRoZWFzdC0xIkgwRgIhANHJuOowIP70c5JvzZjwVv5qS91hqIWdQFZzYJWrf%2BvoAiEAvoVVFMmtRzvvs5kU35dlS89gfzMOt4V6dNlhGoNwtKIqsQQI4v%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDH%2FzNF8qx%2BnHRiOCJSqFBAQWQnV37gSoyaGHp1Jz5xS766UNOpZxuBfnriUqNRkTRQvse4S%2BUx5wWq3Cq1N85EgDWHpTUdk0z0onEOaGztMin1WP6QWXrlX8SmK5wxvk8von2NK6jFMcwAQfaEH7twN%2BjyrnhKNxNFO5vt8vs0H%2FJXTjGK6ePeLRthQ4%2FxqLEtmuQ%2BYoUojAqQBWCEjP5KpAAje2xJqxViGg9iqYP0STI7gWVAYeEmv1nzs4AxvJNdf1fYRBilxZ2PIgn1mvR%2FK7dxbz2smHtg2HhzsZjaJ5H8WHqRLQINQrr714HX9u4k1RvZzzuCtmNDvgC93ASD8mKQlAuaAA1SGfG60tXKUayewaXZFPlGcR%2B6A1j7d5INESj%2BLzK0Jp2dP10GooOXm8UnTCLj2nkFFxMh9VIE64CY1avR%2F0kV72Po7FoTcVcuzoH2IbOrSgIyT8N4Ij8F5fOqcqbDdyhmNRfLmd0N7ZP25qoU6K3rIMeMWKgPzaeT54s2i%2Bnhszui3m0Sy9he9RwbFlf%2FMmaHob3lafQzjThxSkzqFbWm1mogY3tfKExSH1iRtyX1Y%2BAim4wtO1ZizqEDqMkW8SAzD0xxIneYuT3GPr66KWSCEHKHW6t20NRYGyXTEr%2FwELefBQKzfDPLRwL4OPSeHHkEUiypb763zevR2RhLks%2B4EKNse0Py4eASWiTbwwuP%2BQtQY6pQHuYZCSXCZiYnz1Mbqq4CBIMnYufh%2FzzOnVAmaVI9V%2FHpM72SD2qm%2Bk6YSrekCCOcmaa5EwsV9AQDxwXatoy96nbNYBSbjYNXQIq5ygpCHI03EDC%2FTaH%2B9sanKP4qnQopFS5G9SAJl1OdRKmgycdC3TW8IYhWC1%2BFhsTJeNsF824TVcPR4VGJbPfwr75zlJD9Jhh6YhJhq1UMNJaqEm5AFczAffBV0%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFRAGVRWFZ%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=2cdfafcdbf34577bd25312fcbc2ce38f9d05356a1f948652ed6eeaa14ea7f9f1)
FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id;
Employeesテーブルのデータについて
配布されている検証用データのEmployees表のデータを確認したところ、manager_idの列のデータがnullの行とそうでない行がありました。
これは、上司の人はmanager_idを持たず、上司のいる従業員のmanager_id列に上司のemployee_idが入るという認識で合ってますでしょうか。
また、本問題は表の構造だけ見て解けるものなのでしょうか
表データも見ないとわからない気がするのですが…
これは、上司の人はmanager_idを持たず、上司のいる従業員のmanager_id列に上司のemployee_idが入るという認識で合ってますでしょうか。
そうですね。その理解で良いと思います。
前提として「manager_id列に値が入っているレコードは、上司がいることを意味する」と判断しておく必要はありますが、実際の表データは見なくても問題は解けるかなと思います。
以下の2つの選択肢は構文エラーになるので除外。
・SELECT emp.employee_name, mgr.employee_name FROM employees emp NATURAL JOIN employees mgr;
・SELECT employee_name, mgr.employee_name FROM employees JOIN employees mgr ON manager_id = mgr.employee_id;
以下の選択肢は、同じ名前が表示されるだけなので、期待する結果(従業員名とその上司の氏名)にはならないと判断できる。
・SELECT emp.employee_name, mgr.employee_name FROM employees emp JOIN employees mgr USING (employee_id);
そして残りの2つについては、manager_id列に値を持つemployee_nameと、そのmanger_idに該当するemployee_nameが表示されるので、本設問で期待されている「従業員名とその上司の氏名」(と思われるもの)が表示されるので正答である、と判断はできないことはないかなと思いました。
・SELECT emp.employee_name, mgr.employee_name FROM employees emp, employees mgr WHERE emp.manager_id = mgr.employee_id;
・SELECT emp.employee_name, mgr.employee_name FROM employees emp JOIN employees mgr ON emp.manager_id = mgr.employee_id;
もし、運営さんに修正してもらうとしたら、「manager_id列に値が入ってるレコードは、その従業員に上司がいることを意味する」のような補足を問題文に追記してもらうぐらいでしょうか。
実際のSilver SQL 2019の試験問題で、どのような出題がされるかはなんとも言えないところですが...。
コメント
この投稿に対して返信しませんか?
k kyomukyomu
2024/02/13 17:16
ご回答いただきありがとうございます。 確かに消去法で解けないことはないですね。 ご親切にありがとうございました。