I have 2 tables with the same structure (field names). Table1 and Table2.
I need to return all records from Table1 and only records from Table2 that do not match/join to a record in Table1.
Table2 has more records than Table1.
I am joining the 2 tables on 3 fields.
So basically I want all records from table1 returned and only records that don't have a match (joining on the 3 fields) to table1 from table2 returned.
Put another way, Table1 records take precedence over table2 records in my final result output when the records exist in both tables (same value for the 3 fields)
I started writing something like the below but I don't think it will work. Should I use a left outer join instead?
Select * from table1 t1
left join table2 t2 on t1.id = t2.id and t1.date = t2.date and t1.custid= t2.custid
where t2.id is null or t2.date is null or t2.custid is null
So, you need every row from
table1 plus the rows from
table2 that don't match with
SELECT * FROM table1 UNION ALL SELECT * FROM table2 t2 WHERE NOT EXISTS(SELECT * FROM table1 WHERE id = t2.id AND date = t2.date AND custid = t2.custid);