What are SQL NULL Values? - All That You Need to Know!

Unlock the secrets of SQL NULL values with this recipe. Gain valuable insights for dealing with NULL values in your database like a pro.| ProjectPro

Recipe Objective - What are SQL NULL Values? - All That You Need to Know! 

SQL (Structured Query Language) plays a pivotal role in managing and manipulating data. One concept that often puzzles SQL enthusiasts is the use of NULL values. Check out this recipe to delve into the intricacies of SQL NULL values, exploring their definition, use cases, and how to handle them effectively. 

What are SQL NULL Values?

A NULL value in SQL represents the absence of any data in a particular column. It is not the same as an empty string or zero; rather, it signifies the lack of a value in a field. NULL values are commonly used to indicate unknown or undefined data.

Understanding NULL Values in MySQL

MySQL, being a popular relational database management system, adheres to standard SQL practices when it comes to handling NULL values. However, MySQL has its nuances and specific behaviors, especially when it comes to indexing and comparisons. It's crucial to be aware of these distinctions for optimal database management.

How to Insert NULL Values in SQL? 

When it comes to inserting NULL values into a SQL table, the process is straightforward. In your SQL INSERT statement, use the keyword NULL in the corresponding column to represent the absence of data. For instance:

INSERT INTO TableName (ColumnName) VALUES (NULL);

This query inserts a record with a NULL value in the specified column.

Examples of NULL Values in SQL

Consider a table named Employees with a column MiddleName:

SELECT FirstName, MiddleName, LastName FROM Employees;

Output: 

FirstName | MiddleName | LastName

John      | NULL       | Doe

Alice     | Ann        | Smith

Bob       | NULL       | Johnson

Here, the MiddleName column contains NULL values for some records where the middle name is unknown or not applicable. 

Explore diverse SQL projects and acquire the in-demand skills that will make you indispensable in the world of data and analytics.

SQL Conditions for NULL Values

You can check if a NULL value exists using the following statements –

SQL IS NULL

The IS NULL condition is used to filter rows where a specific column contains NULL values. For example:

Syntax:
SELECT column_name1, column_name2, ...
FROM table_name WHERE column_name IS NULL

SQL IS NOT NULL

Selects rows where the specified column does not contain NULL values.

Syntax:
SELECT column_name1, column_name2, ...
FROM table_name WHERE column_name IS NOT NULL

Let us see an example –

Code:

SELECT customer_id FROM customers WHERE city IS NULL;

SQL ISNULL & SQL IS NOT NULL

Output:

Empty set (0.00 sec)

It returns an empty set because there are no null values in the city column of the customers table.

SQL WHERE IS NOT NULL

Combining conditions with the WHERE clause allows for more complex queries. This example selects rows with a specified value in one column and where another column is not NULL:

SELECT * FROM Orders WHERE ProductID = 1001 AND ShipDate IS NOT NULL;

SQL WHERE IS NULL

Similar to the previous example, this query retrieves rows where a specific condition is met, and a column is NULL: 

SELECT * FROM Inventory WHERE Quantity = 0 AND ExpiryDate IS NULL;

How to Handle NULL Values in SQL? 

Handling NULL values in SQL is a critical aspect of maintaining data accuracy and ensuring that your queries and operations behave as expected. This section will explore various strategies for handling NULL values effectively. 

SQL CASE WHEN NULL

The CASE statement can be employed to handle NULL values conditionally. For example:

SELECT FirstName, LastName,

  CASE WHEN MiddleName IS NULL THEN 'N/A' ELSE MiddleName END AS MiddleName

FROM Employees;

This query replaces NULL values in the MiddleName column with 'N/A'.

SQL Replace NULL with 0

In some cases, it's beneficial to substitute NULL values with a default or placeholder value, such as zero. The COALESCE function is commonly used for this purpose. Here's an example: 

SELECT ProductID, COALESCE(UnitsInStock, 0) AS StockQuantity FROM Products;

In this query, the COALESCE function checks if the UnitsInStock column contains NULL values. If it does, it replaces them with zero, ensuring that the result set includes a numeric value for the stock quantity.

SQL Set Value to NULL

Updating existing records to set a column's value to NULL under certain conditions can be necessary for data maintenance. The UPDATE statement is used for this purpose. For example:

UPDATE Employees SET ManagerID = NULL WHERE EmployeeID = 101;

This query updates the "ManagerID" column of the employee with ID 101, setting it to NULL. This operation can be crucial in scenarios where an employee no longer has a manager. 

SQL Project for Data Analysis using Oracle Database-Part 5

SQL Server IS NOT NULL

When dealing with Microsoft SQL Server, the IS NOT NULL condition is employed to filter records where a specific column has non-NULL values. For instance:

SELECT * FROM Orders WHERE ShippedDate IS NOT NULL;

This query retrieves orders from the "Orders" table where the "ShippedDate" column has a non-NULL value, indicating that the order has been shipped.

Gain Practical Experience in SQL with ProjectPro! 

Handling NULL values involves making informed decisions based on the specific requirements of your database and application. Whether you're replacing NULL with a default value, filtering based on NULL conditions, or updating records to set values to NULL, it's essential to choose the approach that aligns with your data integrity goals and application logic. To solidify your skills, gaining hands-on experience is essential, and ProjectPro offers the ideal platform for honing your SQL proficiency. With a diverse repository of over 270+ projects centered around data science and big data, ProjectPro ensures a real-world understanding of SQL concepts.

What Users are saying..

profile image

Ed Godalle

Director Data Analytics at EY / EY Tech
linkedin profile url

I am the Director of Data Analytics with over 10+ years of IT experience. I have a background in SQL, Python, and Big Data working with Accenture, IBM, and Infosys. I am looking to enhance my skills... Read More

Relevant Projects

MLOps Project to Deploy Resume Parser Model on Paperspace
In this MLOps project, you will learn how to deploy a Resume Parser Streamlit Application on Paperspace Private Cloud.

Recommender System Machine Learning Project for Beginners-4
Collaborative Filtering Recommender System Project - Comparison of different model based and memory based methods to build recommendation system using collaborative filtering.

Build a Multi Touch Attribution Machine Learning Model in Python
Identifying the ROI on marketing campaigns is an essential KPI for any business. In this ML project, you will learn to build a Multi Touch Attribution Model in Python to identify the ROI of various marketing efforts and their impact on conversions or sales..

Build Classification Algorithms for Digital Transformation[Banking]
Implement a machine learning approach using various classification techniques in Python to examine the digitalisation process of bank customers.

Topic modelling using Kmeans clustering to group customer reviews
In this Kmeans clustering machine learning project, you will perform topic modelling in order to group customer reviews based on recurring patterns.

MLOps using Azure Devops to Deploy a Classification Model
In this MLOps Azure project, you will learn how to deploy a classification machine learning model to predict the customer's license status on Azure through scalable CI/CD ML pipelines.

Time Series Classification Project for Elevator Failure Prediction
In this Time Series Project, you will predict the failure of elevators using IoT sensor data as a time series classification machine learning problem.

End-to-End Snowflake Healthcare Analytics Project on AWS-2
In this AWS Snowflake project, you will build an end to end retraining pipeline by checking Data and Model Drift and learn how to redeploy the model if needed

Build a Multi ClassText Classification Model using Naive Bayes
Implement the Naive Bayes Algorithm to build a multi class text classification model in Python.

Build CI/CD Pipeline for Machine Learning Projects using Jenkins
In this project, you will learn how to create a CI/CD pipeline for a search engine application using Jenkins.