# Miscellaneous > Structured Query Language (SQL) >  help with joining table and view

## stong

Hi 

I  hava a table (well)  and a view  (plate_view), for plate_view with pid = 1, there are supposed to be only 20 records, each of reach correponds to a record from the "well" table based on the well id.  The well table has 96 different records with different well id.

When I do a full outer join like following, why is it that I can only see 20 reocrds ? Shouldn't I expect to see 20 records  and 76 records with null values appear for the 76 well records that cannot be found from the view of plate_view ?

select a.well, b. b.content, b.well, 
from well_def a full outer join 
plate_view  b 
on a.well_id = b.well_id 
where b.pid = 1 ;

----------


## SDas

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:


```
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:


```
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


```
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:


```
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:


```
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
```

----------


## stong

thanks ,SDas, 

You are right on. I use your suggestion with filtering on the View before it is Joined with my "well" table and the query give me back whas I was looking for (all 96 records with 76 empty values from the View)

I should really remind myself to be more aware of the order  in which SQL directives get executed .

----------

