Full Join on two tables with criteria on both

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.

Table A (EDIT: with criteria)

+---------------------------+
| ID, AREA, STATUS, YEAR     |
+----------------------------+
| ID1, AA,  YES,  1980       |
| ID2, BB,  NO,   1990       |
| ID3, CC,  YES,  1950       |
| ID4, DD,  NO,   1900       |

Table B (EDIT: with criteria)

+--------------------------+
| ID, ZONE, CODE, TIME     |
+--------------------------+
| ID1, FF,  1,  12:00      |
| ID5, HH,  1,  11:11      |
| ID6, II,  1,  13:00      |

Desired Join

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

So far I have the following code. It is returning everything for me besides ID5 and ID6, for some reason it won’t bring rows in the join where col in Table A are null (n/a).

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 > XX AND B.CODE >= XX)
WHERE /* criteria for A */ A.YEAR > XXXX

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 ?

Answer

To limit the scope of the full join I suggest that you use subqueries to implement the wanted conditions using where clauses, such as this:

CREATE TABLE A(
   ID     VARCHAR(8) NOT NULL
  ,AREA   VARCHAR(3) NOT NULL
  ,STATUS VARCHAR(5) NOT NULL
  ,YEAR   INTEGER  NOT NULL
);
INSERT INTO A(ID,AREA,STATUS,YEAR) VALUES ('ID1','AA','YES',1980);
INSERT INTO A(ID,AREA,STATUS,YEAR) VALUES ('ID2','BB','NO',1990);
INSERT INTO A(ID,AREA,STATUS,YEAR) VALUES ('ID3','CC','YES',1950);
INSERT INTO A(ID,AREA,STATUS,YEAR) VALUES ('ID4','DD','NO',1900);

CREATE TABLE B(
   ID   VARCHAR(8) NOT NULL
  ,ZONE VARCHAR(3) NOT NULL
  ,CODE BIT  NOT NULL
  ,TIME VARCHAR(17) NOT NULL
);
INSERT INTO B(ID,ZONE,CODE,TIME) VALUES ('ID1','FF',1,'12:00');
INSERT INTO B(ID,ZONE,CODE,TIME) VALUES ('ID5','HH',1,'11:11');
INSERT INTO B(ID,ZONE,CODE,TIME) VALUES ('ID6','II',1,'13:00');

SELECT A.ID, A.AREA, A.STATUS, A.YEAR, B.ID, B.ZONE, B.CODE, B.TIME
FROM A 
FULL JOIN B
ON A.ID = B.ID
GO

ID   | AREA | STATUS | YEAR | ID   | ZONE | CODE | TIME 
:--- | :--- | :----- | ---: | :--- | :--- | :--- | :----
ID1  | AA   | YES    | 1980 | ID1  | FF   | True | 12:00
ID2  | BB   | NO     | 1990 | null | null | null | null 
ID3  | CC   | YES    | 1950 | null | null | null | null 
ID4  | DD   | NO     | 1900 | null | null | null | null 
null | null | null   | null | ID5  | HH   | True | 11:11
null | null | null   | null | ID6  | II   | True | 13:00

INSERT INTO A(ID,AREA,STATUS,YEAR) VALUES ('ID10','AA','YES',1940);

INSERT INTO B(ID,ZONE,CODE,TIME) VALUES ('ID6','II',-7,'01:30');

SELECT A.ID, A.AREA, A.STATUS, A.YEAR, B.ID, B.ZONE, B.CODE, B.TIME
FROM (
      select * from A where year > 1940
     ) AS A 
FULL JOIN (
     select * from b where code > 0 or time > '12:00'
     ) B
ON A.ID = B.ID

ID   | AREA | STATUS | YEAR | ID   | ZONE | CODE | TIME 
:--- | :--- | :----- | ---: | :--- | :--- | :--- | :----
ID1  | AA   | YES    | 1980 | ID1  | FF   | True | 12:00
null | null | null   | null | ID5  | HH   | True | 11:11
null | null | null   | null | ID6  | II   | True | 13:00
null | null | null   | null | ID6  | II   | True | 01:30
ID2  | BB   | NO     | 1990 | null | null | null | null 
ID3  | CC   | YES    | 1950 | null | null | null | null 

db<>fiddle here