SQL Server: Logical equivalent of ALL query

Question

I have a following query (simplified):

SELECT
    Id
FROM
    dbo.Entity
WHERE
    1 = ALL (
        SELECT
            CASE
                WHEN {Condition} THEN 1
                ELSE 0
            END
        FROM
            dbo.Related
            INNER JOIN dbo.Entity AS TargetEntity ON
                TargetEntity.Id = Related.TargetId
        WHERE
            Related.SourceId = Entity.Id
    )

where {Condition} is a complex dynamic condition on TargetEntity.

In simple terms, this query should return entities for which all related entities match the required condition.

Unfortunately, that does not work quite well, since by SQL standard 1 = ALL evaluates to TRUE when ALL is applied to an empty set. I know I can add AND EXISTS, but that will require me to repeat the whole subquery, which, I am certain, will cause problems for performance.

How should I rewrite the query to achieve the result I need (SQL Server 2008)?

Thanks in advance.

Note: practically speaking, the whole query is highly dynamic, so the perfect solution would be to rewrite only 1 = ALL ( ... ), since changing top-level select can cause problems when additional conditions are added to top-level where.

Answer 1

It can be translated to pick Entities where no related entities with unmatched condition exist.

This can be accomplished by:

SELECT
    Id
FROM
    dbo.Entity
WHERE
    NOT EXISTS (
//as far as I have an element which do not match the condition, skip this entity
        SELECT TOP 1 1
        FROM
            dbo.Related
            INNER JOIN dbo.Entity AS TargetEntity ON
                TargetEntity.Id = Related.TargetId
        WHERE
            Related.SourceId = Entity.Id AND
            CASE
                WHEN {Condition} THEN 1
                ELSE 0
            END = 0
    )

EDIT: depending on condition, you can write something like:
WHERE Related.SourceId = Entity.Id AND NOT {Condition} if it doesn't change too much the complexity of the query.

Answer 2

Instead of using all, change your query to compare the result of the subquery directly:

select Id
  from dbo.Entity
  where 1 = (
      select 
        case 
          when ... then 1
          else 0
        end
        from ...
        where ...
      )

Answer 3

Couldn't you use a min to achieve this?

EG:

SELECT
    Id
FROM
    dbo.Entity
WHERE
    1 = (
        SELECT
            MIN(CASE
                WHEN {Condition} THEN 1
                ELSE 0
            END)
        FROM
            dbo.Related
            INNER JOIN dbo.Entity AS TargetEntity ON
                TargetEntity.Id = Related.TargetId
        WHERE
            Related.SourceId = Entity.Id
    )

The min should return null if there's no clauses, 1 if they're all 1 and 0 if there's any 0's, and comparing to 1 should only be true for 1.

Answer 4

Probably this will work: WHERE NOT 0 = ANY(...)

Answer 5

If I read the query correctly, it can be simplified to something like:

SELECT       e.Id
FROM         dbo.Entity e
INNER JOIN   dbo.Related r ON r.SourceId = e.Id
INNER JOIN   dbo.Entity te ON te.Id = r.TargetId
WHERE        <extra where stuff>
GROUP BY     e.Id
HAVING       SUM(CASE WHEN {Condition} THEN 1 ELSE 0 END) = COUNT(*)

This says the Condition must be true for all rows. It filters the "empty" set case away with the INNER JOINs.