Oracle SQL Basic day 11
Introduction
=============
In any production-grade SQL system, dealing with incomplete or missing data is unavoidable. Oracle SQL provides a rich set of built-in utility functions that help you manage NULL values, control conditional logic, format outputs, and build resilient queries. These functions are often overlooked, yet they form the foundation for writing robust and readable SQL.
In this blog, we’ll focus on utility or support functions like NVL, COALESCE, NULLIF, DECODE, CASE, TO_CHAR, and TO_DATE. You’ll learn their syntax, behavior, and real-world usage examples.
1. NVL()
========
Purpose: Replace NULL values with a specified default.
Syntax:
NVL(expression, replacement_value)
Example 1:
SELECT employee_id, salary, NVL(commission_pct, 0) AS final_commission
FROM employees;
Example 2:
SELECT NVL(hire_date, SYSDATE) AS actual_hire_date
FROM employees;
Key Point: NVL() evaluates both arguments even if the first is not NULL.
2. COALESCE()
=============
Purpose: Return the first non-null value from a list of expressions.
Syntax:
COALESCE(expr1, expr2, ..., exprN)
Example 1:
SELECT employee_id, COALESCE(phone, mobile, 'No contact') AS contact_info
FROM employees;
Example 2:
SELECT department_id, COALESCE(manager_id, -1) AS resolved_manager
FROM departments;
Key Point: Unlike NVL(), COALESCE() can take multiple arguments.
3. NULLIF()
===========
Purpose: Returns NULL if two expressions are equal, otherwise returns the first expression.
Syntax:
NULLIF(expr1, expr2)
Example 1:
SELECT NULLIF(salary, 0) AS adjusted_salary
FROM employees;
Example 2:
SELECT employee_id, NULLIF(job_id, 'CLERK') AS adjusted_job
FROM employees;
Key Point: A cleaner way to introduce NULLs conditionally.
4. DECODE()
===========
Purpose: Provides IF-THEN-ELSE logic similar to a switch-case.
Syntax:
DECODE(expression, search1, result1, ..., default)
Example 1:
SELECT employee_id, DECODE(department_id, 10, 'Admin', 20, 'IT', 'Other') AS dept_name
FROM employees;
Example 2:
SELECT employee_id, DECODE(commission_pct, NULL, 'No Commission', 'Commissioned') AS commission_status
FROM employees;
Key Point: Simpler for equality-based logic than CASE.
5. CASE
=======
Purpose: Provides advanced conditional logic.
Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default
END
Example 1:
SELECT employee_id, salary,
CASE
WHEN salary >= 10000 THEN 'High'
WHEN salary >= 5000 THEN 'Medium'
ELSE 'Low'
END AS salary_grade
FROM employees;
Example 2:
SELECT employee_id, job_id,
CASE job_id
WHEN 'SA_REP' THEN 'Sales'
WHEN 'IT_PROG' THEN 'IT'
ELSE 'Others'
END AS job_category
FROM employees;
Key Point: CASE is preferred for complex or range-based conditions.
6. TO_CHAR()
============
Purpose: Convert DATE or NUMBER to VARCHAR2 with formatting.
Syntax:
TO_CHAR(expression, format_model)
Example 1:
SELECT TO_CHAR(hire_date, 'DD-Mon-YYYY') AS formatted_hire_date
FROM employees;
Example 2:
SELECT TO_CHAR(salary, '9,999.99') AS formatted_salary
FROM employees;
Key Point: Ideal for presenting data in reports or user interfaces.
7. TO_DATE()
============
Purpose: Convert a string to a DATE data type.
Syntax:
TO_DATE(string, format_model)
Example 1:
SELECT TO_DATE('28-May-2024', 'DD-Mon-YYYY') AS parsed_date
FROM dual;
Example 2:
SELECT * FROM employees
WHERE hire_date >= TO_DATE('01-Jan-2020', 'DD-Mon-YYYY');
Key Point: Always use TO_DATE for comparing date strings in WHERE clauses.
8. NVL2()
=========
Purpose: If the first argument is NOT NULL, return the second; otherwise, return the third.
Syntax:
NVL2(expr1, expr2, expr3)
Example 1:
SELECT employee_id, commission_pct,
NVL2(commission_pct, 'Has Commission', 'No Commission') AS status
FROM employees;
Example 2:
SELECT NVL2(phone, 'Contactable', 'No Contact') AS contact_status
FROM employees;
Key Point: Think of NVL2 as a concise IF-THEN-ELSE for NULLs.
Use Cases in Real Life
=======================
These utility functions are incredibly helpful for:
- Data Cleansing: Replacing or ignoring NULLs.
- Reporting: Formatting numbers and dates.
- Conditional Logic: Creating dynamic columns.
- Default Values: Applying business rules to missing data.
- Safe Calculations: Avoiding divide-by-zero and invalid logic.
Summary Table
- NVL | Replaces NULL with a default value
- COALESCE | Returns the first non-NULL in a list
- NULLIF | Returns NULL if two expressions are equal
- DECODE | IF-THEN-ELSE logic for static values
- CASE | More powerful conditional expressions
- TO_CHAR | Converts dates/numbers to formatted strings
- TO_DATE | Parses strings into DATE data type
- NVL2 | Returns one value if NOT NULL, another if NULL
Conclusion
Utility functions in Oracle SQL are indispensable for writing production-grade queries. Whether you're building reports, cleaning up data, or applying business rules, functions like NVL, COALESCE, NULLIF, and CASE allow you to write safer and more readable code.
Learn to use these functions together—COALESCE inside CASE, or TO_CHAR with NVL—to build powerful expressions that adapt to real-world data challenges.
Practice these examples on your own database or Oracle Live SQL to master their behavior. With these tools in your SQL arsenal, you'll write cleaner, smarter, and more dynamic queries.
Here we ended oracle SQL Basic. Next topics will cover DML, DDL and DCL related topics
Hopefully it will give good understanding of oracle SQL
0 Comments