Today, I will discuss about How to calculate the number of nulls that gets populated with left join or %age of matched data or count of not nulls using left join.
CASE 1:
Calculate the number of nulls that gets populated from right hand table using left join.
Assume , I have two employee table EMP_TBL1 and EMP_TBL2. See the data for the same.
EMP_TBL1 EMP_TBL2
below is the code used to get number of nulls that comes from right side table using left join.
select count(*)-count(y_empid) from (
select x.emp_id ,y.emp_id as y_empid
from EMP_TBL1 x
left join EMP_TBL2 y
on y.emp_id = x.emp_id
and y.emp_name = x.emp_name
and y.dept = x.dept )A
CASE 2:
Calculate the number of not nulls that comes from right side table using left join.
Consider the same datasets mentioned above. below is the query to achieve the same.select count(y_empid) from (
select x.emp_id ,y.emp_id as y_empid
from EMP_TBL1 x
left join EMP_TBL2 y
on y.emp_id = x.emp_id
and y.emp_name = x.emp_name
and y.dept = x.dept )A
CASE 3:
Calculate the percentage of matched records between two tables . Here the assumption is emp_ids are unique in each table. If it is not unique in your case, please use distinct clause before calculate the matched records.select count(y_empid)/count(*) from (
select x.emp_id ,y.emp_id as y_empid
from EMP_TBL1 x
left join EMP_TBL2 y
on y.emp_id = x.emp_id
and y.emp_name = x.emp_name
and y.dept = x.dept )A