In SQL, NULL values are used to represent unknown values. It does not indicate any meaning or space or No. Hence we cannot compare NULL values with any other known values or any other column values. We cannot use any arithmetic operator on NULL values/ columns to compare them. Hence we need a separate method to compare their values. Thus SQL introduces ‘IS NULL’ and ‘IS NOT NULL’ clause to compare NULL values / columns.
When ‘IS NULL’ clause is used against a column, it checks if its value is NULL. If yes, then it returns TRUE, else FALSE. NULL does not have any datatype. Hence NULL value can be a column of any datatype.
SELECT * FROM PUBLISHER WHERE CITY IS NULL; — No records with NULL City is found
SELECT * FROM BORROWER WHERE PHONE IS NULL;
When ‘IS NOT NULL’ clause is used against a column, it checks if its value is not NULL. If yes, then it returns TRUE, else FALSE.
SELECT * FROM BORROWER WHERE PHONE IS NOT NULL;
