pokerplayer23 pokerplayer23 - 4 months ago 18
SQL Question

SQL Server - Return all records from LEFT table and only non matching records from right table

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

Answer

So, you need every row from table1 plus the rows from table2 that don't match with table1?:

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);