Mysql isnull usage

Mysql isnull usage

MySQL can use the ISNULL () function. However, it works in a different way than Microsoft's ISNULL () function.

Let's first look at the usage of several is null SQL statements:

Select * from newtable where name is null // obtain all data whose name is null in the newtable table

Select * from tbas_table where title not is null // retrieve all data with the title field not null in the tbas_table table

Let's look at the following statement:

SELECT 'click', 'title', 'created 'FROM dcfsda_table WHERE click is not null

Let's look at the following statement:

SELECT 'id', 'title', 'describle 'FROM bnsdh_table WHERE describle is not null

We can see that this table has 1025014 data records, and only one of the describle columns is null. That is, the index of the describle column stores the information of the first record of the column, and only one record is not stored. The DB2 optimizer tries to use either of the two methods. The first is to retrieve each record from the table and check whether its describle value is null. The second is to first locate all the non-empty data locations in the describle column in the table from the index, and then, when scanning the table, if you encounter these locations, you do not need to retrieve the data to determine whether it is empty, directly jump to the next record.

Is not null high-efficiency applications:

In some cases, it is said that is not null cannot use indexes, so it must be rewritten into other statements to improve efficiency by using indexes. Below are the test cases:

SQL statement: SELECT click FROM bsga_table WHERE click is not null

SQL statement after Rewriting: SELECT click FROM bsga_table WHERE click> 0 and click <100001

Whether it is null or is not null, the index cannot be used, but in different table data structure environments, it is possible that indexes may not be used, but the criteria for determining how to execute the query are performance.

Additional reading:

Is null is used to determine whether a value is null. If it is null, it is compared with null. If null is compared with any value, the result is false, so that no query record exists.

For example, if your record value is null and is null can be found, no results will be returned if it is null.

Note: For more exciting articles, please follow the help houseProgramming TutorialTopic.