Sql-server – Why does the execution plan (sometimes) include a left join

execution-planjoin;sql serversql server 2014

I found a similar question and am aware that in order for a query of the form

SELECT COUNT(1)
FROM foo f
LEFT OUTER JOIN bar b ON f.Value = b.Value AND f.Value = b.Value2

to perform without touching bar, there needs to be a unique index on the two columns in question.

And indeed, this works so far, the tables being defined as:

CREATE TABLE [dbo].[Foo](
    [Value] [varchar](255) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Bar](
    [Value] [varchar](1024) NULL,
    [Value2] [varchar](1024) NULL
)

CREATE UNIQUE CLUSTERED INDEX [IX] ON [dbo].[Bar]
(
    [Value] ASC,
    [Value2] ASC
)

The query plan doesn't touch bar, great.

In my quest of why this doesn't work in my real-life query of some app I write, I reduced the query there to the following test that is simple and also has the query planner fails to leave its hands off the bar table:

WITH numbers AS (
    SELECT 1 AS i
    UNION ALL SELECT i + 1
    FROM numbers
    WHERE i < 10
)
SELECT COUNT(1)
FROM numbers n
LEFT OUTER JOIN bar b ON n.i = b.Value AND n.i = b.Value2
;

This does indeed touch bar. Hmmm.

Any ideas? Why does the query planner think this is any different than the other query?

(My actual problem doesn't use this recursive table expression, it's actually just like the demo case, a simple join on two columns – and I can't fathom why it wouldn't leave the joined table alone in a count scenario.)

Best Answer

I suspect the difference is the implicit conversion, which can get in the way. As an example, Bar isn't touched if it has a compatible data type:

CREATE TABLE [dbo].[Bar]
(
    [Value] int NULL,
    [Value2] int NULL
);
Related Question