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

Question

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;

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.

Answer 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:

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

SELECT 
    P.[Name]
FROM (VALUES
    (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;

Answer 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:

• SQL Server's Constant Scan on Stack Overflow
• Constant Scan in Execution Plans by Grant Fritchey