stong, the reason is the order in which the parts of SQL is processed. The order is:
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
ORDER BY clause
SELECT clause
NOTE: I am doing this from memory. I am fairly sure this is the correct order by may have an item out of order.
The From clause if processed first. This is:
Code:
from well_def a full outer join
plate_view b
on a.well_id = b.well_id
After this processes you will have an intermediate result set as you describe. Here is an example of the FULL OUTER JOIIN where the B table has rows that are not in the A table, the A table as values not in the B table and rows that are in both the A and B table:
Code:
B.PID B.CONTENT B.WELLID A.WELLID A.WELL
1 VALUE A A VALUE
1 VALUE B B VALUE
1 VALUE C D NULL
2 VALUE E E VALUE
NULL NULL NULL F VALUE
3 VALUE G NULL NULL
Next the WHERE clause is applied. This is WHERE B.PID = 1
Code:
B.PID B.CONTENT B.WELLID A.WELLID A.WELL
1 VALUE A A VALUE
1 VALUE B B VALUE
1 VALUE C D NULL
As you can see this is NOT the result you are describing.
What you need to do is either filter on B.PID =1 in the FROM clause or filter on the View before it is Joined.
In the From CLAUSE:
Code:
SELECT A.WELL, B.CONTENT, B.WELL
FROM WELL_DEF A
FULL OUTER JOIN
PLATE_VIEW B
ON A.WELL_ID = B.WELL_ID
AND B.PID = 1
On the View:
Code:
SELECT A.WELL, B.CONTENT, B.WELL
FROM WELL_DEF A
FULL OUTER JOIN
(SELECT CONTENT, WELL, WELL_ID
FROM PLATE_VIEW
WHERE B.PID = 1
) AS B
ON A.WELL_ID = B.WELL_ID