SQL INNER JOIN vs Where Exists Performance Consideration

Question

Suppose, I have a list of string which is sent from client, in this case regions. And I have a table named Compliance Regions. I want to search for the rows which has a name property and region item should be exist on the row.

In the LINQ, I can do it in two different ways. Like below, and they produce two different SQL Queries. My question is which should I choose? Which query has the better performance?

 List<string> regions = new List<string>() { "Canada", "EN 50530" };

           var cregions = from c in complianceRegions
                           from r in regions
                           where c.Name.Equals(r)
                           select c;

            var cregions2 = from c in complianceRegions
                            where regions.Any(x => x == c.Name)
                            select c;

The generated sql is showed below.

      -- cregions
   SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Description] AS [Description]
    FROM  [Administration].[ComplianceRegions] AS [Extent1]
    INNER JOIN  (SELECT 
        N'Canada' AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
    UNION ALL
        SELECT 
        N'EN 50530' AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1] ON [Extent1].[Name] = [UnionAll1].[C1]  

And

--cregions2
    SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Description] AS [Description]
    FROM [Administration].[ComplianceRegions] AS [Extent1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM  (SELECT 
            N'Canada' AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
        UNION ALL
            SELECT 
            N'EN 50530' AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
        WHERE [UnionAll1].[C1] = [Extent1].[Name]
    )

Added Execution plan as requested.

Answer 1

Given the two alternatives, the second is probably better, because there is no function call on the column name.

However, the two are not the same. The first does a partial match and the second does an exact match. You should really do the one that does what you intend. Performance is secondary to accuracy.

Both are painful look at. Casting the output of CHARINDEX() to an index? Can we say "redundant"?

They beg the question why the query isn't simply:

select . . .
from [Administration].[ComplianceRegions] AS [Extent1]
where Extent1.Name in (N'Canada', N'EN 50530');

This is the simplest and best performing version of this logic.