How can I exclude rows from two tables whose values are encompassed by each other? I have the following two tables, Table A and Table B.
What I'm trying to do is return a result set where:

If the start position (only) in Table B is between the start and end position of any row in Table A, then I don't want that row from Table B to be in the result set.

If any of the start and end positions in Table A are between any of the start and end positions of any row in Table B, then I don't want that row from Table A.


Here is Table A:




RID
StartPos
EndPos




7
45
77


7
118
130


7
197
212


7
218
235




Here is Table B:




RID
StartPos
EndPos




7
83
87


7
121
132


7
175
179


7
183
191


7
195
214


7
221
237




Here is my desired result set. You can see that 197 and 212 from Table A fall between the row in Table B that has 195 and 214, so that row from Table A isn't in the result set. You can also see that the start position 221 in Table B is between a range in Table A (218, 235), so that row from Table B isn't in the result set either.




RID
StartPos
EndPos




7
45
77


7
83
87


7
118
130


7
175
179


7
183
191


7
195
214


7
218
235




In sum, I want to exclude where:

TableA start pos and end pos are between any of table B's start pos and end pos.
TableB start pos is between any of table A's start pos and end pos.

Also, there are more than one RIDs in the table...
I thought (and still think) I could get this done using a FULL join (or maybe using APPLY), but I'm getting a little stuck, which is why I'm asking you all for some help on it!
Here is the T-SQL to generate the two tables:
CREATE TABLE #TableA(
    RID int NULL,
    StartPos int NULL,
    EndPos int NULL
)

INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 45, 77)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 118, 130)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 197, 212)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 218, 235)


CREATE TABLE #TableB(
    RID int NULL,
    StartPos int NULL,
    EndPos int NULL
)

INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 83, 87)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 121, 132)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 175, 179)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 183, 191)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 195, 214)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 221, 237)


SELECT *
FROM #TableA

SELECT *
FROM #TableB
According to you question, I think this is what you want:
select * from #TableB b 
where not exists (
    select 1 from #TableA a 
    where b.StartPos between a.StartPos and a.EndPos
    and A.RID = b.RID
)
union
select * from #TableA a 
where not exists (
    select 1 from  #TableB b
    where a.StartPos between b.StartPos and b.EndPos
    and a.EndPos between b.StartPos and b.EndPos
    and A.RID = b.RID
)
;

Fiddle