How to find all records in one table that DO NOT match another table
-
I have two tables with matching column types. I want to list all of the records in T1 that are NOT present in T2. The combination of CustNo and OrderNo should make the record unique.
T1 (CustNo, OrderNo, Amount)
T2 (CustNo, OrderNo, Amount)
T1
1, 1, 23
1, 2, 34
2, 1, 55
T2
1, 2, 34
3, 1, 22
3, 2, 74
Results
1, 1, 23
2, 1, 55
-
select t1.*
from t1 left join t2 on t1.Custno = t2.CustNo and t1.OrderNo = t2.OrderNo
where t2.CustNo is null and t2.OrderNo is null
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Select *
From t1
Where CustNo+OrderNo not in
(Select CustNo+OrderNo from t2)
-
select * from T1 where custno not in (select custno from T2)
and OrderNo not in (selecct OrderNo from T2)
With Regards,
Karthikeyan
-
So what's wrong with my solution?
Your solution, I'm afraid, is logically flawed. Eg if this is sample data
T1 (CustNo, OrderNo)
1,1
1,2
2,1
T2 (CustNo, OrderNo)
1,1
Your query will return no records.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
SELECT *
FROM T1
WHERE NOT EXISTS
(SELECT *
FROM T2
WHERE CustNo = T1.CustNo AND OrderNo = T1.OrderNo)
--Jonathan -
This is the way I do it. It seems to work better than doing the WHERE NOT EXISTS type selects.
select t1.*
from t1
left outer join t2 on t2.custno = t1.custno and t2.orderno = t1.orderno
where t2.custno is null
Ad maiorem Dei gloriam
-
You are right with this test. The first reply was ok too but the test on the second field for null is not necessary. Both solutions get around a potentially unworkable case where the subquery is huge due to large database sizes and probably uses indexes better
-
Oops - I didn't read Phill's first post close enough, he did do the left outer join, I'm not used to reading queries where the joins are on the same line as the froms and such - I need spaces between things to make my brain work.
I'd say I need glasses, but I just had Lasik done, so that can't be it. Must be getting senile.
Ad maiorem Dei gloriam
-
The correlated subquery actually performs better when NOT is involved. Try looking at the execution times for each.
--Jonathan -
You are looking for a intersection of two tables, Exist() oparator to find this
Select * From T1 Where CustNo Exist(Select CustNo From T2 Where CustNo=T1.CustNo) and OrderNo Exist(Select OrderNo From T2 Where OrderNo=T1.OrderNo) and Amount Exist(Select Amount From T2 Where Amount=T1.Amount)
This is a way of getting intersection in MS SQL Server, another way you can do this using JOIN.
regards
Shifan
shifan
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply