Usage of ISNULL,NULLIF,COALESCE in SQL

IsNull is to determine if NULL

And Nullif is to change the value to null.

Coalesce is replacing null with something else.

SELECT Employee_id,first_name,last_name,nullif (sales_quota,-1) as QUOTA

From Employees

is to turn 1 into NULL.

COALESCE (expression 1, expression 2,.... Expression N)

From the front to the back, who is not NULL to show who

Select Employee_id,first_name,last_name,

COALESCE (Appt_quota, (Select Min (Appt_quota) from employees), 0) as quota
From Employees
Where Department = ' Marketing '

Report:
ISNULL (Check_expression, Replacement_value)

• Check_expression and Replacement_value data types must be consistent
• If Check_expression is NULL, Replacement_value is returned
• If Check_expression is not NULL, the check_expression is returned

Nullif is used to check two expressions, syntax:
Nullif (expression, expression)

• Returns null if two expression is equal, which is the data type of the first expression
• If two expression is not equal, the first expression is returned

Example 1:

CREATE TABLE Test_table (
  Idnum number,
  CAT   VARCHAR2 (200),
  Price number
);
INSERT into Test_table VALUES (1, ', 10);
INSERT into Test_table VALUES (2, ' cat1 ', 10);
INSERT into Test_table VALUES (3, ' CAT1 ', 3);
INSERT into Test_table VALUES (4, ' Cat2 ', 11);
INSERT into Test_table VALUES (5, ' cat2 ', 10);
INSERT into Test_table VALUES (6, ', 10);

  COALESCE (CAT, ' unknown '),
  

Result

COALESCE (CAT, ' UNKNOWN ') SUM (price)
Unknown 20
CAT1 13
Cat2 21

Note: Coalesce compared to isnull, coalesce similar case, can be multiple input,isnull only 2 parameter

Coalesce cannot complete conversion of type, otherwise error

Usage of ISNULL,NULLIF,COALESCE in SQL