NULLs in SQL Server

  • Post category:General
  • Post author:

Let us understand what the NULL in SQL is.

  1. Null is an unknown value or an absence of a value/information.
  2. It is not same as zeros/empty values or character strings.
  3. Null value is even not equal to another null value.

That is, NULL != 0 , NULL != ‘’, NULL != NULL

Where != means not is equal to.

Nulls in SQL produce some unexpected results when querying the data, let us understand it one by one.
Create a table Employees: CREATE TABLE Employees

(             

               FIRST_NAME VARCHAR(50),

    MIDDLE_NAME VARCHAR(50),

               LAST_NAME VARCHAR(50),

               SALARY DECIMAL(12,2),

               BONUS DECIMAL(12,2)

);

Insert some sample rows:

INSERT INTO Employees (FIRST_NAME, MIDDLE_NAME, LAST_NAME, SALARY, BONUS) VALUES (John’, NULL, ‘Kevin’, 50000, 1000);
INSERT INTO Employees (FIRST_NAME, LAST_NAME, SALARY) VALUES (‘Victor’, ‘Paul’, 40000);

The first row inserted is for an employee named John Kevin. In this row we inserted a null value for the MIDDLE_NAME column.

The second row inserted is for an employee named Victor Paul. We have neglected the MIDDLE_NAME and BONUS columns in insert statement and database inserts a null value by default.

So, as far as database is concerned the middle names for both the employees is unknown and bonus for second employee is unknown.

A. Nulls while concatenation:

SELECT

   FIRST_NAME + ‘ ‘ + MIDDLE_NAME + ‘ ‘ + LAST_NAME AS FULL_NAME
FROM EMPLOYESS;

B. Nulls while addition (mathematical operation):

SELECT

              FIRST_NAME,

              SALARY,

              BONUS,

              SALARY + BONUS AS TOTAL_REMUNERATION

FROM EMPLOYEES;

Guess, the above statement also returns NULLC.

C. One solution is replacing Nulls with 0 in math operation as below:

SELECT

             FIRST_NAME,

             SALARY,

             BONUS,

             SALARY + ISNULL(BONUS, 0) AS TOTAL_REMUNERATION

FROM EMPLOYEES;

So, in the above case, the null values are replaced by 0 and it won’t affect the operation.

D. Another solution is permanent, while creating the table use the NOT NULL constraint.

CREATE TABLE Employees

(             

FIRST_NAME VARCHAR(50) NOT NULL,

MIDDLE_NAME VARCHAR(50) NOT NULL,

LAST_NAME VARCHAR(50)  NOT NULL,

SALARY DECIMAL(12,2) NOT NULL,

BONUS DECIMAL(12,2) NOT NULL,         

);

This will make sure that, while inserting into the columns which are specified with not null constraint fails when NULL value as an argument.

So, always be careful while querying the data it may include the NULL values.

Thanks.

Srikanth Kotapatti
Data Analyst
Addend Analytics

Addend Analytics is a Microsoft Power BI Gold Partner based in Mumbai, India. Apart from being authorized for Power BI implementations, Addend has successfully executed Power BI projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com or Contact us