IN Operator with SqlDependency is not working

Question

Does sqldependency work with IN operator? My Command is:

SELECT n.PublishedAt
FROM dbo.Navigations n
WHERE NavigationGroupId IN (SELECT ng.Id FROM dbo.NavigationGroups ng
                            WHERE ng.SiteId = 1
                              AND ng.IsActive = 1
                              AND ng.[Type]= 'Secondary')
  AND n.IsActive = 1

I had a look to MSDN Dodumentation but nothing mentioned about IN operator.

Thanks in advance.

Answer 1

From the documentation:

"The statement must not contain subqueries, outer joins, or self-joins."

IN usually maps to a semi-join. Damien_The_Unbeliever notes, that:

IN() is defined as either a subquery or a sequence of expressions - the one in the OPs question s a subquery.

Therefore it probably is not supported.

Can you express the IN as an inner join? This preserves semantics if the IN query produces at most one row for each outer row. If there are more then rows will be duplicated. That does not matter for the notification, though. It should fire under exactly the same circumstances.