Is there a way to return 0 if values does not exist in table using SQL Server

I have put down below a query to retrieve from four tables which are

Sales_Invoice, New_Customer, Company_Information, Expense

Query:

select
    isnull(sum(Expense.Expense_Amount), 0.00), as ExpenseAmount, 
    Company_Information.Company_Name,
    Sales_Invoice.Invoice_No, Sales_Invoice.Invoice_Date, Sales_Invoice.Item_Name,
    New_Customer.Customer_Name, New_Customer.Customer_ID
from 
    Sales_invoice, Company_Information, New_Customer, Expense
where 
    Sales_Invoice.Customer_Id = New_Customer.Customer_ID 
    and Sales_Invoice.Invoice_No = Expense.Invoice_No
group by
    Company_Information.Company_Name,
    Sales_Invoice.Invoice_No, Sales_Invoice.Invoice_Date, Sales_Invoice.Customer_ID, 
    Sales_Invoice.Item_Name, New_Customer.Customer_Name, New_Customer.Customer_ID 

The query is working well but if the Expense table has no values Expense.Invoice_No does not match with Sales_Invoice.Invoice_No, then the query above will return empty rows.

But what I wish to do is that, if Expense.Invoice_No does not exists then I still want to have my rows provided that expense amount return 0.00

Answer

Use standard joins! Then, you can easily handle “missing” relations with a left join.

Your question suggests:

select
    coalesce(sum(e.expense_amount), 0.00) as expenseamount, 
    ci.company_name,
    si.invoice_no, si.invoice_date, si.item_name,
    nc.customer_name, nc.customer_id
from new_customer nc
inner join company_information ci on ???
inner join sales_invoice si       on si.customer_id = nc.customer_id 
left join expense e               on e.invoice_no = si.invoice_no
group by
    ci.company_name,
    si.invoice_no, si.invoice_date, si.customer_id, 
    si.item_name, nc.customer_name, nc.customer_id 

Note that you original code seems to me missing a join condition between the customers and companies. I represented it as ??? in the query.

You could also express the same logic with a correlated subquery, which would avoid outer aggregation:

select
    (
        select coalesce(sum(e.expense_amount), 0.00) 
        from expense e 
        where e.invoice_no = si.invoice_no
    ) as expenseamount, 
    ci.company_name,
    si.invoice_no, si.invoice_date, si.item_name,
    nc.customer_name, nc.customer_id
from new_customer nc
inner join company_information ci on ???
inner join sales_invoice si on si.customer_id = nc.customer_id