Null and Not Null

In SQL Server, NULL is a special marker used to indicate that a data value does not exist in the database. NOT NULL is a constraint that ensures a column cannot have a NULL value. Here are detailed explanations and scenarios for both concepts:

NULL

• Definition: NULL represents missing or unknown data. It is not equivalent to an empty string or zero; it is a distinct value indicating the absence of any value.

• Usage: You use NULL when you want to represent that a piece of data is unknown or not applicable.

• Checking for NULL: To check if a column value is NULL, you use the IS NULL condition.

SELECT * FROM Employees WHERE MiddleName IS NULL;

• Setting a value to NULL: You can set a column's value to NULL during an INSERT or UPDATE.

UPDATE Employees SET MiddleName = NULL WHERE EmployeeID = 1;

NOT NULL

• Definition: NOT NULL is a constraint that specifies that a column must always have a value (i.e., it cannot be NULL).
• Usage: You define a column as NOT NULL when you need to ensure that every record must have a value for that column.
• Defining a NOT NULL column: When creating or altering a table, you can specify that a column should be NOT NULL.

CREATE TABLE Employees (

   EmployeeID INT PRIMARY KEY,

   FirstName NVARCHAR(50) NOT NULL,

   LastName NVARCHAR(50) NOT NULL,

   MiddleName NVARCHAR(50) NULL

);

Level Up Your SQL Server Skills at TrainingHub.io!

Scenarios

1. Creating a Table with NULL and NOT NULL Columns

CREATE TABLE Products (

   ProductID INT PRIMARY KEY,

   ProductName NVARCHAR(100) NOT NULL,

   Description NVARCHAR(255) NULL,

   Price DECIMAL(10, 2) NOT NULL,

   StockQuantity INT NULL

);

In this example, ProductName and Price are required fields (NOT NULL), while Description and StockQuantity can have NULL values.

2. Inserting Data with NULL Values

INSERT INTO Products (ProductID, ProductName, Price, StockQuantity)

VALUES (1, 'Laptop', 799.99, NULL);

Here, the StockQuantity is unknown, so it's set to NULL.

3. Querying Data with NULL Values

SELECT * FROM Products WHERE StockQuantity IS NULL;

This query retrieves all products where the StockQuantity is unknown.

4. Updating Data to NULL

UPDATE Products SET Description = NULL WHERE ProductID = 1;

This updates the Description of the product with ProductID 1 to NULL.

Join the growing community of Data Professionals by taking Data Analyst courses in Toronto through TrainingHub.io

5. Handling NULL in Expressions

• Concatenation: When concatenating strings, if one of the values is NULL, the result is NULL.

SELECT FirstName + ' ' + MiddleName + ' ' +LastName AS FullName FROM Employees;

If MiddleName is NULL, the entire FullName result will be NULL. To handle this, you can use the ISNULL or COALESCE function.

SELECT FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName AS FullName FROM Employees;

• Arithmetic Operations: Any arithmetic operation involving NULL results in NULL.

SELECT Price * (1 + TaxRate) AS FinalPrice FROM Products WHERE TaxRate IS NOT NULL;

If TaxRate is NULL, the FinalPrice will be NULL.

6. Using COALESCE and ISNULL Functions

• COALESCE: Returns the first non-NULL value in the list of arguments.

SELECT COALESCE(MiddleName, 'N/A') AS MiddleName FROM Employees;

• ISNULL: Replaces NULL with a specified replacement value.

SELECT ISNULL(MiddleName, 'N/A') AS MiddleName FROM Employees;

Best Practices

• Always specify NOT NULL for columns that must have a value to avoid unexpected issues.
• Use NULL sparingly and only when the absence of a value is meaningful.
• When handling potential NULL values in expressions, use COALESCE or ISNULL to ensure your results are as expected.

Transform Your Data Mastery - Join TrainingHub.io's SQL Server Program! Gain practical experience through our hands-on IT online courses, offered across Canada.