The MySQL <=>
operator is used to compare two values, including NULLs. This operator is essential for filtering records where one value is equal to another, accounting for NULLs.
SELECT column1, column2, ...
FROM table_name
WHERE column1 <=> value;
The <=>
operator has the following components:
column1, column2, ...
: The columns to be retrieved.table_name
: The name of the table from which to retrieve the data.column1 <=> value
: The condition to filter the records, where column1
is equal to a specified value
, accounting for NULLs.Let's look at some examples of the MySQL <=>
operator:
USE mydatabase;
This query sets the context to the database named mydatabase
.
Create a table to work with:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10, 2),
bonus DECIMAL(10, 2) DEFAULT NULL
);
This query creates a table named employees
with columns for id
, first_name
, last_name
, department
, salary
, and bonus
.
Insert some initial rows into the table:
INSERT INTO employees (first_name, last_name, department, salary, bonus)
VALUES ('John', 'Doe', 'HR', 50000.00, NULL),
('Jane', 'Smith', 'IT', 60000.00, 5000.00),
('Jim', 'Brown', 'IT', 55000.00, NULL),
('Jake', 'White', 'HR', 52000.00, 3000.00),
('Jill', 'Green', 'Marketing', 45000.00, 2000.00);
This query inserts five rows into the employees
table.
Use the <=>
operator to filter records based on a condition:
SELECT *
FROM employees
WHERE bonus <=> NULL;
This query retrieves all columns from the employees
table where the bonus
is equal to NULL.
Use the <=>
operator with multiple conditions:
SELECT *
FROM employees
WHERE department = 'IT' AND bonus <=> NULL;
This query retrieves all columns from the employees
table where the department
is 'IT' and the bonus
is equal to NULL.
Use the <=>
operator with multiple columns:
SELECT first_name, last_name
FROM employees
WHERE department <=> 'HR' AND bonus <=> NULL;
This query retrieves the first_name
and last_name
columns from the employees
table where the department
is equal to 'HR' and the bonus
is equal to NULL.
The MySQL <=>
operator is a powerful tool for filtering records based on a comparison condition that includes NULL values. Understanding how to use the <=>
operator is essential for effective data querying and analysis in MySQL.