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.