Subquery and NOT NULL

Question

I need some help with a subquery. My test column sometimes comes back NULL and if so I want to filter those out of my results set.

My stored procedure looks like this

SELECT
    pl.Id AS Id,
    pl.Name AS Name,
    f.[Url] AS PrimaryImageUrl,
    up.Id AS MemberId,
    up.ProfessionalName,up.
    AvatarUrl,
    test = (SELECT 
                c.Id AS Id,
                c.Name AS Name,
                c.ContentImageUrl AS ImageUrl,
                c.Price AS Price,
                c.BPM AS BPM,
                f.Id AS 'File.Id',
                f.Url AS 'File.Name',
                TotalCount = COUNT (c.Id) OVER()
            FROM
                dbo.Content c
            INNER JOIN 
                dbo.PlayListContents pm ON c.Id = pm.ContentId 
                                        AND pm.PlaylistId = pl.Id
            INNER JOIN
                dbo.Files f ON c.ContentFileId = f.Id
            FOR JSON PATH),
    TotalCount = COUNT(1) OVER()
FROM
    dbo.Playlist pl
INNER JOIN 
    dbo.UserProfiles up ON pl.UserId = up.UserId
INNER JOIN 
    [dbo].[Files] AS f ON pl.[PrimaryImageId] = f.[Id]
WHERE
    (pl.Name LIKE '%' + @searchInput + '%')
    AND test IS NOT NULL

Why is this last line, AND test IS NOT NULL invalid? I need my result set to have all results with test being NOT NULL

Instead of querying from a derived table - you could put that sub-query in a CROSS APPLY which will automatically filter out NULL values. And - in a CROSS APPLY you can get rid of the reference to the table dbo.Files and include WHERE c.ContentField = f.Id to correlate to the outer query. — Jeff, Jul 21 '21 at 21:37

score 1 · Answer 1 · answered Jul 21 '21 at 19:42

Try this

SELECT * FROM 
(Select pl.Id as Id
        ,pl.Name as Name
        ,f.[Url] as PrimaryImageUrl
        ,up.Id as MemberId
        ,up.ProfessionalName
        ,up.AvatarUrl
        ,test = ( select c.Id as Id
                    ,c.Name as Name
                    ,c.ContentImageUrl as ImageUrl
                    ,c.Price as Price
                    ,c.BPM  as BPM
                    ,f.Id as 'File.Id'
                    ,f.Url as 'File.Name'
                    ,TotalCount = count(c.Id)Over()
                            from dbo.Content c
                        inner join dbo.PlayListContents pm on c.Id = pm.ContentId and pm.PlaylistId = pl.Id
                        inner join dbo.Files f on c.ContentFileId = f.Id
                    for json path)      

        --,TotalCount = COUNT(1) OVER()
        from dbo.Playlist pl
        inner join dbo.UserProfiles up on pl.UserId = up.UserId
        inner join [dbo].[Files] as f ON pl.[PrimaryImageId] = f.[Id]
        
        
        where ( pl.Name LIKE '%' + @searchInput + '%')) a
        WHERE a.test IS NOT NULL

this was beautifully simple, are we worried about performance at all here? seems like it's running two queries — Chaz Lee, Jul 21 '21 at 19:54
Yeah... Get rid of any Select * you have and declare all of your columns, also try to index on any that are contained in the WHERE Clause, if this is a common query. Believe it or not, the engine will estimate a `SELECT *` inaccurately and mess up your query plan. I know this sounds implausible but that's the SQL-Server Engine in a nut shell - Implausible `¯\_(ツ)_/¯` Also when you tell anyone a logical criteria like Find X where Y is a criteria it's helps if they are sorted by (Indexed) Y, E.G. Find all trampolines in this catalogue, goes to index, T... Tr, Trampolines Page 18,209 done. — Xyloz Quin, Jul 22 '21 at 08:48

score 0 · Answer 2 · answered Jul 21 '21 at 20:56

Columns in the SELECT are not available in the WHERE, due to SQL's logical order of operations.

Instead, place the value in CROSS APPLY, then filter after that:

SELECT
    pl.Id AS Id,
    pl.Name AS Name,
    f.[Url] AS PrimaryImageUrl,
    up.Id AS MemberId,
    up.ProfessionalName,up.
    AvatarUrl,
    v.test,
    TotalCount = COUNT(1) OVER()
FROM
    dbo.Playlist pl
INNER JOIN 
    dbo.UserProfiles up ON pl.UserId = up.UserId
INNER JOIN 
    [dbo].[Files] AS f ON pl.[PrimaryImageId] = f.[Id]
CROSS APPLY (
  SELECT test =
    (SELECT 
                c.Id AS Id,
                c.Name AS Name,
                c.ContentImageUrl AS ImageUrl,
                c.Price AS Price,
                c.BPM AS BPM,
                f.Id AS 'File.Id',
                f.Url AS 'File.Name',
                TotalCount = COUNT (c.Id) OVER()
            FROM
                dbo.Content c
            INNER JOIN 
                dbo.PlayListContents pm ON c.Id = pm.ContentId 
                                        AND pm.PlaylistId = pl.Id
            INNER JOIN
                dbo.Files f ON c.ContentFileId = f.Id
            FOR JSON PATH
    )
) v
WHERE
    (pl.Name LIKE '%' + @searchInput + '%')
    AND v.test IS NOT NULL;

Subquery and NOT NULL

2 Answers2