How to pass default value to selected column name if null found with inner join with SQL

Question

I have a SQL query that shows details of jobs. here I have check if null value found with inner join then pass with precondition.

Here is my SQL query :

SELECT   
    Jobs.EmailsSent,
    Jobs.SubscriberCount,
    Jobs.CompletedOn,
    Jobs.PreparedEmailID,
    Jobs.JobID,
    Jobs.CreatedOn,
    tbl_Categories.CategoryName,
    tbl_Email_master.Title,
    tbl_From_master.Name As FromUsername,
    tbl_User_master.Name AS CreatedBy 
FROM
    Jobs 
INNER JOIN 
    tbl_Email_master ON Jobs.PreparedEmailID = tbl_Email_master.Id 
INNER JOIN 
    tbl_From_master ON Jobs.FromuserID = tbl_From_master.Id
                    AND  tbl_Email_master.FromUser = tbl_From_master.Id 
INNER JOIN 
    tbl_Categories ON tbl_Categories.Id = Jobs.CategoryID
                   OR (tbl_Categories.Id IS NOT NULL
                       AND Jobs.CategoryID IS NULL) 
-- here pass default value like ALL if Jobs.CategoryID found NULL
INNER JOIN 
    tbl_User_master ON Jobs.UserID = tbl_User_master.Id
                    AND tbl_Email_master.user_id = tbl_User_master.Id
                    AND tbl_From_master.user_id = tbl_User_master.Id
                    AND tbl_Categories.user_id = tbl_User_master.Id
WHERE 
    Jobs.JobID = '7'

Can we do with this?

--------------------------------updated--------------------------------

with this ans :

INNER JOIN tbl_Categories ON tbl_Categories.Id = coalesce(Jobs.CategoryID,tbl_Categories.Id) OR (tbl_Categories.Id IS NOT NULL AND coalesce(Jobs.CategoryID,tbl_Categories.Id)=tbl_Categories.Id)

i have retrieving row like :

now I just only want raws/raw with default category value ALL.

--------------------------------as text----------------------------------


                    CATEGORY NAME 
0   44  NULL    Friends
0   44  NULL    Family Relatives
0   44  NULL    Business Clients
0   44  NULL    Corporate Profiles
0   44  NULL    test
0   44  NULL    Infisms Clients
0   44  NULL    Infisms Clients

here i just want to return row/rows with Category Name as ALL.

Answer 1

Just use coalesce() function

...tbl_Categories.Id = coalesce(Jobs.CategoryID,tbl_Categories.Id) OR 
(tbl_Categories.Id IS NOT NULL 
                   AND coalesce(Jobs.CategoryID,tbl_Categories.Id)=tbl_Categories.Id)

assuming tbl_Categories.Id is a non-null column

Answer 2

You could try this:

INNER JOIN 
tbl_Categories ON tbl_Categories.Id = isnull(Jobs.CategoryID, tbl_Categories.Id)