quote:

---

What I understand is that, we have to put all the values from the left table to the table variable, first. Then we have to UPDATE the table variable with the right table values. Then select from the table variable.

---

-- Base Tables 
IF OBJECT_ID('orders') IS NOT NULL DROP TABLE orders
IF OBJECT_ID('customers') IS NOT NULL DROP TABLE customers

CREATE TABLE customers (
	[customerID] INT IDENTITY(1,1)
	, [firstname] VARCHAR(255)
	, [surname] VARCHAR(255)

	PRIMARY KEY NONCLUSTERED ([customerID])
	)

CREATE TABLE orders (
	[orderID] INT IDENTITY(1,1)
	, [customerID] INT FOREIGN KEY REFERENCES dbo.customers ([customerID])
	, [orderDate] DATETIME
	, [orderAmount] MONEY

	PRIMARY KEY NONCLUSTERED ([orderId])
	)

CREATE CLUSTERED INDEX IX_customers_surname_firstname ON customers ([surname], [firstname])
CREATE CLUSTERED INDEX IX_orders_date_amount_customerID ON orders ([orderDate], [orderAmount], [customerID])

-- Data (262144 Customer Rows)
INSERT customers ([firstname], [surname])
SELECT
	LEFT(CAST(NEWID() AS VARCHAR(50)), 8)
	, LEFT(CAST(NEWID() AS VARCHAR(50)), 8)
FROM
	( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS a
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS b
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS c
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS d
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS e
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS f
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS g
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS h
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS i

-- 1048576 Order Rows
INSERT orders ([customerID], [orderDate], [orderAmount])
SELECT
	ABS(CAST(CAST(NEWID() AS VARBINARY(2)) AS INT)) + 1 -- There will be a customer in this range
	, CAST(ABS(CAST(CAST(NEWID() AS VARBINARY(2)) AS INT)) AS DATETIME) -- valid date in this range
	, CAST(CAST(NEWID() AS VARBINARY(3)) AS MONEY) -- valid money in this range
FROM
	( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS a
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS b
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS c
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS d
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS e
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS f
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS g
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS h
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS i
	CROSS JOIN ( SELECT 1 AS [a] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3] UNION SELECT 4 AS [4]) AS j


-- Everything (Non Clustered Index scan galore!)
SELECT
	*
FROM
	customers c
	LEFT JOIN orders o ON o.[customerID] = c.[customerID]

-- Employees surname greater than C (Clustered index seek on customers -- scan on the orders table)
SELECT
	*
FROM
	customers c
	LEFT JOIN orders o ON o.[customerID] = c.[customerID]
WHERE
	c.[surname] > 'C'


-- Employees surname greater than C wand order dates after 2010
-- Index seeks on both
SELECT
	*
FROM
	customers c
	LEFT JOIN orders o ON
		o.[customerID] = c.[customerID]
		AND o.[orderDate] >= '20100101'
WHERE
	c.[surname] > 'C'