SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL general functions | NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL and NANVL

In SQL, especially in Oracle, there are several functions provided to handle nulls and make conditional evaluations. Here are some commonly used general functions:

1. NVL:

The NVL function is used to replace null values with a default value.

Syntax:

NVL(expression, default_value)

If the expression is null, NVL returns the default_value.

2. NVL2:

The NVL2 function is similar to NVL, but it works a bit differently. It returns the second value if the first one is NOT null, and the third value if the first one is null.

Syntax:

NVL2(expression, value_if_NOT_null, value_if_null)

3. DECODE:

The DECODE function provides if-then-else logic within SQL.

Syntax:

DECODE(expression, search_value1, result1 [, search_value2, result2,...,] [, default_result])

It compares expression to each search_value. If expression is equal to a search_value, it returns the corresponding result.

4. COALESCE:

The COALESCE function returns the first non-null value in its argument list.

Syntax:

COALESCE(value1, value2, ..., valueN)

5. NULLIF:

The NULLIF function compares two expressions. If they are equal, it returns null; otherwise, it returns the first expression.

Syntax:

NULLIF(expression1, expression2)

6. LNNVL:

The LNNVL function is used to evaluate a condition and return FALSE if the condition is TRUE, TRUE if the condition is FALSE, and TRUE if the condition is unknown.

Syntax:

LNNVL(condition)

7. NANVL:

The NANVL function is specifically for floating-point numbers. It returns an alternative value if the input is NaN (Not a Number).

Syntax:

NANVL(expression, alternative_value)

If the expression is NaN, NANVL returns the alternative_value.

Note: While many of these functions, like COALESCE and NULLIF, are standard across various RDBMS, others like NVL, NVL2, and DECODE are specific to Oracle. Always refer to the documentation of the RDBMS you are working with to understand the availability and nuances of each function.

NVL Function in SQL:
- Description: NVL is used to replace NULL values with a specified default value.
- Example:
```
SELECT employee_name, NVL(salary, 0) AS adjusted_salary
FROM employees;
```

Using NVL2 in SQL Queries:

Using NVL2 in SQL Queries:
- Description: NVL2 returns one value if a specified expression is not NULL and another if it is NULL.
- Example:
```
SELECT employee_name,
       NVL2(commission, 'Eligible for Commission', 'Not Eligible') AS commission_status
FROM employees;
```

COALESCE vs NVL in SQL Differences:

COALESCE vs NVL in SQL Differences:
- COALESCE: Accepts multiple parameters and returns the first non-NULL value.
```
SELECT COALESCE(column1, column2, column3, 'No Value') AS result
FROM table1;
```
- NVL: Accepts two parameters and returns the second parameter if the first is NULL.
```
SELECT NVL(column1, 'No Value') AS result
FROM table1;
```

NULLIF Function in SQL with Examples:

NULLIF Function in SQL:
- Description: NULLIF returns NULL if the two expressions are equal; otherwise, it returns the first expression.
- Example:
```
SELECT NULLIF(column1, 0) AS result
FROM table1;
```

LNNVL Function in Oracle SQL:

LNNVL Function in Oracle SQL:
- Description: LNNVL returns TRUE if the condition is FALSE or UNKNOWN, and FALSE if the condition is TRUE.
- Example:
```
SELECT employee_name
FROM employees
WHERE LNNVL(salary > 50000);
```

NANVL in SQL for Handling NaN Values:

NANVL in SQL for Handling NaN Values:
- Description: NANVL replaces NaN (Not a Number) values with a specified default value.
- Example:
```
SELECT column1, NANVL(column2, 0) AS adjusted_value
FROM table1;
```

SQL CASE Statement vs DECODE:

SQL CASE Statement vs DECODE:

CASE Statement: Provides conditional logic in a more readable and flexible way.

SELECT employee_name,
       CASE WHEN salary > 50000 THEN 'High Salary' ELSE 'Low Salary' END AS salary_category
FROM employees;

DECODE: Oracle-specific function for simple conditional expressions.

SELECT employee_name,
       DECODE(salary > 50000, 1, 'High Salary', 'Low Salary') AS salary_category
FROM employees;

COALESCE vs CASE in SQL:

COALESCE vs CASE in SQL:

COALESCE: Used to return the first non-NULL value among its arguments.

SELECT COALESCE(column1, column2, column3, 'No Value') AS result
FROM table1;

CASE Statement: Provides more flexibility in defining conditions and expressions.

SELECT
  CASE
    WHEN column1 IS NOT NULL THEN column1
    WHEN column2 IS NOT NULL THEN column2
    ELSE 'No Value'
  END AS result
FROM table1;

Examples of Using NULLIF and NVL Together in SQL:

Examples of Using NULLIF and NVL Together in SQL:
- Example:
```
SELECT employee_name,
       NVL(NULLIF(salary, 0), 50000) AS adjusted_salary
FROM employees;
```
  - This example uses NULLIF to handle zero values and NVL to replace resulting NULL values with a default salary.