I have run into an issue where an Oracle ANSI full outer join with filter is not returning the results as I am expecting them. I have created a quick example to explain what I am doing and seeing...
Table 1 - MUPPET
ID NAME
1 Kermit the Frog
2 Fozzie Bear
3 Mrs. Piggy
4 Beaker
5 Animal
6 Swedish Chef
Table 2 - PHONE
ID MUPPET_ID PHONE VALID
1 1 1111111111 Y
2 1 2222222222 N
3 2 3333333333 Y
4 4 4444444444 Y
5 5 5555555555 Y
6 6 6666666666 Y
7 6 7777777777 N
8 8 8888888888 Y
From these tables I want to select all muppets and all valid phone numbers. I want all the muppets whether or not they have a phone number and I want to select all valid phone numbers whether or not they are associated to a muppet. This is the query that I would expect to work...
SELECT m.id muppet_id,
m.name,
p.id phone_id,
p.phone,
p.valid
FROM muppet m
FULL OUTER JOIN
phone p
ON (M.ID = P.MUPPET_ID AND P.VALID = 'Y')
But here are the results which include non valid phones even though I specified no non valid phones in the outer join filter
MUPPET_ID NAME PHONE_ID PHONE VALID
1 Kermit the Frog 1 1111111111 Y
2 2222222222 N
2 Fozzie Bear 3 3333333333 Y
4 Beaker 4 4444444444 Y
5 Animal 5 5555555555 Y
6 Swedish Chef 6 6666666666 Y
7 7777777777 N
8 8888888888 Y
3 Mrs. Piggy
I was able to finally get the results I was looking for using a sub-select in the left part of the full outer join
SELECT m.id muppet_id,
m.name,
p.id phone_id,
p.phone,
p.valid
FROM muppet m
FULL OUTER JOIN
(SELECT id,
phone,
valid,
muppet_id
FROM phone
WHERE valid = 'Y') p
ON (M.ID = P.MUPPET_ID)
...and the results...
MUPPET_ID NAME PHONE_ID PHONE VALID
1 Kermit the Frog 1 1111111111 Y
2 Fozzie Bear 3 3333333333 Y
4 Beaker 4 4444444444 Y
5 Animal 5 5555555555 Y
6 Swedish Chef 6 6666666666 Y
8 8888888888 Y
3 Mrs. Piggy
But I don't understand why I would have to query this way. Can someone please help explain to me why my initial outer join query with the filter does not work as expected?
EDIT:
Even more interesting. When I run this query I get 6 records as expected
select valid from (
SELECT m.id muppet_id,
m.name,
p.id phone_id,
p.phone,
p.valid
FROM muppet m
FULL OUTER JOIN
phone p
ON (M.ID = P.MUPPET_ID AND P.VALID = 'Y')
) where valid = 'Y'
BUT when I run this I get no records returned
select valid from (
SELECT m.id muppet_id,
m.name,
p.id phone_id,
p.phone,
p.valid
FROM muppet m
FULL OUTER JOIN
phone p
ON (M.ID = P.MUPPET_ID AND P.VALID = 'Y')
) where valid <> 'Y'
Maybe this is an issue with the Oracle optimizer or driver??
As others have explained in comments, this is because of where you are putting the filter logic. So your original query will return all muppets and all phone numbers and show them as joined when the IDs match AND valid = 'Y'. So this is why you are seeing all phone numbers, but only matches for valid ones.
You can either do the approach you already figured out, or move the "valid" logic into the WHERE clause:
SELECT m.id muppet_id,
m.name,
p.id phone_id,
p.phone,
p.valid
FROM muppet m
FULL OUTER JOIN
phone p
ON (M.ID = P.MUPPET_ID)
WHERE
P.VALID = 'Y' or P.MUPPET_ID is null;
Now the where clause is discarding rows where VALID <> 'Y' or where there was no match for the phone table. If you made the where clause only "P.VALID = 'Y'", then you would be effectively turning this into a right outer join.
That said, I would actually pick the second version you listed, as this will allow you to use an index on VALID = 'Y' if that index exists, where the above version will likely not use the index due to the OR in the where clause.