Member Avatar for js8765

Hi,

I was wondering what is the "cleanest" way of combining two datasets by taking all the values from one data set and only the values from a second data set that have an index that does not appear in the first.

Both data sets have the same columns.

Even if there is an entry in the second data set with the same index as the first but different values, it should not be included.

Thanks in advance for any help!

  • 2 Contributors
  • 4 Replies
  • 802 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by js8765

Dani AI

Generated

A concise, easy-to-read pattern is to return all rows from the first dataset, then append only those rows from the second whose key does not exist in the first. 's FULL OUTER JOIN + CASE is valid, but the UNION ALL + NOT EXISTS approach is simpler to read and avoids CASE logic.

-- keep all rows from t1, then add t2 rows whose id is not present in t1
SELECT id, value
FROM dbo.t1

UNION ALL

SELECT t2.id, t2.value
FROM dbo.t2 t2
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.t1 t1
    WHERE t1.id = t2.id
);

Notes and caveats: prefer NOT EXISTS over NOT IN to avoid NULL-related surprises. Use UNION ALL when preserving duplicates and avoiding the sort/dedup cost of UNION; use UNION only when distinct rows are required. For very large tables, ensure an index on the key column in the first table so the anti-join is efficient. If the goal is to find rows in the second set that are distinct by all columns, EXCEPT (or t2 EXCEPT t1) can help but it removes duplicates and also sorts/duplicates internally, so test performance. This approach preserves the first dataset's values when keys overlap (first-set wins), which matches the desired behavior described by .

Recommended Answers

All 4 Replies

Member Avatar for urtrivedi

you may use left, right outer joins to control result as expected.

select t1.col1,t1.col2, t2.col1, t2.col2 
from db1.table1 t1 
left outer join db2.table2 t2 
on t1.CommonColName=t2.CommanColName
Member Avatar for js8765

Hi urtrived,

Thanks for you quick answer. Your solution is not exactly what I was looking for.

Here's an example for what I was hoping to do:

t1

id value
-- -----
1 valueA
2 valueB
3 valueC
4 valueD

t2

id value
-- -----
2 valueE
3 valueF
4 valueG
5 valueH

Desired result
id value
-- -----
1 valueA
2 valueB
3 valueC
4 valueD
5 valueH

Thanks again for any help.

Member Avatar for urtrivedi

I hope following query will help you

SELECT case when t1.id is null then t2.id else t1.id end id
, case when t1.id is null then t2.value else t1.value end value
FROM db1.table1 t1 FULL OUTER JOIN db2.table2 t2 ON t1.ID=t2.ID
Member Avatar for js8765

Perfect! Thanks very much!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.