MySQL IsNull usage explanation

MySQL IsNull usage explanation

MySQL can use the ISNULL () function. But it works a little differently from Microsoft's ISNULL () function.

Let's look at a few is null SQL usage first:

SELECT * from newtable where name is null//Get all data with name null value in NewTable table

SELECT * from tbas_table where title is null//Get all Data tbas_table table title field NOT NULL

Then look at the following statement:

SELECT ' click ', ' title ', ' created ' from dcfsda_table WHERE click ' isn't null

Then look at the following statement:

SELECT ' id ', ' title ', ' Describle ' from bnsdh_table WHERE describle was not null

We can see that this table has 1025014 data, where only one of the describle columns is a null value. That is, the index of the Describle column stores information for the 1,025,014 records in this column, and only one is not saved. In choosing how to do this, the DB2 optimizer will try to use two ways, the first is to take each record out of a table and see if its describle value is empty. The second is to find the location of all non-null data in the table from the index, and then, when you scan the table, if it is describle, skip the data to determine if it is empty and jump directly to the next record.

is not NULL high-efficiency application:

In some places, it is said that not NULL cannot take advantage of an index, so it can be rewritten to other statements to make it more efficient with indexes. Here is the test case:

SQL statement: SELECT Click From bsga_table WHERE click isn't null

Rewritten SQL statements: SELECT Click from bsga_table WHERE Click > 0 and click < 100001

Whether it is null or is not NULL, it is not as if it is null on the Web or is not NULL, but it is in a different table data structure environment, it is possible to take advantage of the index may not take advantage of the index, and decide how to execute the query criteria is performance.

Extended reading:

Is null to determine whether the value is null, with =null is compared to NULL, and null and any value for comparison operation result is false, there will be no query records.

For example, you have a record value of NULL, with IS null can be found, with =null will not return any results.

Note : Please pay attention to the triple programming Tutorials section for more wonderful articles .