All/Any Implementation in SQL alongside IN

Hello I need to implement few Queries via dapper on a structure similar to below pseudocode.

// Tags
[{id: 1, name: "Tech"}, {id: 2, name: "SQL"}, {id: 3, name: "C#"}]

// BlogPost [
{
  Id: 1
  Tags: [1, 2] // Tech, Sql
},
{
   Id: 2,
   Tags: [1,3] // Tech, C#  
},
{
   Id: 3,
   Tags: [1,2,3] // Text, Sql, C#
}]

Given this query

SELECT 
    [Blogpost].*
From BlogPost blogPost
    LEFT JOIN BlogPostTags tags ON tags.blogId = blogPost.Id
WHERE blogpost.Tags IN (1,2)

Running above query i would expect this result. [{blogId: 1}, {blogId: 2}, {BlogId: 3}]

• I need to obtain a result looking like [{blogId: 1}] given the same parameters (1,2) in above query.

• Also I need to obtain a result like post [{blogId: 1}, {BlogId: 3}] given the parameters (1,2) in above query.

Does sql (MSSQL) have any nifty way to obtain these result?

Or how could I obtain these result in performant manner as I would have more joins in the actual query?

Thanks!

A "Contains Any" is just a simple EXISTS query.

var tagIds = new[]{ 1, 2, };

using connection = GetConnection();
const string query = @"
SELECT 
    bp.*
FROM BlogPost bp
WHERE EXISTS (SELECT 1
    FROM BlogPostTags tags
    WHERE tags.blogId = bp.Id
      AND tags.Id IN @tags
);

var results = await connection.QueryAsync<BlogPost>(query, new { tags, });

For the other two requirements, these are classic Relational Division, one being With Remainder, the other being Without Remainder.

For With Remainder, just do an EXISTS join, along with a grouped HAVING checking that all of them exist.

var tagIds = new[]{ 1, 2, };

using connection = GetConnection();
const string query = @"
SELECT 
    bp.*
FROM BlogPost bp
WHERE EXISTS (SELECT 1
    FROM BlogPostTags tags
    WHERE tags.blogId = bp.Id
      AND tags.Id IN @tags
    HAVING COUNT(*) = @count   -- are all input matched?
);

var results = await connection.QueryAsync<BlogPost>(query, new { tags, count = tags.Length });

For Without Remainder, it's similar, but you need to look for all tags, and then check in the HAVING that the only ones that match are the ones you are looking for.

var tagIds = new[]{ 1, 2, };

using connection = GetConnection();
const string query = @"
SELECT 
    bp.*
FROM BlogPost bp
WHERE EXISTS (SELECT 1
    FROM BlogPostTags tags
    WHERE tags.blogId = bp.Id
    HAVING COUNT(*) = @count   -- are all input matched?
       AND COUNT(*) = COUNT(CASE WHEN tags.Id IN @tags THEN 1 END)   -- are all tags in the list
);

var results = await connection.QueryAsync<BlogPost>(query, new { tags, count = tags.Length });

Note that Dapper parameterizes lists as (@p1, @p2) etc automatically.

You can also use a Table Valued Parameter, which is more efficient for large lists.

Define a Table Type, it's best to keep a few useful standard ones around.

CREATE TYPE dbo.IntList AS TABLE (value int PRIMARY KEY);

Then put the values into a DataTable and use .AsTableValuedParameter.

The query is also slightly different as you would use a join here.

var tagIds = new[]{ 1, 2, };
var table = new DataTable { Columns = { { "value", typeof(int) } } };
foreach (var tag in tagIds)
    table.Add(tag);

using connection = GetConnection();
const string query = @"
SELECT 
    bp.*
FROM BlogPost bp
WHERE EXISTS (SELECT 1
    FROM BlogPostTags tags
    LEFT JOIN @tags input ON input.value = tags.Id
    WHERE tags.blogId = bp.Id
    HAVING COUNT(*) = @count   -- are all input matched?
       AND COUNT(*) = COUNT(input.value)   -- did the join match all tags?
);

var results = await connection.QueryAsync<BlogPost>(query, new { tags = tags.AsTableValuedParameter("dbo.IntList"), count = tags.Length });