CODEWITHSUNDEEP
sql-servert-sql
Fanalea
Fanalea

Reputation: 171

Checking for NULL values in SQL

I'm using this query to get news articles by tags.

SELECT N.*
FROM News N
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT(N.Tags, ',') s1 JOIN
                   STRING_SPLIT('tag1,tag2,tag3', ',') s2
                   ON s1.value = s2.value
             );

I'm wondering if there is any possibility to return all news articles if no tags were defined in query. For example:

SELECT N.*
FROM News N
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT(N.Tags, ',') s1 JOIN
                   STRING_SPLIT(NULL, ',') s2
                   ON s1.value = s2.value
             );

Upvotes: 0

Views: 137

Answers (3)

Fanalea
Fanalea

Reputation: 171

I'm sorry for confusion around my query. I hope you will understand what I tried to achieve with this working example:

SELECT N.*
FROM News N
      WHERE CONCAT(',', N.Tags, ',') LIKE ISNULL('%@Request("tags")%', '') OR
      EXISTS (SELECT 1 FROM STRING_SPLIT(N.Tags, ',') s1 JOIN STRING_SPLIT('@Request("tags")', ',') s2 ON s1.value = s2.value)
OPTION (RECOMPILE);

Upvotes: 0

Thom A
Thom A

Reputation: 95950

If we assume that the value being passed to s2 is a parameter, as the above doesn't make a lot of sense otherwise, you could use an OR. I also suggest adding OPTION (RECOMPILE) as the query plan for the NULL and non-NULL queries will likely be different, thus this stops poor query caching:

SELECT N.* --Replace with Columns
FROM dbo.News N
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT(N.Tags, ',') s1 
                   JOIN STRING_SPLIT(@Param, ',') s2 ON s1.value = s2.value)
    OR @Param IS NULL
OPTION (RECOMPILE);

You can read more on why OPTION (RECOMPILE) is a good choice in Revisiting catch-all queries and An Updated Kitchen Sink Example.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270793

A simple method is a second check:

SELECT N.*
FROM News N
WHERE @tag_list IS NULL OR
      EXISTS (SELECT 1
              FROM STRING_SPLIT(N.Tags, ',') s1 JOIN
                   STRING_SPLIT(@tag_list, ',') s2
                   ON s1.value = s2.value
             );

Upvotes: 2

Related Questions