如果没有返回行，则返回具有空值的行 [英] Return row with null values if no rows returned

问题描述

如果一个返回值的查询没有返回任何行，那么关于如何返回 NULL 有很多问题.但是我没有发现任何关于多值查询在没有返回结果时返回 NULL 的信息.

There are plenty of questions about how to return NULL if a one value returned query returns no rows. But I didn't find anything about a multiple value query returning NULL when no results are returned.

主要查询:

SELECT UserOpinion.*, x, y, z... FROM subquery, (x) AS x, (y) AS y...
I trust you get the gist.

我的查询是一个子查询:

The query I have is a subquery:

(SELECT TOP 1                       
        u.BADId,                        
        Date,                       
        State,                      
        Note,                       
        Comment,                        
        Alias,                      
        Title,                      
        UserId,
        o.Id AS OpinionId                   
    FROM                        
        [Opinion] o                 
    RIGHT JOIN                      
        [User] u                        
        ON                          
        o.UserId = u.Id                     
        WHERE                       
        (Date IS NULL OR (Date = (SELECT MAX(Date)                              
                                FROM [Opinion]                              
                                WHERE UserId = o.UserId )))
        AND                             
            u.BADId = 'myvalue') AS UserOpinion; 

例如，我尝试了以下方法:

For instance I have tried the following:

(SELECT TOP 1 * FROM (SELECT TOP 1                      
        u.BADId,                        
        Date,                       
        State,                      
        Note,                       
        Comment,                        
        Alias,                      
        Title,                      
        UserId,
        o.Id AS OpinionId                   
    FROM                        
        [Opinion] o                 
    RIGHT JOIN                      
        [User] u                        
        ON                          
        o.UserId = u.Id                     
        WHERE                       
        (Date IS NULL OR (Date = (SELECT MAX(Date)                              
                                FROM [Opinion]                              
                                WHERE UserId = o.UserId )))
        AND                             
            u.BADId = 'myvalue' 
        UNION SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) t) AS UserOpinion; 

这将返回 NULL，无论 'myvalue' 是否存在.我想要的是，如果条目存在，它返回查询的值，或者如果不存在，它在每一列上返回 null.

This returns NULL, regardless if 'myvalue' exists. What I want is that it returns the value of the query if an entry exists or that it returns null on every column if it doesn't.

我还尝试了以下方法:

(COALESCE(SELECT TOP 1                      
    u.BADId,                        
    Date,                       
    State,                      
    Note,                       
    Comment,                        
    Alias,                      
    Title,                      
    UserId,
    o.Id AS OpinionId                   
FROM                        
    [Opinion] o                 
RIGHT JOIN                      
    [User] u                        
    ON                          
    o.UserId = u.Id                     
    WHERE                       
    (Date IS NULL OR (Date = (SELECT MAX(Date)                              
                            FROM [Opinion]                              
                            WHERE UserId = o.UserId )))
    AND                             
        u.BADId = 'myvalue'), NULL) AS UserOpinion; 

这里的主要问题是，如果myvalue"没有匹配项，则整个查询不返回任何内容.其他查询不需要 myvalue 并且进行多个独立查询不是性能限制的选项.

The main issue here is that the entire query returns nothing if 'myvalue' has no matches. The other queries don't need myvalue and making multiple independent queries is not an option for performance constraints.

我远非 SQL 专家，任何正确方向的建议将不胜感激.

I am far from an expert in SQL, any suggestions in the right direction will be greatly appreciated.

类似的问题，但在此处不适用:

Similar questions but not applicable here:

• ​​1、2、3 等等...

• 1, 2, 3 etc...

推荐答案

你可以用 left outer join 做到这一点:

You can do this with left outer join:

with s as (<your subquery here>)
select s.*
from (select 1 as x) x left join
     s
     on x.x = 1;

这将返回表中的值.如果没有匹配项，则查询返回一行，所有列都为 NULL.

This will return either the values in your table. If thee are no matches, the query returns one row with all columns being NULL.

注意:CTE 不是必需的.我只是把它放在强调查询的逻辑.您可以将子查询放在 s 所在的位置.

Note: the CTE is not required. I only put it in to emphasize the logic for the query. You can just put your subquery where s is.

这篇关于如果没有返回行，则返回具有空值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！