FULL OUTER JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

FULL OUTER JOIN

Author

Topic

damcalcan
Starting Member

11 Posts

Posted - 2002-12-08 : 00:09:12

I’m using Microsoft SQL Server 2000 - 8.00.534 (Build 2195: Service Pack 2).

In Northwind database context I executed the following script:

/************ begin 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 orderId)

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

/************ end script ******************/

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.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-08 : 01:54:00

Same results here.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-08 : 14:59:08

I think the results you are getting are completely normal and expected.

Isn't it true that if you do any filtering on a table involved in a FULL OUTER JOIN, you are in effect removing the OUTER JOIN part?

It's the same as filtering on the outer table of a LEFT OUTER JOIN.

That is, with:

SELECT A.*
FROM
A
LEFT OUTER JOIN
B
ON A.ID = B.ID
WHERE (some criteria for table B)

the WHERE negates the outer join and turns the above query into an INNER JOIN. This is a key thing to understand about SQL that many don't grasp.

The correct way to handle this is:

SELECT A.*
FROM
A
LEFT OUTER JOIN
(SELECT * FROM B WHERE (somecriteria)) B
ON A.ID = B.ID

which you have done in your second query, and that is why the second one works correctly.

So, for a FULL OUTER JOIN, if you put criteria on one of the tables, you aren't truly performing a full outer join.

That is,

SELECT A.*
FROM
A
FULL OUTER JOIN
B
ON A.ID = B.ID
WHERE (some criteria for table A)

is the same as :

SELECT A.*
FROM
A
LEFT OUTER JOIN
B
ON A.ID = B.ID
WHERE (some criteria for table A)

unless you say

SELECT A.*
FROM
(SELECT * FROM A WHERE (criteria) ) A
FULL OUTER JOIN
B
ON A.ID = B.ID

I usually avoid FULL OUTER JOINs; instead I do a CROSS JOIN of all possible values, encapsulate that into a subquery, and from there do LEFT OUTER JOIN's to the data and filter as needed.

- Jeff

Edited by - jsmith8858 on 12/08/2002 15:31:31

damcalcan
Starting Member

11 Posts

Posted - 2002-12-08 : 17:02:30

Thank you for your response mr. Jeff. It does make sense..

Regarding your analogy with LEFT JOIN operator and a WHERE clause on the outer table.
We can get a correct result if we add WHERE predicate into the join condition.
So we’ll have the following query which will filter first the outer table.

SELECT OD.OrderID,OD.ProductID , P.productId
FROM dbo.[Order Details] OD
LEFT OUTER JOIN #Products P
ON P.productId = OD.ProductID
AND P.productId = 11

Now my question is :

Do you think is reasonable to expect that the FULL OUTER JOIN operator will work as expected if we were to add the WHERE predicate into join condition.

So to speak shouldn’t the following query work fine?

SELECT OD.OrderID,OD.ProductID , P.productId
FROM dbo.[Order Details] OD
FULL OUTER JOIN #Products P
ON P.productId = OD.ProductID
AND OD.OrderID = 10248

Thank you and apologise if my questions are silly…

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-08 : 18:39:19

Good point -- I always forget about adding the condition as part of the join. That works equally well. I assume the optimiser uses it the same way? Haven't tested it...

The FULL OUTER JOIN example you give at the end, with the criteria embedded in the JOIN, should work then, I would think. Haven't tried it. I just think FULL OUTER JOIN's are kind off odd and I don't really like them.

Why? Because a FULL OUTER JOIN, if you didn't don't have matching data in both tables, will return records with NULL values in BOTH table's primary keys usually!

I just think this resultset is very odd:


A.Company    B.Company    Amount
XYZ          XYZ          $100
(Null)       ABC          $50
DEF          (Null)       $75
GHI          GHI          $600

I mean, how do you use the above result? You need to use an ISNULL() or something to combine
the two "Company" fields, and then you might have trouble using indexes with further JOINs or sorts.

What I would do in the above query is either a UNION of tables A and B to "merge" the results, or a LEFT OUTER JOIN from a table of all possible companies to both tables A and B.

I just don't like FULL OUTER JOINs! I see where they can be useful sometimes, but I just don't really use them. A query that returns NULLs in key fields just doesn't make sense to me.

Do others use them often?

- Jeff

Edited by - jsmith8858 on 12/08/2002 18:40:13

damcalcan
Starting Member

11 Posts

Posted - 2002-12-08 : 20:14:04

So the second FULL OUTER JOIN query doesn’t work as we would expected… The execution plan shows that the filter isn’t applied before the physical MERGE JOIN operator so the result set is very strange and I cannot interpret it.

I’m using FULL OUTER JOIN for a relational division operation. I’m having a set of products and I’m trying to get the orders that have the exact set of products. (this problem was posted a while ago)
Actually it works fine if I’m using the derived query with FULL OUTER JOIN but I was thinking that I could use it without derived queries too….

thanks for your response…

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources