What does Nested Loops join operator has to do with a Scalar?

All we need is an easy explanation of the problem, so here it is.

As far as I understand the docs, the Nested Loops operator is a join operator, i.e., it requires two tables as input.

If that’s correct, then why the Nested Loops operator is used with a Scalar input?

For example, take the following query (from Paul White’s website) and its execution plan.
You can see that the inputs for the Nested Loops operator are (1) a Scalar, and (2) a table (the result of the Index Seek).

I know that a scalar can’t be joined with a table, then what that actually means? What’s actually being joined?

USE AdventureWorks2019;
DECLARE @Like nvarchar(50) = N'D%';
SELECT p.[Name]
FROM   Production.Product AS p
WHERE  p.[Name] LIKE @Like;

What does Nested Loops join operator has to do with a Scalar?

BTW, I presume that it’s a pretty basic question, but I couldn’t find a good resource that goes through such basics methodologically, so a recommendation would be much appreciated.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This question is based on a misunderstanding of what these operators do:

Constant Scan

This operator returns a table of constants. This may be one or multiple rows, and may be anywhere from zero to multiple columns.

In this particular instance, the constant scan is one row and zero columns. It is only used to generate a driving row for the Compute Scalar, a bit like (VALUES()) if that were possible.

Compute Scalar

This operator adds one or more columns to the row passing through it, the calculation of which may or may not be dependent on other columns. It does not add rows, it works on each row at a time.

Note that in may instances the calculation is not done directly by this operator but rather by later plan operators, and it merely functions as a placeholder. This is why it often does not have "Actual Number of Rows"

In this instance, it is used by the compiler to calculate the beginning and end points for the LIKE predicate, and internal information about the predicate.

Nested Loops

The single row is then fed through the join and the calculations are passed through as correlated parameters (outer references).

Effectively, the query becomes something like this:

    @Like nvarchar(50) = N'D%';

    (LikeRangeStart(@Like), LikeRangeEnd(@Like), LikeRangeInfo(@Like))
) v(Expr1005, Expr1006, Expr1007)
JOIN Production.Product AS P
  ON p.Name > Expr1005 AND p.Name < Expr1006
  AND LikeFunction(p.[Name], @Like, Expr1007) > 0;

Method 2

A scalar input could also be expressed as a single row, single column table.

That is exactly what the Constant Scan and Compute Scalar operators do – turn it into a table so that it can then be joined on:

The Constant Scan operator introduces one or more constant rows into a query. A Compute Scalar operator is often used after a Constant Scan to add columns to a row produced by the Constant Scan operator.

The Compute Scalar operator evaluates an expression to produce a computed scalar value. This may then be returned to the user, referenced elsewhere in the query, or both. An example of both is in a filter predicate or join predicate. Compute Scalar is a logical and physical operator.

And since Nested Loops are used when one of the two tables being joined is very small (~10 rows or less) you’re very likely to get a Nested Loops join when using scalar inputs. More to the point, only nested loops join supports correlated parameters (outer references), also known as an apply.

Some more in depth explanations:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply