Example of Checking for Empty or Null Columns in MySQL
Example 1: Checking for Null Values
So, In this example we have created the Database Info Consider a ’employees’ table with a column ‘middle_name.’ We want to retrieve records where the middle name is null.
-- SQL Code
CREATE DATABASE Info;
USE Info;
-- Create a sample 'employees' table
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
middle_name VARCHAR(30)
);
-- Insert sample data
INSERT INTO employees VALUES
(1, 'John', 'Doe', NULL),
(2, 'Jane', 'Smith', 'Ann'),
(3, 'Bob', 'Johnson', ''),
(4, 'Alice', 'Williams', 'Marie');
-- Query to retrieve records where middle name is null
SELECT *
FROM employees
WHERE middle_name IS NULL;
Output:
Explanation:
The query retrieves records from the ’employees’ table where the ‘middle_name’ column is NULL. In the provided sample data, this would include the record for ‘John Doe’ since his middle name is not specified (NULL). The record for ‘Bob Johnson’ is also included as it has an empty string for the middle name. The query excludes records with non-NULL middle names, such as ‘Jane Smith’ and ‘Alice Williams’.
Example 2: Checking for Empty or Null Values
Now, let’s use the COALESCE function to retrieve records where the ‘notes’ column is either empty or null.
-- SQL Code
CREATE DATABASE Info;
USE Info;
-- Create a sample 'tasks' table
CREATE TABLE tasks (
task_id INT PRIMARY KEY,
task_name VARCHAR(50),
notes VARCHAR(100)
);
-- Insert sample data
INSERT INTO tasks VALUES
(1, 'Task A', 'Important notes'),
(2, 'Task B', ''),
(3, 'Task C', NULL),
(4, 'Task D', 'Additional notes');
-- Query to retrieve records where notes are either empty or null
SELECT *
FROM tasks
WHERE COALESCE(notes, '') = '';
Output:
Explanation:
The query selects records from the ‘tasks‘ table where the ‘notes‘ column is either an empty string or NULL. In the provided sample data, this would include ‘Task B‘ with an empty string for notes and ‘Task C‘ with a NULL value for notes. Records with non-empty notes, such as ‘Task A‘ and ‘Task D‘, would be excluded from the output.
How to Check a Column is Empty or Null in MySQL?
In the databases, determining whether a column is empty or null is a common task. MySQL provides various techniques to perform this check, allowing users to filter and manipulate data efficiently. This article delves into the methods for checking if a column is empty or null in MySQL, outlining the syntax and presenting practical examples to illustrate their application.
So, In this article, we will explore the MySQL syntax for checking if a column is empty or null. Understanding this query is crucial for efficiently managing and querying databases. Let’s delve into the syntax and rationale behind this operation.
Contact Us