sql - 如果使用 SQL 的内部连接找到 null，如何将默认值传递给选定的列名

Question

我有一个显示作业详细信息的 SQL 查询。在这里，我检查是否通过内部连接找到空值，然后通过前提条件。

这是我的 SQL 查询：

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'

我们可以这样做吗？

- - - - - - - - - - - - - - - - 更新 - - - - - - - - - ---------------

有了这个答案：

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)

我有检索行，如：

现在我只想要具有默认类别值 ALL 的 raws/raw。

--------------------------------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

在这里，我只想返回类别名称为 ALL 的行/行。

标签： sqlsql-servertsql

Answer 1

只需使用coalesce()功能

...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)

假设tbl_Categories.Id是一non-null列