What does mysql's is null mean?

In mysql, "is null" refers to a null value query, which is used to determine whether the value of a field is null (NULL). If the value of the field is null, the query conditions are met and the record will be queried; if the value of the field is not null, the query conditions are not met. "is null" is a comparison operator, so it can be used anywhere an operator can be used, such as in a select or where statement.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL IS NULL: Null value query

MySQL provides the IS NULL keyword to determine whether the field value is null ( NULL). The null value is different from 0, which is also different from the empty string.

If the value of the field is null, the query conditions are met and the record will be queried. If the value of the field is not null, the query condition is not met.

The basic syntax format for using IS NULL is as follows:

IS [NOT] NULL

Among them, "NOT" is an optional parameter, indicating that the condition is met when the field value is not a null value.

If the value is null, the expression returns TRUE, otherwise it returns FALSE.

Note that MySQL does not have a built-in BOOLEAN type (Boolean value). It uses TINYINT(1) to represent the BOOLEAN value, that is, 1 represents TRUE and 0 represents FALSE.

is null is a comparison operator, so it can be used anywhere an operator can be used, such as in a select or where statement.

SELECT
	1 IS NULL,
	0 IS NULL,
NULL IS NULL;

To check that a field is not NULL, you can use is not null.

SELECT
	1 IS NOT NULL,
	0 IS NOT NULL,
NULL IS NOT NULL;

Example

Find customers without a sales representative using the IS NULL operator from the customers table:

SELECT
	customerName,
	country,
	salesRepEmployeeNumber 
FROM
	customers 
WHERE
	salesRepEmployeeNumber IS NULL 
ORDER BY
	customerName 
	LIMIT 5;

Special functions of MySQL IS NULL

In order to be compatible with ODBC programs, MySQL supports some special functions of the IS NULL operator .

1) If there are constraints such as NOT NULL and a field in the format of date or datetime that contains the special date '0000-00-00', you can use the is null operator to find it.

CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT,
    title VARCHAR(255),
    begin_date DATE NOT NULL,
    complete_date DATE NOT NULL,
    PRIMARY KEY(id)
);
 
INSERT INTO projects(title,begin_date, complete_date)
VALUES('New CRM','2020-01-01','0000-00-00'),
      ('ERP Future','2020-01-01','0000-00-00'),
      ('VR','2020-01-01','2030-01-01');
 
SELECT * FROM projects WHERE complete_date IS NULL;

Created a table named projects, whose complete_date field is not null and contains the special date '0000-00-00'.

Use complete_date IS NULL to get the row with the date '0000-00-00'.

2) Continue to use the projects table.

If the variable @@sql_auto_is_null is set to 1, you can get the value of the id generated column after the insert statement is executed using the is null operator.

Note that by default, @@sql_auto_is_null is set to 0.

 set @@sql_auto_is_null =1;
insert into projects (title,begin_date,complete_date)
values('MRP III','2010-01-01','2020-12-31');

select id from projects where id is null;

[Related recommendations: mysql video tutorial]

The above is the detailed content of What does mysql's is null mean?. For more information, please follow other related articles on the PHP Chinese website!