SQL Reloaded: Null-ifying the World

    Do you remember the table used in previous post? In the respective case we provided all the values, what happens when a value is missing? How do we deal with such cases? For such cases we have the value NULL. Let's try to insert a NULL in one of the columns:

 

CREATE TABLE Table1(
ID int
, [Year] int
, Product varchar(30)
, Price decimal(13,2)
, Quantity decimal(13,3))

 

   Then we insterted in the table test data, let's try to insert also a NULL for one of the values:

 

-- inserting the test data 
INSERT Table1(ID, [Year], Product, Price, Quantity)
VALUES (1, 2002, NULL, 199.23, 100)

--checking table's content
SELECT *
FROM Table1

    We can check for NULL values using the IS NULL clasuse:

 

-- checking for NULLs
SELECT *
FROM Table1
WHERE Product IS NULL

If you don't like NULL values, you can replace them with other values, with the help of IsNull function. 

-- handling NULL values with a text 
SELECT ID, [Year], IsNull (Product, 'missing value'), Price, Quantity
FROM Table1

-- handling NULL values with a text (different result)
<span style="color: #000099;">SELECT</span> ID, [Year], IsNull(Product, 'The Prouct value is missing! Please correct it!') Product, Price, Quantity
<span style="color: #000099;">FROM</span> Table1

   As you can see, because the maximum length of Product column is 30, the text is trimmed after 30 characters. In order to solve this issue, we can reduce the error message to maximum 30 characters or convert the Product type from varchar(30) to varchar(50), enough to hold the whole string:

SELECT ID, [Year], IsNull(Cast(Product as varchar(50)), 'The Prouct value is missing! Please correct it!') Product, Price, Quantity
FROM Table1

-- inserting more NULL values.
INSERT Table1(ID, [Year], Product, Price, Quantity)
VALUES (1, 2002, 'Product 6', NULL, 100)
INSERT Table1(ID, [Year], Product, Price, Quantity)
VALUES (1, 2002, 'Product 7', 45.08, NULL)
INSERT Table1(ID, [Year], Product, Price, Quantity)
VALUES (1, 2002, 'Product 8', NULL, NULL)
INSERTTable1(ID, [Year], Product, Price, Quantity)
VALUES (1, NULL, 'Product 9', NULL, NULL)

      

 When we need to test for NULL values in multiple columns we could write something like this:

 

-- individual checks 
SELECT *
FROM Table1
WHERE Price IS NULL
OR Quantity IS NULL

-- simpler alternative 
SELECT *
FROM Table1
WHERE IsNull(Price1, Price2) IS NULL

These checks are useful when you need to check for two values which shouldn't be simultaneously NULL. For more columns with possible NULL values, when the values have the same type, we can rewrite the statements as follows:

 

-- checking for multiple values
SELECT *
FROM Table1
WHERE COALESCE(Price1, Price2, Price3) IS NULL

-- replacing NULL values 
SELECT ID
, [Year]
, IsNull(Product, 'The Prouct value is missing!') Product
, IsNull(Price, 0)
, IsNull(Quantity, 0)
FROM Table1

When inserting NULL values in a column with a NOT NULL constraint, the following error message will be shown:
Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'Product', table 'Learning.dbo.Table1'; column does not allow nulls. INSERT fails.

--droping the table
DROP TABLE Table1

-- creating the table anew
CREATE TABLE Table1(
ID NOT NULL
, [Year] int NOT NULL
, Product varchar(30) NOT NULL
, Price decimal(13,2) NOT NULL
, Quantity decimal(13,3) NOT NULL)

-- inserting NULL values
INSERT Table1(ID, [Year], Product, Price, Quantity)
VALUES (1, 2002, NULL, 199.23, 100)7

 

   Another way is to use defaults, thus if a value was not provided, it will be replaced with the DEFAULT values:

 

-- creating the table anew
CREATE TABLE Table1(
  ID int NOT NULL
, [Year] int NOT NULL
, Product varchar(30) DEFAULT 'Unknown' 
, Price decimal(13,2) NOT NULL
, Quantity decimal(13,3) NOT NULL)

-- Inserting some values
INSERT Table1(ID, [Year], Price, Quantity)
VALUES (1, 2002, 199.23, 100)
INSERT Table1(ID, [Year], Product, Price, Quantity)
VALUES (1, 2002, NULL, 199.23, 100)

--testing the output
SELECT *
FROM Table1