SQL Null Values

In SQL, ‘Null’ represents the absence of a value or an unknown value. It is used to signify that a data field does not contain any data or that the value is missing. Having a ZERO value and NO value is not the same.

SQL Null Values Syntax

SQL Null Values Inserting NULL Values Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, NULL, ...);

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, NULL, ...);

SQL Null Values Updating to NULL Syntax

UPDATE table_name
SET column_name = NULL
WHERE condition;

UPDATE table_name

SET column_name = NULL

WHERE condition;

SQL Null Values Selecting Non-NULL Values

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

SELECT column1, column2, ...

FROM table_name

WHERE column_name IS NOT NULL;

SQL Null Values Example

SQL Null Values Inserting NULL Values Example

This query effectively adds a record for a patient named Alice Brown, born on April 23,1991, with the gender indicated as female. The patient’s contact number is not included, which is acceptable due to the contact_number column allowing NULL values.

INSERT INTO Patients (patient_id, first_name, last_name, dob, gender, contact_number)
VALUES (13, 'Alice', 'Brown', '1991-04-23', 'F', NULL);

INSERT INTO Patients (patient_id, first_name, last_name, dob, gender, contact_number)

VALUES (13, 'Alice', 'Brown', '1991-04-23', 'F', NULL);

SQL Null Values Updating a Column to NULL

This query effectively updates the record for the patient with patient_id 12 by setting the contact_number to NULL. This means the contact number for this specific patient is now absent or undefined, and this update is applied only to the record with patient_id = 12.

UPDATE Patients
SET contact_number = NULL
WHERE patient_id = 12;

UPDATE Patients

SET contact_number = NULL

WHERE patient_id = 12;

SQL Null Values Selecting Records with NULL Values Example

This query retrieves the first_name and last_name of all patients from the Patients table where the contact_number is set to NULL. This means it will return the names of patients who do not have a recorded contact number.

SELECT first_name, last_name
FROM Patients
WHERE contact_number IS NULL;

SELECT first_name, last_name

FROM Patients

WHERE contact_number IS NULL;

SQL Null Values Selecting Records with Non-NULL Values Example

This query retrieves the first_name, last_name, and contact number of all patients from the Patients table where the contact_number is not NULL.

SELECT first_name, last_name, contact_number
FROM Patients
WHERE contact_number IS NOT NULL;

SELECT first_name, last_name, contact_number

FROM Patients

WHERE contact_number IS NOT NULL;

SQL Null Values Visual Diagram

SQL Null Values Labs