How to make an OUTER JOIN return ZERO instead of NULL

Question

I am trying to accomplish this on SQL Server. The simplest table structure with data is shown below.

Table: Blog

BlogID | Title
-------+--------
  1    | FirstBlog
 23    | Pizza

Table: User

UserID | Name
-------+-----------
123    | james
444    | John

Table: UserBlogMapping

UserBlogMappingID | BlogID | UserID
------------------+--------+------
1                 |    1   |  123

I want to get FormID and UserBlogMappingID in one SQL query. If provided UserID is not in the mapping table, return ZERO otherwise return the valid userBlogMappingID. I am trying to run the below query but its not correct.

SELECT 
    B.BlogID, 
    BUM.BlogUserMappingID 
FROM 
    Blog AS B
        LEFT JOIN BlogUserMapping AS BUM ON B.BlogID = BUM.BlogID
WHERE 
    (B.BlogID = 23)  -- it exists in the table
    AND BUM.userID = 444 -- it is NOT in the mmaping table but I want a ZERO return in such case 

Assumption: We can assume that the UserID provided in the WHERE clause is always a valid UserID and is present in the User table.

Answer 1

You can use either ISNULL() or COALESCE() function:

SELECT 
    BlogID = B.BlogID, 
    BlogUserMappingID = COALESCE(BUM.BlogUserMappingID, 0)
-- ...

or the more verbose CASE expression:

--  BlogUserMappingID = CASE WHEN BUM.BlogUserMappingID IS NULL 
--                          THEN 0 ELSE BUM.BlogUserMappingID 
--                      END

The second correction that the query needs is to move the condition - regarding the table on the right side of the LEFT join - from WHERE to ON (otherwise the left join becomes an inner join):

FROM 
    Blog AS B
        LEFT JOIN BlogUserMapping AS BUM ON  B.BlogID = BUM.BlogID
                                         AND BUM.userID = 444 
WHERE 
    B.BlogID = 23 ;

---

To avoid multiple rows with same BlogID, you could use an inline subquery with TOP 1, instead of the left join:

SELECT 
    BlogID = B.BlogID, 
    BlogUserMappingID = COALESCE(
        (SELECT TOP (1) BUM.BlogUserMappingID
         FROM BlogUserMapping AS BUM 
         WHERE B.BlogID = BUM.BlogID
           AND BUM.userID = 444
         ORDER BY BUM.BlogUserMappingID DESC
        ), 0)
FROM 
    Blog AS B
WHERE 
    B.BlogID = 23 ;

Answer 2

You can try this:

ISNULL(count(*), 0)