NULL

Handle NULL values in SQL:

>  Primary Key column/s can not have NULL values
>  Foreign Key column can have any number of NULL values.
>  Distinct command allow one NULL value.
>  Unique command allow any number of NULL values.
>  Group functions such as SUM(), AVG(), MAX(), MIN(),COUNT() ignore NULL values. Such as : AVG(5,NULL)=5, SUM(5,NULL)=5

For example:

Name	Dept	Salary	comm	State
Ram	Technical	1000	null	TX
Sam	Admin	1100	null	CA
Hari	Mechanical	1200	null	TX
Raju	Helper	800	800	TX
Govind	HR	1000	null	CA
Murali	Driver	900	null	NY
Murali1	DHead	null	null	NY
Sita	Actor	1400	111	TX

1.   Select Count(salary), count(name), sum(salary), avg(salary), sum(salary)/8, max(salary), min(salary) from TableUnitTest;

Output: 

Count(salary)	count(name)	sum(salary)	avg(salary)	sum(salary)/8	max(salary)	min(salary)
7	8	7400	1057.142857	925	1400	800

2.    Select state, avg(salary) from TableUnitTest group by state ;

State      avg(salary)

CA          1050

NY          900

TX           1100

 

>  NVL > To convert a NULL value to other values , use NVL command. Such as:
Select Name, NVL(Income,0), Department from TableName;
In above query, if salary is NULL then it returns '0'.

>   COALESCE  >  use this command to replace NULL with value from another column in the same row. Such as :
Select FirstName, MiddleName, LastName, COALESCE (FirstName,LastName,MiddleName) as LegalName, Salary, Department from TableName;
In above example, if FirstName is NULL then LastName will be used as LegalName, if FirstName and LastName both are NULL then MiddleName will be used as LegalName.

> NVL2  >  use this command to manipulate a column which might have NULL value. Let's see an example:
Select Name, Salary, Commission, NVL2(Commission, Salary+Commision,Salary) as TotalSalary Department from TableName;
In above example, if commission is not null then it displays Salary+Commission, if Commission is NULL then it displays only Salary as Total Salary. 

 

>NULLif  > example: nullif(a,b) will return null if a and b are equal

Let's see in the below table:

Name	Dept	Salary	comm
Ram	Technical	1000	null
Sam	Admin	1100	null
Hari	Mechanical	1200	null
Raju	Helper	800	800
Govind	HR	1000	null
Murali	Driver	900	null
Murali1	DHead	null	null
Sita	Actor	1400	111

Select nullif(salary,comm) as NullCheck from TableUnitTest where name = 'Raju';

Output: 

NullCheck

null