Sqlisnotnull and isnull usage

If a column in the 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 as a NULL value. The Processing Method of the NULL value is different from that of other values. NULL is used as a placeholder for unknown or unsuitable values. Note: NULL and 0 cannot be compared; they are not equivalent. Isnotnull instance 5 & gt; createtableBillings (6 & gt; & nbsp; & if a column in the 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 as a NULL value.

The Processing Method of the NULL value is different from that of other values.

NULL is used as a placeholder for unknown or unsuitable 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 Billings VALUES (1, 1, '2017-01-22 ', 2005, 1, '2017-04-22', 165 );
3> GO

(1 rows affected)
1> insert into Billings VALUES (2, 2, '2017-02-21 ', 2001, 1, '2017-02-22', 165 );
2> GO

(1 rows affected)
1> insert into Billings VALUES (3, 3, '2017-05-02 ', 2003, 1, '2017-04-12', 165 );
2> GO

(1 rows affected)
1> insert into Billings VALUES (4, 4, '2017-03-12 ', 1999, 1, '2017-04-18', 165 );
2> GO

(1 rows affected)
1> insert into Billings VALUES (5, 5, '2017-04-23 ', 2000, 1, '2017-04-17', 165 );
2> GO

(1 rows affected)
1> insert into Billings VALUES (6, 6, '2017-06-14 ', 2001, 1, '2017-04-18', 165 );
2> GO

(1 rows affected)
1> insert into Billings VALUES (7, 7, '2017-07-15 ', 2002, 1, '2017-04-19', 165 );
2> GO

(1 rows affected)
1> insert into Billings VALUES (8, 8, '2017-08-16 ', 2003, 1, '2017-04-20', 165 );
2> GO

(1 rows affected)
1> insert into Billings VALUES (9, 9, '2017-09-17 ', 2004, 1, '2017-04-21', 165 );
2> GO

(1 rows affected)
1> insert into Billings VALUES (0, 0, '2017-10-18 ', 2005, 1, '2017-04-22', 165 );
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 CreditTotal
--------------------------------------------------------------------------------------------------------------------
1 1 00:00:00. 000 165 1 00:00:00. 000 123 321
2 2 00:00:00. 000 165 1 2002-02-22 00:00:00. 000 123 321
3 3 00:00:00. 000 165 1 00:00:00. 000 123 321
4 4 00:00:00. 000 165 1 00:00:00. 000 123 321
5 5 00:00:00. 000 165 1 00:00:00. 000 123 321
6 6 00:00:00. 000 165 1 00:00:00. 000 123 321
7 7 00:00:00. 000 165 1 00:00:00. 000 123 321
8 8 00:00:00. 000 165 1 00:00:00. 000 123 321
9 9 00:00:00. 000 165 1 00:00:00. 000 123 321
0 0 00:00:00. 000 165 1 00:00:00. 000 123 321

(10 rows affected)
1>
2> drop table Billings;
3> GO

Is null determines the content to be null

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> * from titleauthor where au_id is null;
3> GO
Au_id title_id au_ord royaltyper
---------------------------------------------------------
NULL 2 1 60

(1 rows affected)
1> select * from titleauthor where au_id = null;
2> GO
Au_id title_id au_ord royaltyper
---------------------------------------------------------

(0 rows affected)