CoderFunda
  • Home
  • About us
    • Contact Us
    • Disclaimer
    • Privacy Policy
    • About us
  • Home
  • Php
  • HTML
  • CSS
  • JavaScript
    • JavaScript
    • Jquery
    • JqueryUI
    • Stock
  • SQL
  • Vue.Js
  • Python
  • Wordpress
  • C++
    • C++
    • C
  • Laravel
    • Laravel
      • Overview
      • Namespaces
      • Middleware
      • Routing
      • Configuration
      • Application Structure
      • Installation
    • Overview
  • DBMS
    • DBMS
      • PL/SQL
      • SQLite
      • MongoDB
      • Cassandra
      • MySQL
      • Oracle
      • CouchDB
      • Neo4j
      • DB2
      • Quiz
    • Overview
  • Entertainment
    • TV Series Update
    • Movie Review
    • Movie Review
  • More
    • Vue. Js
    • Php Question
    • Php Interview Question
    • Laravel Interview Question
    • SQL Interview Question
    • IAS Interview Question
    • PCS Interview Question
    • Technology
    • Other

26 December, 2020

SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

 Programing Coderfunda     December 26, 2020     SQL Tutorial     No comments   

SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

Look at the following "Products" table:

P_IdProductNameUnitPriceUnitsInStockUnitsOnOrder
1Jarlsberg10.451615
2Mascarpone32.5623 
3Gorgonzola15.67920

Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.

Look at the following SELECT statement:

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;

In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL.


Solutions

MySQL

The MySQL IFNULL() function lets you return an alternative value if an expression is NULL:

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;

or we can use the COALESCE() function, like this:

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;

SQL Server

The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL:

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products;

MS Access

The MS Access IsNull() function returns TRUE (-1) if the expression is a null value, otherwise FALSE (0):

SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder))
FROM Products;

Oracle

The Oracle NVL() function achieves the same result:

SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
FROM Products;
  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook

Related Posts:

  • The SQL INSERT INTO SELECT StatementThe SQL INSERT INTO SELECT StatementThe INSERT INTO SELECT statement copies data from one table and inserts it into another table.INSERT INTO SELECT r… Read More
  • The SQL ANY and ALL OperatorsThe SQL ANY and ALL OperatorsThe ANY and ALL operators are used with a WHERE or HAVING clause.The ANY operator returns true if any of the subquery val… Read More
  • The SQL SELECT INTO StatementThe SQL SELECT INTO StatementThe SELECT INTO statement copies data from one table into a new table.SELECT INTO SyntaxCopy all columns into a new table… Read More
  • The SQL HAVING ClauseThe SQL HAVING ClauseThe HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.HAVING SyntaxSELECT … Read More
  • The SQL EXISTS OperatorThe SQL EXISTS OperatorThe EXISTS operator is used to test for the existence of any record in a subquery.The EXISTS operator returns true if the subqu… Read More
Newer Post Older Post Home

0 comments:

Post a Comment

Thanks

Meta

Popular Posts

  • Sitaare Zameen Par Full Movie Review
     Here’s a  complete Vue.js tutorial for beginners to master level , structured in a progressive and simple way. It covers all essential topi...
  • Credit card validation in laravel
      Validation rules for credit card using laravel-validation-rules/credit-card package in laravel Install package laravel-validation-rules/cr...
  • C++ in Hindi Introduction
    C ++ का परिचय C ++ एक ऑब्जेक्ट ओरिएंटेड प्रोग्रामिंग लैंग्वेज है। C ++ को Bjarne Stroustrup द्वारा विकसित किया गया था। C ++ में आने से पह...
  • Write API Integrations in Laravel and PHP Projects with Saloon
    Write API Integrations in Laravel and PHP Projects with Saloon Saloon  is a Laravel/PHP package that allows you to write your API integratio...
  • iOS 17 Force Screen Rotation not working on iPAD only
    I have followed all the links on Google and StackOverFlow, unfortunately, I could not find any reliable solution Specifically for iPad devic...

Categories

  • Ajax (26)
  • Bootstrap (30)
  • DBMS (42)
  • HTML (12)
  • HTML5 (45)
  • JavaScript (10)
  • Jquery (34)
  • Jquery UI (2)
  • JqueryUI (32)
  • Laravel (1017)
  • Laravel Tutorials (23)
  • Laravel-Question (6)
  • Magento (9)
  • Magento 2 (95)
  • MariaDB (1)
  • MySql Tutorial (2)
  • PHP-Interview-Questions (3)
  • Php Question (13)
  • Python (36)
  • RDBMS (13)
  • SQL Tutorial (79)
  • Vue.js Tutorial (69)
  • Wordpress (150)
  • Wordpress Theme (3)
  • codeigniter (108)
  • oops (4)
  • php (853)

Social Media Links

  • Follow on Twitter
  • Like on Facebook
  • Subscribe on Youtube
  • Follow on Instagram

Pages

  • Home
  • Contact Us
  • Privacy Policy
  • About us

Blog Archive

  • July (4)
  • September (100)
  • August (50)
  • July (56)
  • June (46)
  • May (59)
  • April (50)
  • March (60)
  • February (42)
  • January (53)
  • December (58)
  • November (61)
  • October (39)
  • September (36)
  • August (36)
  • July (34)
  • June (34)
  • May (36)
  • April (29)
  • March (82)
  • February (1)
  • January (8)
  • December (14)
  • November (41)
  • October (13)
  • September (5)
  • August (48)
  • July (9)
  • June (6)
  • May (119)
  • April (259)
  • March (122)
  • February (368)
  • January (33)
  • October (2)
  • July (11)
  • June (29)
  • May (25)
  • April (168)
  • March (93)
  • February (60)
  • January (28)
  • December (195)
  • November (24)
  • October (40)
  • September (55)
  • August (6)
  • July (48)
  • May (2)
  • January (2)
  • July (6)
  • June (6)
  • February (17)
  • January (69)
  • December (122)
  • November (56)
  • October (92)
  • September (76)
  • August (6)

  • Sitaare Zameen Par Full Movie Review - 7/7/2025
  • Step-by-step Vue.js Tutorial Beginner to Master - 7/7/2025
  • Tailwindcss best practices for responsive design - 7/1/2025
  • Tailwind CSS Tutorial (Beginner to Master) - 7/1/2025
  • Failed to install 'cordova-plugin-firebase': CordovaError: Uh oh - 9/21/2024

Laravel News

  • AI-Driven Development Insiders Launch: 500 Seats. 24 Hours. 50% Off - 7/14/2025
  • Convert Eloquent Models to HLS Video - 7/15/2025
  • Laravel's toUri() Method for Dynamic URL Construction - 7/14/2025
  • Set up an AI-powered Laravel Development Environment with Claude Code and MCP Servers - 7/14/2025
  • PHP Fatal Error Backtraces in PHP 8.5 - 7/11/2025

Copyright © 2025 CoderFunda | Powered by Blogger
Design by Coderfunda | Blogger Theme by Coderfunda | Distributed By Coderfunda