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
(
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