December 7, 2002 at 10:37 pm
I’m using Microsoft SQL Server 2000 - 8.00.534 (Build 2195: Service Pack 2).
In Northwind database context I executed the following script:
CREATE TABLE #Products (productId int UNIQUE NOT NULL)
INSERT #Products
SELECT 11 UNION ALL SELECT 12
--first query
SELECT OD.OrderID,OD.ProductID , P.productId
FROM dbo.[Order Details] OD
FULL OUTER JOIN #Products P
ON P.productId = OD.ProductID
WHERE OD.OrderID = 10248
--second query (using derived table to filter out a specific order Id)
SELECT OD.OrderID,OD.ProductID , P.productId
FROM (SELECT * FROM dbo.[Order Details] WHERE OrderID = 10248 ) OD
FULL OUTER JOIN #Products P
ON P.productId = OD.ProductID
DROP TABLE #Products
The orderId= 10248 does contains just productId = 11 and not productId = 12
The 2 select queries return different results:
First one returns all products from orderId =10248 and completely ignores the fact that productId = 12 should appear in result set.
The result is similar if we would ‘ve replaced FULL OUTER JOIN operation with an LEFT OUTER JOIN.
The execution plan for this query shows that a Nested Loop/Left outer Join physical operator was used.
The second query returns the correct result (productId = 12 is included in resultset)
The execution plan for this query shows that a Merge Join/Full outer Join physical operator was used.
Are you experiencing the same inconsistency? Or I’m completely wrong and my SQL version its way outdated.
December 8, 2002 at 6:35 am
It is not an inconsistency as the queries specifically ask for different results.
Think about it:
quote:
The orderId= 10248 does contains just productId = 11 and not productId = 12
In the first one you are joing the tables for every row in the first table joint to one in the second table. Then after that the WHERE condition is processed. So Only records that are of OrderID 10248 will be left when done.
In the second you are doing differently. You are first saying give me all the records from Order Details WHERE orderid = 10248 and generate that as the output. Then you join it to the other table. When done 12 has the value NULL, NULL in front of it. Thus that would not have meet the criteria of WHERE OD.OrderID = 10248 in the previous query.
You in fact wrote 2 entirely different queries.
Keep in mind order of operation here
The second was before the join so the intial results where all records with OrderID 10248 then join.
The first was join then remove all records where orderid did not equal 10248.
December 8, 2002 at 3:35 pm
I very much appreciate your answer. You’re totally right.
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply