助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26679
問題を開く
xとyの値は表の通りです。
次のSQL文の結果として誤っているものはどれですか。
SELECT x, y, COALESCE(x * 12 + y, x, y)
FROM dual;
次のSQL文の結果として誤っているものはどれですか。
SELECT x, y, COALESCE(x * 12 + y, x, y)
FROM dual;
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20088/48949.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T023338Z&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=f7c866c9602b865125d09d2d6bab22bd8bf32ec8a8d65896351c35b966828733)
正解
C
解説
COALESCE関数は引数の値を判定し、最初に見つかったNULL値以外の値を返す関数です。
設問の表のうち、Cは第1引数の計算結果と第2引数がNULL値となりますが、第3引数はNULL値ではないので、第3引数の値が返されるため誤りです。
以上より、
・C
が正解となります。
正解のSQL文の実行結果は次のようになります。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20089/k48949.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T023338Z&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=ee56740c2860ca4265a293c878930598b6b8a4029eead07fc3057fc8cc01b25f)
設問の表のうち、Cは第1引数の計算結果と第2引数がNULL値となりますが、第3引数はNULL値ではないので、第3引数の値が返されるため誤りです。
以上より、
・C
が正解となります。
正解のSQL文の実行結果は次のようになります。
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20089/k48949.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T023338Z&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=ee56740c2860ca4265a293c878930598b6b8a4029eead07fc3057fc8cc01b25f)
SQLを表示
SELECT COALESCE(NULL + 12 + 150, NULL, 150)
FROM dual;
FROM dual;
参考
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=20240727T023338Z&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=c440d782b897ef6843b6cb05fbea3104b44792bc5c29b57a02d8c89366bb2e40)
ただし、第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=20240727T023338Z&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=75c83683ff7954ff39c5286e4a83f86bc1d9ad0d2b184bbc2e4eb2498936aa2a)
[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=20240727T023338Z&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=7b46c6a852f9eca5b8f4b6289681efb532f791b59cccb92052d5abd05f9534f7)
ただし、第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=20240727T023338Z&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=96f82091a629cdd54c66169eb7f3587f5cd8574935939126b8eeb115b7c74a4b)
[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=20240727T023338Z&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=ca6d7ca235829667d9e4b2d670721d688817dcce999d66f255f7b12cb3ab8807)
なお、第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=20240727T023338Z&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=20ca6b1609906fdb99885a88d5afa0846944e8640ca8ef1eef001a67d1eb69ad)
上記のように第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=20240727T023338Z&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=6d15160d4c2a19afa4a12ec957b16c20d3838134bf780a4e1fdd2c9305be2268)
なお、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=20240727T023338Z&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=4eb30c8d528cfe050e1eddb14e2b376e733c1a481e17292ed9377fb13c3d392e)
[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=20240727T023338Z&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=c440d782b897ef6843b6cb05fbea3104b44792bc5c29b57a02d8c89366bb2e40)
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=20240727T023338Z&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=75c83683ff7954ff39c5286e4a83f86bc1d9ad0d2b184bbc2e4eb2498936aa2a)
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=20240727T023338Z&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=7b46c6a852f9eca5b8f4b6289681efb532f791b59cccb92052d5abd05f9534f7)
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=20240727T023338Z&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=96f82091a629cdd54c66169eb7f3587f5cd8574935939126b8eeb115b7c74a4b)
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=20240727T023338Z&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=ca6d7ca235829667d9e4b2d670721d688817dcce999d66f255f7b12cb3ab8807)
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=20240727T023338Z&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=20ca6b1609906fdb99885a88d5afa0846944e8640ca8ef1eef001a67d1eb69ad)
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=20240727T023338Z&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=6d15160d4c2a19afa4a12ec957b16c20d3838134bf780a4e1fdd2c9305be2268)
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=20240727T023338Z&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=4eb30c8d528cfe050e1eddb14e2b376e733c1a481e17292ed9377fb13c3d392e)
SQLを表示
SELECT COALESCE(employee_id, employee_name, hiredate)
FROM employees;
FROM employees;
式中のNULLの扱い
公開日 2023/03/21
今回の問題の選択肢C(x:NULL、y:150)のとき、
COALESCE(x * 12 + y, x, y)の第1引数がNULLになる理由がわかりません。
NULL * 12 + 150 は150にならないということでしょうか。
→式の中にNULLが入っている場合、答えは基本的にNULLなのでしょうか。(選択肢Aも同様)
初歩的な質問かもしれませんが、どなたか教えていただけないでしょうか。
この投稿に対して返信しませんか?
m masa12831
2023/03/21 21:33
そうなのですね!回答ありがとうございます。