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

Gautam Vermani

Data Consultant at Confidential
linkedin profile url

Having worked in the field of Data Science, I wanted to explore how I can implement projects in other domains, So I thought of connecting with ProjectPro. A project that helped me absorb this topic... Read More

Relevant Projects

Build an Image Classifier for Plant Species Identification
In this machine learning project, we will use binary leaf images and extracted features, including shape, margin, and texture to accurately identify plant species using different benchmark classification techniques.

Predictive Analytics Project for Working Capital Optimization
In this Predictive Analytics Project, you will build a model to accurately forecast the timing of customer and supplier payments for optimizing working capital.

MLOps AWS Project on Topic Modeling using Gunicorn Flask
In this project we will see the end-to-end machine learning development process to design, build and manage reproducible, testable, and evolvable machine learning models by using AWS

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.

Deep Learning Project for Beginners with Source Code Part 1
Learn to implement deep neural networks in Python .

PyTorch Project to Build a LSTM Text Classification Model
In this PyTorch Project you will learn how to build an LSTM Text Classification model for Classifying the Reviews of an App .

Build a Review Classification Model using Gated Recurrent Unit
In this Machine Learning project, you will build a classification model in python to classify the reviews of an app on a scale of 1 to 5 using Gated Recurrent Unit.

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

Learn to Build Generative Models Using PyTorch Autoencoders
In this deep learning project, you will learn how to build a Generative Model using Autoencoders in PyTorch

Build a Face Recognition System in Python using FaceNet
In this deep learning project, you will build your own face recognition system in Python using OpenCV and FaceNet by extracting features from an image of a person's face.