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 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 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
  • SQL UNION OperatorThe SQL UNION OperatorThe UNION operator is used to combine the result-set of two or more SELECT statements.Each SELECT statement within UNION must ha… Read More
  • The SQL GROUP BY StatementThe SQL GROUP BY StatementThe GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each c… 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

  • Spring boot app (error: method getFirst()) failed to run at local machine, but can run on server
    The Spring boot app can run on the online server. Now, we want to replicate the same app at the local machine but the Spring boot jar file f...
  • Log activity in a Laravel app with Spatie/Laravel-Activitylog
      Requirements This package needs PHP 8.1+ and Laravel 9.0 or higher. The latest version of this package needs PHP 8.2+ and Laravel 8 or hig...
  • Failed to install 'cordova-plugin-firebase': CordovaError: Uh oh
    I had follow these steps to install an configure firebase to my cordova project for cloud messaging. https://medium.com/@felipepucinelli/how...
  • Laravel auth login with phone or email
          <?php     Laravel auth login with phone or email     <? php     namespace App \ Http \ Controllers \ Auth ;         use ...
  • Cashier package and Blade files
    I'm a little confused about this Cashier package. I installed it using the Laravel website (with composer), but noticed there's no...

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 (68)
  • 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

  • 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)

  • Failed to install 'cordova-plugin-firebase': CordovaError: Uh oh - 9/21/2024
  • pyspark XPath Query Returns Lists Omitting Missing Values Instead of Including None - 9/20/2024
  • SQL REPL from within Python/Sqlalchemy/Psychopg2 - 9/20/2024
  • MySql Explain with Tobias Petry - 9/20/2024
  • How to combine information from different devices into one common abstract virtual disk? [closed] - 9/20/2024

Laravel News

  • Improved Installation and Frontend Hooks in Laravel Echo 2.1 - 5/15/2025
  • Filter Model Attributes with Laravel's New except() Method - 5/13/2025
  • Arr::from() Method in Laravel 12.14 - 5/14/2025
  • Streamline API Resources with Laravel's Fluent Methods - 5/13/2025
  • Customize URL Handling with Laravel's Macroable URI Class - 5/13/2025

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