Tutorial by Examples

SELECT 1 NUM_COLUMN, 'foo' VARCHAR2_COLUMN from DUAL UNION ALL SELECT NULL, NULL from DUAL; NUM_COLUMNVARCHAR2_COLUMN1foo(null)(null)

SELECT 1 a, '' b from DUAL; AB1(null)

SELECT 3 * NULL + 5, 'Hello ' || NULL || 'world' from DUAL; 3*NULL+5'HELLO'||NULL||'WORLD'(null)Hello world

SELECT a column_with_null, NVL(a, 'N/A') column_without_null FROM (SELECT NULL a FROM DUAL); COLUMN_WITH_NULLCOLUMN_WITHOUT_NULL(null)N/A NVL is useful to compare two values which can contain NULLs : SELECT CASE WHEN a = b THEN 1 WHEN a <> b THEN 0 else -1 END comparison_without_n...

If the first parameter is NOT NULL, NVL2 will return the second parameter. Otherwise it will return the third one. SELECT NVL2(null, 'Foo', 'Bar'), NVL2(5, 'Foo', 'Bar') FROM DUAL; NVL2(NULL,'FOO','BAR')NVL2(5,'FOO','BAR')BarFoo

SELECT COALESCE(a, b, c, d, 5) FROM (SELECT NULL A, NULL b, NULL c, 4 d FROM DUAL); COALESCE(A,B,C,D,5)4 In some case, using COALESCE with two parameters can be faster than using NVL when the second parameter is not a constant. NVL will always evaluate both parameters. COALESCE will stop a...