Let us understand what the NULL in SQL is.
- Null is an unknown value or an absence of a value/information.
- It is not same as zeros/empty values or character strings.
- 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
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:
FIRST_NAME + ‘ ‘ + MIDDLE_NAME + ‘ ‘ + LAST_NAME AS FULL_NAME
B. Nulls while addition (mathematical operation):
SALARY + BONUS AS TOTAL_REMUNERATION
Guess, the above statement also returns NULLC.
C. One solution is replacing Nulls with 0 in math operation as below:
SALARY + ISNULL(BONUS, 0) AS TOTAL_REMUNERATION
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.
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 firstname.lastname@example.org or Contact us