SQL query – Return 0 for the column if no record found

I will like to see the specific fields returns with 0 value when there is no record found. Below are my SQL queries that I have tried so far.

SELECT Customer, Address, Number of lines, Date 
FROM table_name 
WHERE Date = '30-5-2022' AND Customer IN (A, B, C)

What it returns is only 1 row as below.

Customer	Address	Number of Lines	Date
A	3	RF	30-5-2022

But what I expected to see is:

Customer	Address	Number of Lines	Date
A	UK	33	30-5-2022
B	0	0	30-5-2022
C	0	0	30-5-2022

The customer B and C has no record on 30-5-2022 but I will still need to see the rows but certain columns can be 0. Please advise if anything that I have missed out? Thanks a lot!

Answer

Try below query:

SELECT A.Customer, ISNULL(B.Address, 0), 
ISNULL(B.[Number of lines],0), ISNULL(B.Date, '30-05-2022') Date
FROM
(
 SELECT DISTINCT Customer 
 FROM table_name
) A
LEFT JOIN table_name B
ON A.Customer = B.Customer
AND B.Date = '30-5-2022'

This will output all the customers present in the table. You can filter the customers with WHERE clause in the end of the above query based on your requirement.

dbfiddle Link