ISNULL not working

Question

I'm trying to get the salary of an employee and I'm not sure why ISNULL is not working here. Although the same query works when used outside subquery. Maybe I'm implementing the Isnull function wrong. Net salary is showing null even there is basic pay given.

 DECLARE @dateFrom datetime
 SET @dateFrom = '2018-03-01'
  DECLARE @dateTo datetime
 SET @dateTo = '2018-03-31'
 Select 
     [Emp].ID
    ,[Emp].EmpCode
    ,[Emp].FirstName + ' ' + [Emp].LastName AS Name
    ,[Emp].BasicPay
        ,((select SUM(InstallmentAmount) from HRM.tbl_EmployeeLoanInstallment [Loan]
       LEFT JOIN HRM.tbl_EmployeeLoan [EmpLoan] ON [EmpLoan].ID = [Loan].EmployeeLoanCode
       where [EmpLoan].EmpCode = Emp.ID AND IsReceived != 1
       AND CONVERT(date, [Loan].InstallmentDueOn) >=  CONVERT(date, @dateFrom)
    AND CONVERT(date, [Loan].InstallmentDueOn) <=  CONVERT(date, @dateTo))) AS LoanDeduction
    ,( SELECT 
        (ISNULL([Info].[BasicPay], 0))

        -
        (SELECT SUM(ISNULL([Loan].InstallmentAmount, 0))            
                    FROM [HRM].[tbl_EmployeeLoan] [EmpLoan]

                    FULL JOIN [HRM].[tbl_EmployeeInfo] [Info] ON [Info].[ID] = [EmpLoan].[EmpCode]
                    FULL JOIN [HRM].[tbl_EmployeeLoanInstallment] [Loan] ON [EmpLoan].[ID] = [Loan].[EmployeeLoanCode]
                    WHERE
                        CONVERT(date, [Loan].InstallmentDueOn) >=  CONVERT(date, @dateFrom)
                        AND 
                        CONVERT(date, [Loan].InstallmentDueOn) <=  CONVERT(date, @dateTo)
                        AND
                        [Info].[ID] = [Emp].[ID]

                    GROUP BY Info.ID)

    FROM  
    [HRM].[tbl_EmployeeInfo] [Info]

    WHERE Info.ID = Emp.ID
    GROUP BY [Info].[ID], [Info].[BasicPay]
) AS NetSalary

    from HRM.tbl_EmployeeInfo [Emp]

Output:

412 C3-345  Ayesha Fatima   20000.00    NULL        NULL
413 C3-651  Zainab Ali      20000.00    NULL        NULL
414 C1343   Ahmed Abdullah  20000.00    11111.11    8888.89
415 231     Ahmed  Aslam    20000.00    NULL        NULL
416 FS-16   Fawaz Aslam     25000.00    NULL        NULL

Answer 1

Are you sure that your subquery return a value? Seems to not be the case. If you really want a value, you should catch your null value at the end of your subquery.

,ISNULL(
( SELECT 
        (ISNULL([Info].[BasicPay], 0))

        -
        (SELECT SUM(ISNULL([Loan].InstallmentAmount, 0))            
                    FROM [HRM].[tbl_EmployeeLoan] [EmpLoan]

                    FULL JOIN [HRM].[tbl_EmployeeInfo] [Info] ON [Info].[ID] = [EmpLoan].[EmpCode]
                    FULL JOIN [HRM].[tbl_EmployeeLoanInstallment] [Loan] ON [EmpLoan].[ID] = [Loan].[EmployeeLoanCode]
                    WHERE
                        CONVERT(date, [Loan].InstallmentDueOn) >=  CONVERT(date, @dateFrom)
                        AND 
                        CONVERT(date, [Loan].InstallmentDueOn) <=  CONVERT(date, @dateTo)
                        AND
                        [Info].[ID] = [Emp].[ID]

                    GROUP BY Info.ID)
, 0)

    FROM  
    [HRM].[tbl_EmployeeInfo] [Info]