Home Database Mysql Tutorial sql isnull函数在各种数据库中的用法分析

sql isnull函数在各种数据库中的用法分析

Jun 07, 2016 pm 05:51 PM
isnull sql

本文章介绍了现在主流的数据库中使用is null函数是一些区别和事项,有需要的朋友可以简单的参考一下。

isnull在查询中的应用,特别是再语句连接的时候需要用到

比如连接时候,某个字段没有值但是又要左连接到其他表上 就会显示空,

isnull可以判断是否是NULL,如果是给个默认值

 

isnull("字段名","默认的数据")


ISNULL  
  使用指定的替换值替换    NULL。  
   
语法  
  ISNULL    (    check_expression    ,    replacement_value    )    
   
  参数  
   check_expression  
   
   将被检查是否为    NULL的表达式。check_expression    可以是任何类型的。  
   
   replacement_value  
   
   在    check_expression    为    NULL时将返回的表达式。replacement_value    必须与    check_expresssion    具有相同的类型。    
   
   返回类型  
   返回与    check_expression    相同的类型。  
   
  注释  
   如果    check_expression    不为    NULL,那么返回该表达式的值;否则返回    replacement_value。  
   
   示例  
   A.    将    ISNULL    与    AVG    一起使用  
   下面的示例查找所有书的平均价格,用值    $10.00    替换    titles    表的    price    列中的所有    NULL    条目。   
    
 

 代码如下 复制代码
  USE    pubs  
   GO  
   SELECT    AVG(ISNULL(price,    $10.00))  
   FROM    titles  
   GO  

    
   下面是结果集:  
   
   --------------------------    
   14.24                                              
   
   (1    row(s)    affected)  
   
   B.    使用    ISNULL  
   下面的示例为    titles    表中的所有书选择书名、类型及价格。如果一个书名的价格是    NULL,那么在结果集中显示的价格为    0.00。   
    
  

 代码如下 复制代码
USE    pubs  
   GO  
   SELECT    SUBSTRING(title,    1,    15)    AS    Title,    type    AS    Type,    
         ISNULL(price,    0.00)    AS    Price  
   FROM    titles  
   GO  

    
   下面是结果集:   
  

 代码如下 复制代码
 
   Title                        Type                    Price                      
   ---------------    ------------    --------------------------    
   The    Busy    Execut    business            19.99                                              
   Cooking    with    Co    business            11.95                                              
   You    Can    Combat      business            2.99                                                
   Straight    Talk    A    business            19.99                                              
   Silicon    Valley      mod_cook            19.99                                              
   The    Gourmet    Mic    mod_cook            2.99                                                
   The    Psychology      UNDECIDED          0.00                                                
   But    Is    It    User      popular_comp    22.95                                              
   Secrets    of    Sili    popular_comp    20.00                                              
   Net    Etiquette        popular_comp    0.00                                                
   Computer    Phobic    ychology        21.59                                              
   Is    Anger    the    En    psychology        10.95                                              
   Life    Without    Fe    psychology        7.00                                                
   Prolonged    Data      psychology        19.99                                              
   Emotional    Secur    psychology        7.99                                                
   Onions,    Leeks,      trad_cook          20.95                                              
   Fifty    Years    in      trad_cook          11.95                                              
   Sushi,    Anyone?      trad_cook          14.99                                              
   
   (18    row(s)    affected) 

国外一些说明

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

Microsoft's ISNULL() function is used to specify how we want to treat NULL values.

The NVL(), IFNULL(), and COALESCE() functions can also be used to achieve the same result.

In this case we want NULL values to be zero.

Below, if "UnitsOnOrder" is NULL it will not harm the calculation, because ISNULL() returns a zero if the value is NULL:

SQL Server / MS Access

 代码如下 复制代码

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

Oracle does not have an ISNULL() function. However, we can use the NVL() function to achieve the same result:

 代码如下 复制代码

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

MySQL does have an ISNULL() function. However, it works a little bit different from Microsoft's ISNULL() function.

In MySQL we can use the IFNULL() function, like this: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

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What is the difference between HQL and SQL in Hibernate framework? What is the difference between HQL and SQL in Hibernate framework? Apr 17, 2024 pm 02:57 PM

HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).

Usage of division operation in Oracle SQL Usage of division operation in Oracle SQL Mar 10, 2024 pm 03:06 PM

"Usage of Division Operation in OracleSQL" In OracleSQL, division operation is one of the common mathematical operations. During data query and processing, division operations can help us calculate the ratio between fields or derive the logical relationship between specific values. This article will introduce the usage of division operation in OracleSQL and provide specific code examples. 1. Two ways of division operations in OracleSQL In OracleSQL, division operations can be performed in two different ways.

Comparison and differences of SQL syntax between Oracle and DB2 Comparison and differences of SQL syntax between Oracle and DB2 Mar 11, 2024 pm 12:09 PM

Oracle and DB2 are two commonly used relational database management systems, each of which has its own unique SQL syntax and characteristics. This article will compare and differ between the SQL syntax of Oracle and DB2, and provide specific code examples. Database connection In Oracle, use the following statement to connect to the database: CONNECTusername/password@database. In DB2, the statement to connect to the database is as follows: CONNECTTOdataba

Detailed explanation of the Set tag function in MyBatis dynamic SQL tags Detailed explanation of the Set tag function in MyBatis dynamic SQL tags Feb 26, 2024 pm 07:48 PM

Interpretation of MyBatis dynamic SQL tags: Detailed explanation of Set tag usage MyBatis is an excellent persistence layer framework. It provides a wealth of dynamic SQL tags and can flexibly construct database operation statements. Among them, the Set tag is used to generate the SET clause in the UPDATE statement, which is very commonly used in update operations. This article will explain in detail the usage of the Set tag in MyBatis and demonstrate its functionality through specific code examples. What is Set tag Set tag is used in MyBati

What does the identity attribute in SQL mean? What does the identity attribute in SQL mean? Feb 19, 2024 am 11:24 AM

What is Identity in SQL? Specific code examples are needed. In SQL, Identity is a special data type used to generate auto-incrementing numbers. It is often used to uniquely identify each row of data in a table. The Identity column is often used in conjunction with the primary key column to ensure that each record has a unique identifier. This article will detail how to use Identity and some practical code examples. The basic way to use Identity is to use Identit when creating a table.

How to implement Springboot+Mybatis-plus without using SQL statements to add multiple tables How to implement Springboot+Mybatis-plus without using SQL statements to add multiple tables Jun 02, 2023 am 11:07 AM

When Springboot+Mybatis-plus does not use SQL statements to perform multi-table adding operations, the problems I encountered are decomposed by simulating thinking in the test environment: Create a BrandDTO object with parameters to simulate passing parameters to the background. We all know that it is extremely difficult to perform multi-table operations in Mybatis-plus. If you do not use tools such as Mybatis-plus-join, you can only configure the corresponding Mapper.xml file and configure The smelly and long ResultMap, and then write the corresponding sql statement. Although this method seems cumbersome, it is highly flexible and allows us to

How to use SQL statements for data aggregation and statistics in MySQL? How to use SQL statements for data aggregation and statistics in MySQL? Dec 17, 2023 am 08:41 AM

How to use SQL statements for data aggregation and statistics in MySQL? Data aggregation and statistics are very important steps when performing data analysis and statistics. As a powerful relational database management system, MySQL provides a wealth of aggregation and statistical functions, which can easily perform data aggregation and statistical operations. This article will introduce the method of using SQL statements to perform data aggregation and statistics in MySQL, and provide specific code examples. 1. Use the COUNT function for counting. The COUNT function is the most commonly used

How to solve the 5120 error in SQL How to solve the 5120 error in SQL Mar 06, 2024 pm 04:33 PM

Solution: 1. Check whether the logged-in user has sufficient permissions to access or operate the database, and ensure that the user has the correct permissions; 2. Check whether the account of the SQL Server service has permission to access the specified file or folder, and ensure that the account Have sufficient permissions to read and write the file or folder; 3. Check whether the specified database file has been opened or locked by other processes, try to close or release the file, and rerun the query; 4. Try as administrator Run Management Studio as etc.

See all articles