助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26891
問題を開く
次の2つのSQL文の結果として正しいものはどれですか。
1) SELECT employee_name, NVL(salary, salary + 10000) FROM employees;
2) SELECT employee_name, NVL2(salary, salary + 10000, '') FROM employees;
1) SELECT employee_name, NVL(salary, salary + 10000) FROM employees;
2) SELECT employee_name, NVL2(salary, salary + 10000, '') FROM employees;
正解
1),2)は正常に実行できるが、異なる結果が出力される
解説
NVL(salary, salary + 10000)は、SALARY列の値がNULL値の場合は「salary + 10000」を返し(但し、NULL値の算術演算の結果もNULLです)、NULL値以外の場合はそのままSALARY列の値を返します。
NVL2(salary, salary + 10000, '')は、SALARY列の値がNULL値以外の場合は「salary + 10000」を返し、NULL値の場合は''を返します。
以上より、
・1),2)は正常に実行できるが、異なる結果が出力される
が正解となります。
1)のSQL文の実行結果は次のようになります。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20367/k49310.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=d37d5cb40a2bc1f1089c5af2053fb20325bd509f756ce4bf00c52649d2b7eb3e)
2)のSQL文の実行結果は次のようになります。
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20368/kk49310.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=1eaecd499869cc0974da256917ace87195ddca490450e4170d25ded9107b2fdb)
NVL2(salary, salary + 10000, '')は、SALARY列の値がNULL値以外の場合は「salary + 10000」を返し、NULL値の場合は''を返します。
以上より、
・1),2)は正常に実行できるが、異なる結果が出力される
が正解となります。
1)のSQL文の実行結果は次のようになります。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20367/k49310.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=d37d5cb40a2bc1f1089c5af2053fb20325bd509f756ce4bf00c52649d2b7eb3e)
SQLを表示
SELECT employee_name, NVL(salary, salary + 10000) FROM employees;
2)のSQL文の実行結果は次のようになります。
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20368/kk49310.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=1eaecd499869cc0974da256917ace87195ddca490450e4170d25ded9107b2fdb)
SQLを表示
SELECT employee_name, NVL2(salary, salary + 10000, '') FROM employees;
参考
NULL値を扱う汎用関数には次の関数があります。
[NVL関数]
NVL関数は値がNULL値であった場合に、別の値を返す関数です。
使用法は以下の通りです。
NVL(式1, 式2)
第1引数の値がNULL値の場合、第2引数の値を返します。第1引数の値がNULL値でなければ、そのまま第1引数の値を返します。
![<img src="/mondai3/img/jpg/kk48943.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19489/kk48943.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=c43662cfb58e3425ba1ef5b3c504a5a1ad7c9de710a972a89a1ef0f89321d62b)
ただし、第2引数に指定する式は、第1引数のデータ型と同じデータ型でなければなりません(暗黙的なデータ変換が行われる場合はエラーとなりません)。異なるデータ型を返す式を指定するとエラーとなります。
![<img src="/mondai3/img/jpg/k48943.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19488/k48943.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=b620709ae996a773a491aa4fe3ee77fcc98f983980297a9ed684e4c780eaa6c0)
[NVL2関数]
NVL2関数は値がNULL値か否かを調べ、NULL値以外の場合は値A、NULL値の場合は値Bを返す関数です。
使用法は以下の通りです。
NVL2(式1, 式2, 式3)
第1引数の値がNULL値以外の場合、第2引数の値を返します。
第1引数の値がNULL値の場合、第3引数の値を返します。
![<img src="/mondai3/img/jpg/kk48945.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19490/kk48945.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=50f3000675751088935a5985d4e83554c87d7d2862285caf1cf420d8b9455fe0)
ただし、第2引数と第3引数は同じデータ型でなければなりません(暗黙的なデータ変換が行われる場合はエラーとなりません)。異なるデータ型を返す式を指定するとエラーとなります。
![<img src="/mondai3/img/jpg/k48945.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19491/k48945.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=61a5eb3ebe5a416f4ffc6d5444b47142155c38f9ce3d95f3a51f9f2ba147760e)
[NULLIF関数]
NULLIF関数は2つの値を比較して、等しい場合にNULLを返す関数です。
使用法は以下の通りです。
NULLIF(式1, 式2)
第1引数と第2引数が等しい場合はNULL値を、等しくない場合は第1引数の値を返します。
![<img src="/mondai3/img/jpg/k48947.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19492/k48947.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=e1add02f87f8390c7d21e9b4dc5bb74f82530d4544f2be66641bc9848ffc0555)
なお、第1引数にはリテラルのNULL値以外の値を指定しなければなりません。
![<img src="/mondai3/img/jpg/kk48947.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19493/kk48947.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=9d09311daa715e69b72699ea79687a534bbfbf8b39c09d2b169e3006e348a7f3)
上記のように第1引数にリテラルのNULL値は指定できませんが、第1引数で指定した列の値などが結果としてNULL値となる場合は問題ありません。
第1引数が結果としてNULL値となる場合は、第2引数がいかなる値であっても、NULL値が返されます。(第2引数がNULL値であれば等しいのでNULL値、第2引数がNULL値以外であれば等しくないので第1引数のNULL値が返される為)
[COALESCE関数]
COALESCE関数は引数の値を判定し、最初に見つかったNULL値以外の値を返す関数です。
使用法は以下の通りです。
COALESCE(式1, 式2 [, 式3 ...])
引数に指定された式全てがNULL値の場合、COALESCE関数はNULL値を返します。
![<img src="/mondai3/img/jpg/k48948.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19494/k48948.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=13a5acc39640cc8245b7e3709d7388054da0a755c8bae3bac6da2a5216871984)
なお、COALESCE関数の引数は、すべて同じデータ型の値でなければなりません。異なるデータ型の値を指定するとエラーとなります(暗黙的なデータ変換は行われません)。
![<img src="/mondai3/img/jpg/kk48948.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19495/kk48948.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=cc274b38df8b529dcab57bcf6fbdafdea574a0f0ddda2086abc9d6de22265959)
[NVL関数]
NVL関数は値がNULL値であった場合に、別の値を返す関数です。
使用法は以下の通りです。
NVL(式1, 式2)
第1引数の値がNULL値の場合、第2引数の値を返します。第1引数の値がNULL値でなければ、そのまま第1引数の値を返します。
![<img src="/mondai3/img/jpg/kk48943.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19489/kk48943.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=c43662cfb58e3425ba1ef5b3c504a5a1ad7c9de710a972a89a1ef0f89321d62b)
SQLを表示
SELECT employee_id, employee_name, salary
FROM employees
WHERE employee_id IN (1017, 1018, 1019);
SELECT employee_id, employee_name, NVL(salary, 0)
FROM employees
WHERE employee_id IN (1017, 1018, 1019);
FROM employees
WHERE employee_id IN (1017, 1018, 1019);
SELECT employee_id, employee_name, NVL(salary, 0)
FROM employees
WHERE employee_id IN (1017, 1018, 1019);
ただし、第2引数に指定する式は、第1引数のデータ型と同じデータ型でなければなりません(暗黙的なデータ変換が行われる場合はエラーとなりません)。異なるデータ型を返す式を指定するとエラーとなります。
![<img src="/mondai3/img/jpg/k48943.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19488/k48943.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=b620709ae996a773a491aa4fe3ee77fcc98f983980297a9ed684e4c780eaa6c0)
SQLを表示
SELECT NVL(manager_id, 'none')
FROM employees;
FROM employees;
[NVL2関数]
NVL2関数は値がNULL値か否かを調べ、NULL値以外の場合は値A、NULL値の場合は値Bを返す関数です。
使用法は以下の通りです。
NVL2(式1, 式2, 式3)
第1引数の値がNULL値以外の場合、第2引数の値を返します。
第1引数の値がNULL値の場合、第3引数の値を返します。
![<img src="/mondai3/img/jpg/kk48945.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19490/kk48945.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=50f3000675751088935a5985d4e83554c87d7d2862285caf1cf420d8b9455fe0)
SQLを表示
SELECT salary, NVL2(salary, 1, 0)
FROM employees;
FROM employees;
ただし、第2引数と第3引数は同じデータ型でなければなりません(暗黙的なデータ変換が行われる場合はエラーとなりません)。異なるデータ型を返す式を指定するとエラーとなります。
![<img src="/mondai3/img/jpg/k48945.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19491/k48945.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=61a5eb3ebe5a416f4ffc6d5444b47142155c38f9ce3d95f3a51f9f2ba147760e)
SQLを表示
SELECT NVL2(commission, commission, 'none')
FROM employees;
FROM employees;
[NULLIF関数]
NULLIF関数は2つの値を比較して、等しい場合にNULLを返す関数です。
使用法は以下の通りです。
NULLIF(式1, 式2)
第1引数と第2引数が等しい場合はNULL値を、等しくない場合は第1引数の値を返します。
![<img src="/mondai3/img/jpg/k48947.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19492/k48947.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=e1add02f87f8390c7d21e9b4dc5bb74f82530d4544f2be66641bc9848ffc0555)
SQLを表示
SELECT employee_name, salary, commission, NULLIF(salary, commission/4)
FROM employees;
FROM employees;
なお、第1引数にはリテラルのNULL値以外の値を指定しなければなりません。
![<img src="/mondai3/img/jpg/kk48947.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19493/kk48947.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=9d09311daa715e69b72699ea79687a534bbfbf8b39c09d2b169e3006e348a7f3)
SQLを表示
SELECT NULLIF(NULL, 'database')
FROM dual;
FROM dual;
上記のように第1引数にリテラルのNULL値は指定できませんが、第1引数で指定した列の値などが結果としてNULL値となる場合は問題ありません。
第1引数が結果としてNULL値となる場合は、第2引数がいかなる値であっても、NULL値が返されます。(第2引数がNULL値であれば等しいのでNULL値、第2引数がNULL値以外であれば等しくないので第1引数のNULL値が返される為)
[COALESCE関数]
COALESCE関数は引数の値を判定し、最初に見つかったNULL値以外の値を返す関数です。
使用法は以下の通りです。
COALESCE(式1, 式2 [, 式3 ...])
引数に指定された式全てがNULL値の場合、COALESCE関数はNULL値を返します。
![<img src="/mondai3/img/jpg/k48948.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19494/k48948.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=13a5acc39640cc8245b7e3709d7388054da0a755c8bae3bac6da2a5216871984)
SQLを表示
SELECT commission, salary, employee_id,
COALESCE(commission, salary, employee_id)
FROM employees
WHERE employee_id >= 1016;
COALESCE(commission, salary, employee_id)
FROM employees
WHERE employee_id >= 1016;
なお、COALESCE関数の引数は、すべて同じデータ型の値でなければなりません。異なるデータ型の値を指定するとエラーとなります(暗黙的なデータ変換は行われません)。
![<img src="/mondai3/img/jpg/kk48948.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19495/kk48948.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T034400Z&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%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=cc274b38df8b529dcab57bcf6fbdafdea574a0f0ddda2086abc9d6de22265959)
SQLを表示
SELECT COALESCE(employee_id, employee_name, hiredate)
FROM employees;
FROM employees;
NVL2関数について
r
ren4219
公開日 2023/03/25
お世話になります。
Oracle Silver SQLの問題でNVL2について質問なのですが、
「SELECT employee_name, NVL2(salary, salary + 10000, '') FROM employees;」回答は正常に実行できるということですが、NVL2関数は第2引数と第3引数が同じデータ型ではないといけない認識なのですが、何故正常に実行されるのでしょうか。
暗黙のデータ変換がされるのでしょうか。
お手数お掛けしますがご確認の程お願い致します。
c
cent700
2023/03/25 13:16
「''」は空文字(長さ0の文字列)ですが、Oracle DBはNullとして扱います。
そのため、第2引数が数値型、第3引数がNullであり、データ型が揃えられたという状態です。
以下リンクで空文字の扱いに触れています。
https://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements005.htm
コメント
この投稿に対して返信しませんか?
r ren4219
2023/03/25 19:27
Oracleでは長さが0の文字列はNULL値になるんですね。 ありがとうございます。