SQL Null Values

In this lesson, you will learn how to create mandatory fields, how to check whether the field is null or not and how you can replace NULL values.

What Is a Null Value?

A field with a NULL is a field with no value.

An optional field in a table is a field that can be left blank. When you insert a new record or update an existing one, if you didn't set a value for the optional fields they will have NULL as a value.

Create A Not Null Field In SQL

By default, normal fields in the table can be left blank.
To make the field not accept NULL values, you have to define it as NOT NULL when you create the table.

If the field is not defined as NOT NULL on table creation but later it's modified to be so, then the new records cannot have that field blank and you have to pass values for the old empty fields only in case you update a record with an old NULL value.

Preparing A Database

Since you will learn how to deal with NULL values, you must have a database table with some NULL values to practice the operators that you are going to learn. Execute the following query to create the database.

-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;

-- Create a new database called "demo"
CREATE DATABASE demo;

-- Select the "demo" database
USE demo;

-- Create a new table called "users" inside the selected database
CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    country VARCHAR(100)
);

-- Insert 10 records in the "users" table
-- We add null in the id column because its value will be generated automatically by the database server
-- We add null in the country column for some records on purpose for later examples
-- username column cannot be null because we defined it as not null
INSERT INTO users VALUES (null, "Emily", null);
INSERT INTO users VALUES (null, "Omar", null);
INSERT INTO users VALUES (null, "Olivia", "France");
INSERT INTO users VALUES (null, "William", "Norway");
INSERT INTO users VALUES (null, "Sophia", null);
INSERT INTO users VALUES (null, "Daniel", "Germany");
INSERT INTO users VALUES (null, "Ava", "Japan");
INSERT INTO users VALUES (null, "James", null);
INSERT INTO users VALUES (null, "Mia", "France");
INSERT INTO users VALUES (null, "Ethan", null);
    

This is the users table that we created in the demo database.

Check If The Field Is Null Or Not

To check whether the field has a value or not you can use IS NULL or IS NOT NULL operators in the condition.

• The IS NULL operator is used to check if the field has a value.
• The IS NOT NULL operator is used to check if the field does not have a value.

The following query returns all users that have a specific country.

SELECT * FROM users
WHERE country IS NOT NULL;
    

The following query returns all users that do not have a specific country.

SELECT * FROM users
WHERE country IS NULL;
    

Replace Null Values

When showing data for normal users it is better to show them meaningful words instead of the word NULL.

The way you can follow to replace the NULL value in a result varies from one database to another:

• In MySQL and SQLite, we use the IFNULL() function.
• In SQL Server and Access, we use the ISNULL() function.
• In Oracle, we use the NVL() function.

The following query returns all records in the users and replaces the NULL values in the country field with the text 'Unknown'.

SELECT id, username, IFNULL(country,'Unknown') AS 'country'
FROM users;
    

The Column Cannot Be Null Error

If you didn't pass a value for a NOT NULL field, you will get an error message saying:

If you try to insert a new record in the users table without passing a value for the username field it will fail.
Note: It will fail because we defined the username field as NOT NULL.

INSERT INTO users VALUES (NULL, NULL, 'Russia');
    

So, to solve the previous problem you have to pass a value for the username field.

INSERT INTO users VALUES (NULL, 'Natasha', 'Russia');
    

Null Values Checking Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "customers" and check whether it exists or not. the table should contain the following fields:

• id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
• first_name VARCHAR(50) NOT NULL
• last_name VARCHAR(50) NOT NULL
• phone VARCHAR(20)

3- Write a query that inserts 5 rows in the "customers" table.
Note: Don't pass a phone number to three of them.

4- Write a query that returns all users with a phone number value only.

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS customers (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20)
);

INSERT INTO customers (first_name, last_name, phone) VALUES
('Jack', 'Reed', NULL),
('Emma', 'Clark', '55512388'),
('Luke', 'Lee', NULL),
('Jim', 'Wilson', NULL),
('Lily', 'Brooks', '36018090');

SELECT * FROM customers
WHERE phone IS NOT NULL;