---
title: SQL Null Functions
description: "## SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions – SQL ХЭРВЭЭХООСОН(), ХООСОНБОЛ(), ЭВСЭХ() болон NVL() функцууд Look at the following “Products” table: Доорх “Бүтээгдэхүүнүүд” хүснэгтийг харна уу: | P_Id | ProductName | UnitPrice | UnitsInStock | UnitsOnOrder | | ---…"
url: "https://apprentice.mn/docs/sql-null-functions/"
updated: "2024-07-21"
category: SQL
---

# SQL Null Functions

## SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions – SQL ХЭРВЭЭХООСОН(), ХООСОНБОЛ(), ЭВСЭХ() болон NVL() функцууд

Look at the following “Products” table:

Доорх “Бүтээгдэхүүнүүд” хүснэгтийг харна уу:

| P_Id | ProductName | UnitPrice | UnitsInStock | UnitsOnOrder |
| --- | --- | --- | --- | --- |
| 1 | Jarlsberg | 10.45 | 16 | 15 |
| 2 | Mascarpone | 32.56 | 23 |   |
| 3 | Gorgonzola | 15.67 | 9 | 20 |

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

“НэгжЗахиалга” багана нь сонголттой бөгөөд ХООСОН утга байж болно.

Look at the following SELECT statement:

Доорх SELECT өгүүлбэрийг авч үзье:

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

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

Дээрх жишээнд, хэрэв “НэгжЗахиалга” баганын аль нэг утга ХООСОН бол үр дүн нь NULL болно.

---

## Solutions – Шийдлүүд

**MySQL**

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

MySQL-ийн `IFNULL()` функц нь илэрхийлэл ХООСОН бол өөр утга буцаах боломжийг олгодог:

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

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

эсвэл `COALESCE()` функцийг ашиглаж болно, ингэж:

> 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:

SQL Server-ийн `ISNULL()` функц нь илэрхийлэл ХООСОН үед өөр утга буцаах боломжийг олгодог:

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

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

эсвэл `COALESCE()` функцийг ашиглаж болно, ингэж:

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

**MS Access**

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

MS Access-ийн `IsNull()` функц нь илэрхийлэл ХООСОН бол ҮНЭН(-1), үгүй бол ХУДАЛ(0) буцаана:

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

**Oracle**

The Oracle `NVL()` function achieves the same result:

Oracle-ийн NVL() функц нь ижил үр дүнг гаргана:

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

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

эсвэл `COALESCE()` функцийг ашиглаж болно, ингэж:

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

         [https://www.addtoany.com/add_to/facebook?linkurl=https%3A%2F%2Fapprentice.mn%2Fdocs%2Fsql-null-functions%2F&linkname=SQL%20Null%20Functions](https://www.addtoany.com/add_to/facebook?linkurl=https%3A%2F%2Fapprentice.mn%2Fdocs%2Fsql-null-functions%2F&linkname=SQL%20Null%20Functions)[https://www.addtoany.com/add_to/mastodon?linkurl=https%3A%2F%2Fapprentice.mn%2Fdocs%2Fsql-null-functions%2F&linkname=SQL%20Null%20Functions](https://www.addtoany.com/add_to/mastodon?linkurl=https%3A%2F%2Fapprentice.mn%2Fdocs%2Fsql-null-functions%2F&linkname=SQL%20Null%20Functions)[https://www.addtoany.com/add_to/email?linkurl=https%3A%2F%2Fapprentice.mn%2Fdocs%2Fsql-null-functions%2F&linkname=SQL%20Null%20Functions](https://www.addtoany.com/add_to/email?linkurl=https%3A%2F%2Fapprentice.mn%2Fdocs%2Fsql-null-functions%2F&linkname=SQL%20Null%20Functions)[https://www.addtoany.com/share](https://www.addtoany.com/share)