CODEWITHSUNDEEP
sqlsql-serversubquery
Jose Luis
Jose Luis

Reputation: 3363

Return row with null values if no rows returned

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.

Main query:

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;

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.

I also tried the following:

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

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.

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

Similar questions but not applicable here:

Upvotes: 4

Views: 6506

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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;

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

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.

Upvotes: 9

Related Questions