Also, the true meaning of null in SQL statements

Null indicates an ambiguous or unknown column value.

 

Test Table: testnull (ID varchar (32 ))

Database: Sybase asa11.0

Row data (''), (null)

 

When the database option ansinull is true (which is also the default option of the ASA database,

Select * From testnull where id = NULL

Select * From testnull where ID! = NULL

The result is empty.

Select * From testnull where ID is null

The result is (null)

Select * From testnull where ID is not null

Result ('')

 

When ansinull is false,

Select * From testnull where id = NULL

The result is (null)

Select * From testnull where ID! = NULL

Result ('')

 

From the above results, the null value is indeed a controversial thing, but there is no doubt that ansinull defines null accurately, that is, it cannot be equal to or unequal to the null value, the result is false regardless of whether it is equal or not.

While the unified is null, is not null is clearly clear, null is always true, and non-null is always false.

 

Let's look at the results in Oracle:

SQL> select * From testnull where ID is null;

ID
--------------------------------

 

SQL> select * From testnull where ID is not null;

No rows selected

SQL> select * From testnull where id = NULL;

No rows selected

SQL> select * From testnull where ID! = NULL;

No rows selected

The Null String ''is displayed as a null value in Oracle. Weird.

 

The reproduction process of Oracle is as follows:

SQL> Create Table testnull (ID varchar (32 ));

Table created.

SQL> insert into testnull values ('');

1 row created.

SQL> select * From testnull where ID is null;

ID
----------------------------------------------------------------

SQL> select count (*) from testnull where ID is null;

Count (*)
----------
1

SQL>