助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26866
問題を開く
EMPLOYEES表の構造を確認して下さい。
![<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=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=249fb6d225a749211affc767206ccdafaff70afec0578ad27eabe2017a8e2508)
給与の額が所属する部署の最低給与を超える従業員の一覧と、部署ごとの最低給与を表示します。
以下の問合せを実行しましたが、実行結果について正しい記述はどれですか。
SELECT e.employee_name, e.salary, e.department_id, s.minsal
FROM employees e, (SELECT department_id, MIN(salary) minsal FROM employees GROUP BY department_id) s
WHERE e.salary > s.minsal
ORDER BY e.department_id, e.salary;
![<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=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=249fb6d225a749211affc767206ccdafaff70afec0578ad27eabe2017a8e2508)
給与の額が所属する部署の最低給与を超える従業員の一覧と、部署ごとの最低給与を表示します。
以下の問合せを実行しましたが、実行結果について正しい記述はどれですか。
SELECT e.employee_name, e.salary, e.department_id, s.minsal
FROM employees e, (SELECT department_id, MIN(salary) minsal FROM employees GROUP BY department_id) s
WHERE e.salary > s.minsal
ORDER BY e.department_id, e.salary;
正解
実行されるが正しい結果が返されない
解説
設問のSQL文は、Oracle独自の結合構文です。
Oracle独自の結合構文では、
・結合する表名は,(カンマ)で区切ってFROM句に指定
・結合条件はWHERE句に指定
・結合条件以外の条件はWHERE句に指定した結合条件の後にAND演算子で指定
します。
設問のSQL文ではEMPLOYEES表と、部署ごとの最低給与を問合せる( )で囲まれた副問合せをFROM句に指定し、両者を結合しています。
しかし、WHERE句には「e.salary > s.minsal」という検索条件しか指定されておらず、結合条件が欠けています。この場合エラーとはなりませんが、結合した全てのデータの組み合わせが返ってくるので正しい結果は得られません。
以上より、
・実行されるが正しい結果が返されない
が正解となります。
設問のSQL文の実行結果は次のようになります。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20340/k49277.jpg?X-Amz-Expires=600&X-Amz-Date=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=c6ae3f85d1a4f4bdf5a00cf7f0861bee43bfd8afd4742e25687413210b06482b)
正しく実行するには、WHERE句に結合条件を指定します。
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20341/kk49277.jpg?X-Amz-Expires=600&X-Amz-Date=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=3616c71af2a189ad6a37d471310d917167e5c1e6f40f70ea0b00d483fcee0abd)
Oracle独自の結合構文では、
・結合する表名は,(カンマ)で区切ってFROM句に指定
・結合条件はWHERE句に指定
・結合条件以外の条件はWHERE句に指定した結合条件の後にAND演算子で指定
します。
設問のSQL文ではEMPLOYEES表と、部署ごとの最低給与を問合せる( )で囲まれた副問合せをFROM句に指定し、両者を結合しています。
しかし、WHERE句には「e.salary > s.minsal」という検索条件しか指定されておらず、結合条件が欠けています。この場合エラーとはなりませんが、結合した全てのデータの組み合わせが返ってくるので正しい結果は得られません。
以上より、
・実行されるが正しい結果が返されない
が正解となります。
設問のSQL文の実行結果は次のようになります。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20340/k49277.jpg?X-Amz-Expires=600&X-Amz-Date=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=c6ae3f85d1a4f4bdf5a00cf7f0861bee43bfd8afd4742e25687413210b06482b)
SQLを表示
SELECT e.employee_name, e.salary, e.department_id, s.minsal
FROM employees e, (SELECT department_id, MIN(salary) minsal FROM employees GROUP BY department_id) s
WHERE e.salary > s.minsal
ORDER BY e.department_id, e.salary;
FROM employees e, (SELECT department_id, MIN(salary) minsal FROM employees GROUP BY department_id) s
WHERE e.salary > s.minsal
ORDER BY e.department_id, e.salary;
正しく実行するには、WHERE句に結合条件を指定します。
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20341/kk49277.jpg?X-Amz-Expires=600&X-Amz-Date=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=3616c71af2a189ad6a37d471310d917167e5c1e6f40f70ea0b00d483fcee0abd)
SQLを表示
SELECT e.employee_name, e.salary, e.department_id, s.minsal
FROM employees e, (SELECT department_id, MIN(salary) minsal FROM employees GROUP BY department_id) s
WHERE e.department_id = s.department_id
AND e.salary > s.minsal
ORDER BY e.department_id, e.salary;
FROM employees e, (SELECT department_id, MIN(salary) minsal FROM employees GROUP BY department_id) s
WHERE e.department_id = s.department_id
AND e.salary > s.minsal
ORDER BY e.department_id, e.salary;
参考
Oracle Databaseには、Oracle独自の結合構文があります。
構文は以下の通りです。
SELECT [表接頭辞.]列名 [, [表接頭辞.]列名 ...]
FROM 表名1, 表名2
WHERE 結合条件
[AND 結合条件以外の条件];
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19571/k48997.jpg?X-Amz-Expires=600&X-Amz-Date=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=aa02f6a57f28aa2c12ad170aa18841c29bea14110f1a676ca20c55a1cd53de50)
Oracle独自の結合構文では、
・結合する表名は,(カンマ)で区切ってFROM句に指定
・結合条件はWHERE句に指定
・結合条件以外の条件はWHERE句に指定した結合条件の後にAND演算子で指定
します。
ON句を使用した結合同様に、結合する2つの表にある同じ名前の列をSELECT句やWHERE句に指定する場合は、必ず表接頭辞を使用して列を指定しなければなりません。
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19572/kk48997.jpg?X-Amz-Expires=600&X-Amz-Date=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=352477b39ff4a3a7d72f7771ae19988bacbfb6e87c5a7788b78b50bb597d9352)
なお、以下はOracle Databaseで使用できる表の結合方法(ANSI SQL:1999準拠の結合構文)で、〇がついているものはOracle独自の結合構文でも記述できます。
![【図を表示3】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19573/kkk48997.jpg?X-Amz-Expires=600&X-Amz-Date=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=9f315e7f754a5f2fa0bdd94cae48d33cb360c1f7e413f1f0c94ff76b41d94c12)
SQL:1999準拠の結合構文とOracle独自の結合構文とではパフォーマンスに違いはありません。
構文は以下の通りです。
SELECT [表接頭辞.]列名 [, [表接頭辞.]列名 ...]
FROM 表名1, 表名2
WHERE 結合条件
[AND 結合条件以外の条件];
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19571/k48997.jpg?X-Amz-Expires=600&X-Amz-Date=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=aa02f6a57f28aa2c12ad170aa18841c29bea14110f1a676ca20c55a1cd53de50)
SQLを表示
SELECT d.department_id, d.department_name, e.employee_id,e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND salary BETWEEN 200000 AND 450000;
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND salary BETWEEN 200000 AND 450000;
Oracle独自の結合構文では、
・結合する表名は,(カンマ)で区切ってFROM句に指定
・結合条件はWHERE句に指定
・結合条件以外の条件はWHERE句に指定した結合条件の後にAND演算子で指定
します。
ON句を使用した結合同様に、結合する2つの表にある同じ名前の列をSELECT句やWHERE句に指定する場合は、必ず表接頭辞を使用して列を指定しなければなりません。
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19572/kk48997.jpg?X-Amz-Expires=600&X-Amz-Date=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=352477b39ff4a3a7d72f7771ae19988bacbfb6e87c5a7788b78b50bb597d9352)
SQLを表示
SELECT department_id, d.department_name, e.employee_id,e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND salary BETWEEN 200000 AND 450000;
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND salary BETWEEN 200000 AND 450000;
なお、以下はOracle Databaseで使用できる表の結合方法(ANSI SQL:1999準拠の結合構文)で、〇がついているものはOracle独自の結合構文でも記述できます。
![【図を表示3】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19573/kkk48997.jpg?X-Amz-Expires=600&X-Amz-Date=20240726T234323Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAYaDmFwLW5vcnRoZWFzdC0xIkcwRQIgZGbfF1GoEDO%2FYrfCsXwfFSjCKj1RvjjY73XrK7%2BQmNoCIQDQepXbON3IQIjPhJHvDWmfx7QZLZ38Eufauz3VD8MrMyqxBAjf%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F8BEAAaDDYyMzQyOTgyNzI3NSIMN422Dczg4LODIH7UKoUEAQgot%2FkaKm0ycrY0esL1EN%2Fd8yPeY1B0QFsipqtIABA1SdaGl3RwYaxW09JrRsZCI64Zk4KkOBPvzL%2FMkx8c8c8gmEvs0u4A40Cua4V5YIxMhs8PuNvZQW2ICoSi9GVcILgRjhGXvjQxZtHC2hGEG5f0016Y2ZGR8AhwBhRMD4vLmOTIngn2xqwRp6O9A%2FSuGoAf8pcTscE4NyRQ6hrfv2mhzSuq3BwTtE4LtZcJK%2B0cj2nQu5chUJtJ8B3jbX04Mil%2BVIJoutTSsGgy%2FPzC9EsaDmydOSESyOI8zsIeObqNo2%2BtCfgBsBlPy8sIFPIaWk7e2q9j9Jn18c2rndsn8Nt955J%2F%2F91JpdvbuTXWa3dSTnvM18t9OHyR6NBJrkbZOggFlXR4BqCZbwf2oUllVG9uulGiaItDV7QmMr2mvJ16seiRZ1jbzPIDLvAqFrOZsoh9ibS3i%2Fcco6Xv%2Fwj%2F5Q2%2FnJmQeoj2f5njnGgr%2Bkh91qXkq2FCdvp9kM2fRXKw7LXzNA5zjZdw7jEjmYYEKO3kfRt%2BmqAUAGHbjWD6ZORaeOZ6SyeE%2BciAA%2F8uRtuTVaP6rYsYw9IXoT3lGJ1QiwWHNbGlyNop%2F76tqMn90JcgNqKDnM%2FsAlWGFVi%2FEg28vzgaqnTthI%2Fz9PwO9v4KQLIQFo4vbTBEKC1K2tWHh%2FZT80vnXjDyvZC1BjqmAQac2E5lk6HQlBuIP81mejR4mAHvW19rMqK8fPWiS5T8snoCbT35rOShhx1mBdzRzs%2FPfy6Pa1X%2B67U%2BYNoGExGt4QVAmzs4QUnkOre3y20krCO%2BzqIr%2BdZZltTxih7YSet0NWSGAdu4pqWJvyjJfaMU6Aqiq0XQOa7bCEgdRby39OQNS6VWw3ah5PRFigfyolbI7tQ2FIOnXTRc1vTBrgvbZvF9yAY%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLFV3Q5B6V3%2F20240726%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=9f315e7f754a5f2fa0bdd94cae48d33cb360c1f7e413f1f0c94ff76b41d94c12)
SQL:1999準拠の結合構文とOracle独自の結合構文とではパフォーマンスに違いはありません。
WHERE句で列別名を使用
m
mmmss125
公開日 2023/01/02
列別名を使用できるのはORDER BY句のみという認識なのですが、なぜエラーにならないのでしょうか?
例外などありましたでしょうか。調べてもわからなかったのでどなたか教えてください。
q
quensan
2023/01/04 13:38
FROM句に副問合せがあるからのようです。
問題ID:26896の解説に以下のように書いてありました。
SQL文は以下の順序で評価されるため、通常は列別名だけを指定できるのはORDER BY句のみです(SELECT句で指定した列別名を認識できるのはORDER BY句のみのため)。
(評価順) FROM句→WHERE句→GROUP BY句→HAVING句→SELECT句→ORDER BY句
ですが、設問のSQL文ではFROM句の副問合せ(インライン・ビュー)で算術式に列別名を指定しているため、FROM句の後に評価される全ての句で列別名が認識されます。
コメント
この投稿に対して返信しませんか?
m mmmss125
2023/01/08 16:59
そんな決まりがあったのですね。。 ありがとうございます。