quote:

---

Originally posted by robvolk

Probably figured something out by now.

---

quote:

---

Originally posted by mcp111

Can you give an example of a query using the table datatype to eliminate left joins?

---

CREATE TABLE #MainTable ( 
        CustomerID INT  PRIMARY KEY, 
        FirstName VARCHAR(100) 
) 


DECLARE @Count INT
SET @Count = 0

DECLARE @Iterator INT
SET @Iterator = 0

WHILE @Count <8000
BEGIN

	INSERT INTO #MainTable SELECT @Count, 'Cust'+CONVERT(VARCHAR(10),@Count)
	SET @Count = @Count+1

END
  
CREATE TABLE  #RightTable 
( 
        OrderID INT  PRIMARY KEY, 
        CustomerID INT, 
        Product VARCHAR(100) 
) 
 
CREATE INDEX  [IDX_CustomerID] ON #RightTable (CustomerID)

WHILE @Iterator <400000
BEGIN

		IF @Iterator % 2 = 0
		BEGIN

			INSERT INTO #RightTable  SELECT @Iterator,2, 'Prod'+CONVERT(VARCHAR(10),@Iterator)
		END
		ELSE
		BEGIN
			INSERT INTO #RightTable  SELECT @Iterator,1, 'Prod'+CONVERT(VARCHAR(10),@Iterator)
		END

SET @Iterator = @Iterator+1

END


-- Approach 1: Using LEFT JOIN
SELECT  mt.CustomerID,mt.FirstName,COUNT(rt.Product) [CountResult]
FROM    #MainTable mt 
		LEFT JOIN #RightTable rt ON mt.CustomerID = rt.CustomerID 
GROUP BY mt.CustomerID,mt.FirstName


---------------------------



-- Approach 2: Using Table variable Update
DECLARE @WorkingTableVariable TABLE
( 
        CustomerID INT, 
        FirstName VARCHAR(100),
		ProductCount INT  
) 
 
INSERT INTO @WorkingTableVariable (CustomerID,FirstName)
SELECT  CustomerID, FirstName FROM #MainTable 
 
UPDATE  @WorkingTableVariable 
SET     ProductCount = [Count]
FROM    @WorkingTableVariable wt 
		INNER JOIN
			(SELECT CustomerID,COUNT(rt.Product) AS [Count]
			 FROM #RightTable rt
			 GROUP BY  CustomerID) IV ON wt.CustomerID = IV.CustomerID 
 
SELECT CustomerID,FirstName, ISNULL(ProductCount,0) [CountResult] FROM    @WorkingTableVariable 
ORDER BY CustomerID

--------

DROP TABLE  #MainTable
DROP TABLE  #RightTable