Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Null Functions (Arent those Meetings?)

By Dinesh Priyankara,

Definitely you must have read the article by James Travis's called =NULL AND IS NULL that is very informative. After reading the article, I really wanted to gather more related information that are used in day-to-day operation and ended up with a couple of things. Then, as usual, I decided to share with you because it may helpful to you too.

About NULLs

Generally, null value is considered as unknown value that is not blank or not zero. Because they are unknown, operations like comparison, adding may return UNKNOWN instead of expected result. Go through BOL and see, you can find enough information regarding this.

Now let's go through couple of NULL related functions. Before that we need to create two tables that need to see the functionalities. Run the below code.
CREATE TABLE Orders ([ID] int PRIMARY KEY NOT NULL, CompanyName varchar(100) NULL, DateToBeShipped datetime NOT NULL,DateShipped datetime NULL) 
GO
CREATE TABLE OrderDetails (OrderID int REFERENCES Orders([ID]) NOT NULL, Product varchar(20) NOT NULL, Quantity int NOT NULL, Price money NOT NULL)
GO
CREATE TABLE OrderDetailsTemp (OrderID int REFERENCES Orders([ID]) NOT NULL, Product varchar(20) NOT NULL, Quantity int NOT NULL, Price money NOT NULL)
GO
INSERT INTO Orders VALUES (245, 'Company 1', '02/10/2004', '02/10/2004')
INSERT INTO Orders VALUES (246, 'Company 1', '02/20/2004', '02/25/2004')
INSERT INTO Orders VALUES (247, 'Company 2', '04/02/2004', NULL)
GO
INSERT INTO OrderDetailsTemp VALUES (246, 'Product 2', 75, 80)
INSERT INTO OrderDetailsTemp VALUES (246, 'Product 2', 2, 1000)
INSERT INTO OrderDetails VALUES (245, 'Product 1', 5, 100)
INSERT INTO OrderDetails VALUES (245, 'Product 2', 2, 75)
GO
INSERT INTO OrderDetailsTemp VALUES (247, 'Product 2', 75, 80)
INSERT INTO OrderDetailsTemp VALUES (247, 'Product 2', 2, 1000)

ISNULL

ISNULL is used to validate the expression and replace it with specified value if it is null. Do not confuse with IS NULL that is used to make comparison with NULLs.
See given examples below.
--this query return hyphen for DateShipped if it not entered (null) yet. 
SELECT [ID], CompanyName, ISNULL(CONVERT(varchar(8), DateShipped, 101), '-') AS DateShipped FROM Orders

--this query returns number of items for order number 247. (
SELECT [ID], CompanyName, (SELECT SUM(Quantity) FROM OrderDetails WHERE OrderID = [ID]) AS NofItems FROM Orders WHERE [ID] = 247

SELECT [ID], CompanyName, ISNULL((SELECT SUM(Quantity) FROM OrderDetails WHERE OrderID = [ID]),0) AS NofItems FROM Orders WHERE [ID] = 247
SELECT [ID], CompanyName, ISNULL((ISNULL((SELECT SUM(Quantity) FROM OrderDetails WHERE OrderID = [ID]), 
					(SELECT SUM(Quantity) FROM OrderDetailsTemp WHERE OrderID = [ID]))),0) AS NofItems 
FROM Orders WHERE [ID] = 247

The first query shows the basic functionality of ISNULL. It replaces the all null values with hyphen.
If you execute the second query, you will receive null for NofItems. The workaround is given with third query. There you can the usage of ISNULL with sub query.
The forth query has enhanced the third query that shows the NofItems from OrderDetailsTemp if record is not exists in OrderDetails.

NULLIF

With NULLIF, you can compare two expressions and it returns null if expressions are equal. If expressions are not equal, it will return the first expression. See the example below.
SELECT [ID], CompanyName FROM Orders WHERE NULLIF(DateToBeShipped, DateShipped) IS NULL

SELECT [ID], CompanyName FROM Orders 
WHERE 
	[ID] = 247
	AND
	NULLIF(     	(ISNULL((SELECT SUM(Quantity) FROM OrderDetails WHERE OrderID = [ID]),0))
			, 
				ISNULL((SELECT SUM(Quantity) FROM OrderDetailsTemp WHERE OrderID = [ID]),0)) IS NULL
The first query returns the orders that are shipped on time. Of course, you can get the same result by checking the equality of two values but let's use this query to see the NULLIF functionality.
The second query returns all orders that have equal quantities in OrderDetails and OrderDetailsTemp tables.

COALESCE

This function allows you to get the first non-null expression from multiple expressions. Remember that all the expressions should be compatible data types and if all expressions are null, it returns null.
SELECT [ID], CompanyName, COALESCE(DateShipped, DateToBeShipped) AS ShippingDate FROM Orders
The above query returns all orders with shipping date that is taken from either DateShipped or DateToBeShipped.

= NULL and IS NULL

Read the James Travis's article: =NULL AND IS NULL

SET ANSI_NULL_DFLT_ON and SET ANSI_NULL_DFLT_OFF

This SET statement allows you to set the default nullability of new columns when create or alter tables. Remember, this is a run-time setting not parse-time setting. By setting either ANSI_NULL_DFLT_ON to ON or ANSI_NULL_DFLT_OFF to OFF, all new columns will be allowed-null if the nullability is not explicitly specified and vice-varser. If both settings are set to OFF, the ANSI NULL default option of database is used. See the code below.
SET ANSI_NULL_DFLT_ON OFF
CREATE TABLE Test1 (col1 int)

SET ANSI_NULL_DFLT_ON ON
CREATE TABLE Test2 (col1 int)
The column in the first table will not be allowed null and the column in the second table will be allowed.

Though some functions are rarely used, they are very useful. So, use them and enjoy.
Total article views: 10133 | Views in the last 30 days: 2
 
Related Articles
FORUM

ISNULL QUESTION

is Not ISNULL(...) the opposite of ISNULL?

FORUM

using

using ISNull in where clause, query performance

FORUM

Select query

Select query

FORUM

Query performance for Ordering

Query performance for Ordering

ARTICLE

Twist in ISNULL function

Be careful while using the ISNULL function

Tags
miscellaneous    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones