Webinar Alert : Mastering Manual and Automation Testing! - Reserve Your Free Seat Now
Imagine you are in charge of distributing food packets for an office party. Each person will get one packet. You have made a list of all the employees’ names with a place against each of them for them to sign. In case of somebody is absent, you put a dot against his name.Now imagine you are handling the same situation again but this time electronically. You have created a small application having a small database containing the employee names attached to it. Whenever an employee takes a food packet, you mark him as present in the system, how the system handles a situation where a particular employee is absent. It puts a null value against his or her name.
Null is a way for the database to mark an entry as blank.Over the next few paragraphs, we will learn about SQL NULL Values. Or to know in-depth about SQL, else you can also enroll in an online SQL server training course and shape your ever-growing SQL career.
A field without a value is one with the value NULL. It is possible to insert a new record or update an existing record without adding a value to a field in an optional table. The value of the field will then be saved as NULL. Note: A NULL value is distinct from a zero or space-filled field.
Following are the different types of SQL Server Null Functions
The ISNULL function has different uses in SQL Server and MySQL. In SQL Server, the ISNULL () function replaces NULL values.
Syntax:
SELECT column(s), ISNULL(column_name, value_to_replace) FROM table_name; Let us take the case of [dbo].[Employee_Null_Test]
The SQL query looks like below
Query: Find the sum of the salary of all Employees; if the Salary of any employee is not available (or NULL value), use salary as 10000.
SELECT SUM(ISNULL(Salary, 10000) AS Salary
FROM Employee:
The output looks like below
This function is available in MySQL, not SQL Server or Oracle. This function takes two arguments. If the first argument is not NULL, the function returns the first argument. Otherwise, the second argument is returned. This function is commonly used to replace a NULL value with another value.
Syntax:
SELECT column(s), IFNULL(column_name, value_to_replace) FROM table_name; Let us again take the case of [dbo].[Employee_Null_Test].
Query: Find the sum of the salary of all Employees; if the Salary of any employee is not available (or NULL value), use salary as 10000.
SELECT SUM(IFNULL(Salary, 10000)) AS Salary FROM [dbo].[Employee_Null_Test];
The output looks like below
COALESCE function in SQL returns the first non-NULL expression among its arguments. The COALESCE function will return null if all the expressions evaluate to null.
Syntax:
SELECT column(s), COALESCE(expression_1,....,expression_n) FROM table_name;
Example:
Consider the following Contact_info table,
Query: Fetch the name and contact number of each employee.
SELECT Name, COALESCE(Phone1, Phone2) AS Contact
FROM Contact_info;
The output looks like below
The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned.
Syntax:
SELECT column(s), NULLIF(expression1, expression2)
FROM table_name;
Example:
Consider the following [dbo].[Employee_Null_Test];
Query
SELECT Store, NULLIF(Actual, Goal)
FROM [dbo].[Employee_Null_Test];;
The output looks like below
The importance of NULL in SQL Server is as follows.
The properties of Null values in SQL are as follows.
The following SQL ensures that the "ID," "LastName," and "FirstName" columns will NOT accept NULL values, and Age can accept the null value when the "Persons" table is created:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int NULL );
The output looks like the one below.
The database's NULL values are generally regarded as distinct from one another. The outcome of a comparison operation is deemed to be UNKNOWN when it involves a NULL. As a result, SQL makes use of logic with three values: True, False, and Unknown. When the logical connectives AND, OR, and NOT are used, defining the results of three-valued logical expressions is necessary.
SQL allows queries to determine whether a value in an attribute is NULL. SQL uses IS and IS NOT rather than = or NULL to compare an attribute value to NULL. This is because equality comparison is inappropriate. After all, SQL treats each NULL value as distinct from all other NULL values. Do you love working with data? Or want to pursue a career in the Microsoft SQL Server Database domain? But feel stuck with doubts? A comprehensive SQL career path will help you explore all the career options.
Now let us create an Employee table with the following SQL Statement.
create table Employee_Null_Test ( Fname varchar(max) NOT NULL, Lname varchar(max) NOT NULL, SSN integer Not Null, Salary integer Not Null, Super_ssn integer Null )
The structure looks like the one below.
Let us now insert some data. The script to insert data in the table is as below.
INSERT [dbo].[Employee_Null_Test] ([Fname], [Lname], [SSN], [Salary], [Super_ssn]) VALUES (N'John', N'Smith', 123456789, 300000, 3334) INSERT [dbo].[Employee_Null_Test] ([Fname], [Lname], [SSN], [Salary], [Super_ssn]) VALUES (N'Fraklin', N'Wong', 33445566, 40000, 7788) INSERT [dbo].[Employee_Null_Test] ([Fname], [Lname], [SSN], [Salary], [Super_ssn]) VALUES (N'James', N'Borg', 88995566, 55000, NULL)
The output looks like the below.
Suppose we find the Fname and Lname of the Employee having no Super_ssn then the query will be:
SELECT Fname, Lname FROM [dbo].[Employee_Null_Test] WHERE Super_ssn IS NULL;
The output looks like below
Now if we find the Count of the number of Employees having Super_ssn, the query looks like the one below.
SELECT COUNT(*) AS Count FROM [dbo].[Employee_Null_Test] WHERE Super_ssn IS NOT NULL;
The output looks like the one below.
The Advantages of Null Constraints in SQL Server
The disadvantages of Null Constraint are as follows.
SQL Training For Administrators & Developers
In this write-up, we discussed SQL Server Null constraint. We learned how to implement Null constraint and its advantages and disadvantages. We also learned about different SQL Server Null functions. This will become a starting reference for people wanting to study Null Constraints further. Still, have doubts regarding career benefits? Go through the SQL DBA career path and the top companies hiring SQL developers worldwide. Get in touch with our consultant today!
What is Schema in SQL With Example: All You Need to Know
Data Definition Language (DDL) Commands in SQL
How To Create Database Table-All You Need To know
What does a Database Administrator do? A Detailed Study
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment