How to union a data set with a specific rule

Question

I have two almost identical data sets that I am trying to union but I only want to union them if there isn't a date in File 1 for it already.

Data Set 1

File	Date	Type
1	1/1/2020	a
1	1/2/2020	b
1	1/3/2020	c

Data Set 2

File	Date	Type
2	1/1/2020	a
2	1/2/2020	b
2	1/3/2020	c
2	1/4/2020	d

Ideal output

File	Date	Type
1	1/1/2020	a
1	1/2/2020	b
1	1/3/2020	c
2	1/4/2020	d

If both data sets have the same dates, then I only want to use data from File #1

SELECT FILE, DATE, TYPE FROM TABLE A
UNION
SELECT FILE, DATE, TYPE FROM TABLE B

[set up] https://www.db-fiddle.com/f/whfZGwnAJcAYaQii6avF6k/1

Answer 1

You can use union all with not exists as follows:

SELECT DISTINCT FILE, DATE, TYPE FROM TABLEA
UNION
SELECT DISTINCT FILE, DATE, TYPE FROM TABLEB B
WHERE NOT EXISTS 
(SELECT 1 FROM TABLEA A
  WHERE A.DATE = B.DATE
    AND A.TYPE = B.TYPE) 

Note: I have used distinct to make the logic similar to union as it removes the duplicate. You can remove the distinct from your query if you do not have duplicates in your individual tables.