title: Full Join on two tables with criteria on both
body: <p>I have two tables with a matching key. Some records exist on both tables, others only exist on one or the other. What I cannot for the life of me figure out is how to add criteria to both tables when doing a full outer join.</p>
  <p>Table A (EDIT: with criteria)</p>
  <pre><code>+---------------------------+
  | ID, AREA, STATUS, YEAR     |
  +----------------------------+
  | ID1, AA,  YES,  1980       |
  | ID2, BB,  NO,   1990       |
  | ID3, CC,  YES,  1950       |
  | ID4, DD,  NO,   1900       |
  </code></pre>
  <p>Table B (EDIT: with criteria)</p>
  <pre><code>+--------------------------+
  | ID, ZONE, CODE, TIME     |
  +--------------------------+
  | ID1, FF,  1,  12:00      |
  | ID5, HH,  1,  11:11      |
  | ID6, II,  1,  13:00      |
  </code></pre>
  <p>Desired Join</p>
  <pre><code> +------------------------------------------+
   | ID, AREA, STATUS, YEAR, ZONE, CODE, TIME |
   +------------------------------------------+
   | ID1, AA,  YES,  1980,    FF,  1,   2000  |
   | ID2, BB,  NO,   1990,    n/a, n/a,  n/a  |
   | ID3, CC,  YES,  1950     n/a, n/a,  n/a  |
   | ID4, DD,  NO,   1900     n/a, n/a,  n/a  |
   | ID5, n/a, n/a,  n/a,     HH,  1,   2001  |
   | ID6, n/a, n/a,   n/a,    II,  1,   2000  |
  </code></pre>
  <p>So far I have the following code. It is returning everything for me besides ID5 and ID6, <strong>for some reason it won't bring rows in the join where col in Table A are null (n/a).</strong></p>
  <pre><code>SELECT A.ID, A.AREA, A.STATUS, A.YEAR, B.ID, B.ZONE, B.CODE, B.TIME
  FROM TableA A 
  FULL JOIN TableB B
  ON A.ID = B.ID AND /* criteria for B */ (B.ZONE &gt; XX AND B.CODE &gt;= XX)
  WHERE /* criteria for A */ A.YEAR &gt; XXXX
  </code></pre>
  <p><strong>Am I putting my criteria in the incorrect locations? How can I do a Full Join but also apply criteria to both TableA and TableB ?</strong></p>