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.
