数据库中ISNULL(exp1,exp2)函数的用法

在SQL SERVER中，-ISNULL()函数：用来判断一个变量是否为空，如果该变量为空，可以给变量赋一个默认值

  如：   ISNULL(@VaribleName, 'DefaultValue')    ISNULL(COLUMN_NAME, 'Default Value')

举个例子 ：

建立表student，和表Collegeinfo

USE [db_ldrk] GO /****** 对象: Table [dbo].[student] 脚本日期: 12/30/2010 16:14:53 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[student]( [studentid] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [studentname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [college] [int] NULL, [major] [int] NULL, [id] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]  

USE [db_ldrk] GO /****** 对象: Table [dbo].[Collegeinfo] 脚本日期: 12/30/2010 16:16:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Collegeinfo]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [parentid] [int] NULL, CONSTRAINT [PK_Collegeinfo] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]  

 

在查询窗口执行如下语句：select studentid,studentname, ISNULL(b.name, '') as collegename, ISNULL(c.name, '') as majorname from student left join Collegeinfo b on college = b.id left join Collegeinfo c on major = c.id 

结果如下：

                                   

mysql中于此相近的函数为：IFNULL(exp1,exp2)

NULLIF(expr1,expr2)     
  如果expr1 
  =   expr2     成立，那么返回值为NULL，否则返回值为   expr1。这和CASE   WHEN   expr1   =   expr2   
THEN   NULL   ELSE   expr1   END相同。