SQL is not null and is NULL usage

If a column in a table is optional, we can insert a new record or update an existing record without adding a value to the column. This means that the field will be saved with a NULL value.

NULL values are treated differently from other values.

NULL is used as a placeholder for unknown or inapplicable values.

Note: NULL and 0 cannot be compared; they are not equivalent.

is not NULL instance

5> CREATE TABLE Billings (
6>     bankerid            INTEGER,
7>     billingnumber      INTEGER,
8>     billingdate        datetime,
9 >     billingtotal       INTEGER,
10>      termsid            INTEGER,
11>      billingduedate     datetime,
12>     paymenttotal       INTEGER,
13>     CreditTotal         INTEGER
14>
15>)
16> go
1>
2> INSERT into Billi NGS VALUES (1, 1, ' 2005-01-22 ', 165, 1, ' 2005-04-22 ', 123,321);
3> Go

(1 rows affected)
1> INSERT into Billings VALUES (2, 2, ' 2001-02-21 ', 165, 1, ' 2002-02-22 ', 123,321);
2> Go

(1 rows affected)
1> INSERT into Billings VALUES (3, 3, ' 2003-05-02 ', 165, 1, ' 2005-04-12 ', 123,321);
2> Go

(1 rows affected)
1> INSERT into Billings VALUES (4, 4, ' 1999-03-12 ', 165, 1, ' 2005-04-18 ', 123,321);
2> Go

(1 rows affected)
1> INSERT into Billings VALUES (5, 5, ' 2000-04-23 ', 165, 1, ' 2005-04-17 ', 123,321);
2> Go

(1 rows affected)
1> INSERT into Billings VALUES (6, 6, ' 2001-06-14 ', 165, 1, ' 2005-04-18 ', 123,321);
2> Go

(1 rows affected)
1> INSERT into Billings VALUES (7, 7, ' 2002-07-15 ', 165, 1, ' 2005-04-19 ', 123,321);
2> Go

(1 rows affected)
1> INSERT into Billings VALUES (8, 8, ' 2003-08-16 ', 165, 1, ' 2005-04-20 ', 123,321);
2> Go

(1 rows affected)
1> INSERT into Billings VALUES (9, 9, ' 2004-09-17 ', 165, 1, ' 2005-04-21 ', 123,321);
2> Go

(1 rows affected)
1> INSERT into Billings VALUES (0, 0, ' 2005-10-18 ', 165, 1, ' 2005-04-22 ', 123,321);
2> Go

(1 rows affected)
1>
2>
3> SELECT *
4> from Billings
5> WHERE Billingtotal is not NULL
6> Go
Bankerid billingnumber billingdate billingtotal termsid billingduedate paymenttotal CreditTota L
----------- ------------- ----------------------- ------------ ----------- ----------------------- ------------ ------ -----
1 1 2005-01-22 00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321
2 2 2001-02-21 00:00:00.000 165 1 2002-02-22 00:00:00.000 123 321
3 3 2003-05-02 00:00:00.000 165 1 2005-04-12 00:00:00.000 123 321
4 4 1999-03-12 00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321
5 5 2000-04-23 00:00:00.000 165 1 2005-04-17 00:00:00.000 123 321
6 6 2001-06-14 00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321
7 7 2002-07-15 00:00:00.000 165 1 2005-04-19 00:00:00.000 123 321
8 8 2003-08-16 00:00:00.000 165 1 2005-04-20 00:00:00.000 123 321
9 9 2004-09-17 00:00:00.000 165 1 2005-04-21 00:00:00.000 123 321
0 0 2005-10-18 00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321

(Ten rows affected)
1>
2> drop table Billings;
3> Go

Is null judged null content

4>
5> CREATE TABLE titleauthor (
6> au_id varchar (20),
7> title_id varchar (20),
8> Au_ord tinyint NULL,
9> Royaltyper int NULL
10>)
11> Go
1>
2> Insert titleauthor values (NULL, ' 2 ', 1, 60)
3> Insert titleauthor values (' 2 ', ' 3 ', 1, 100)
4> Insert titleauthor VALUES (' 3 ', ' 4 ', 1, 100)
5> Insert titleauthor VALUES (' 4 ', ' 5 ', 1, 100)
6> Insert titleauthor VALUES (' 5 ', ' 6 ', 1, 100)
7> Insert titleauthor VALUES (' 6 ', ' 7 ', 2, 40)
8> Insert titleauthor VALUES (' 7 ', ' 8 ', 1, 100)
9> Insert titleauthor VALUES (' 8 ', ' 9 ', 1, 100)
10> Go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2> select * from titleauthor where au_id to null;
3> go
au_id                 title_id              au_ord royaltyper
------------------------------------------ ---------------
null                  2                          1         

(1 rows affected)
1> SELECT * from titleauthor where au_id = null;
2> Go
au_id title_id Au_ord Royaltyper
-------------------- -------------------- ------ -----------

(0 rows affected)