1

I have two tables, each with the following fields: IDnumber, SectionNumber, Date. There is overlapping information in the two tables.

How do I select only rows that do NOT overlap (ie. in one table but not the other)?

Ken White
  • 123,280
  • 14
  • 225
  • 444
user1572544
  • 11
  • 1

4 Answers4

1

You can use a NOT IN in your WHERE clause.

SELECT IDnumber, SectionNumber, Date
FROM table1
WHERE IDnumber NOT IN (SELECT IDnumber FROM table2)

OR NOT EXISTS

SELECT IDnumber, SectionNumber, Date
FROM table1 t1
WHERE NOT EXISTS (SELECT IDnumber FROM table2 t2 WHERE t1.IDnumber = t2.IDnumber)
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 1
    +1. Or an `OUTER JOIN` and only return the rows with `NULL` values in the joined table. :-) – Ken White Aug 02 '12 at 21:06
  • @user1572544 if this is helpful be sure to accept it as the answer via the checkmark on the left. :) – Taryn Aug 02 '12 at 21:15
  • For sql server in particular, use `NOT EXISTS` if performance matters. Citation: http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null – YS. Aug 02 '12 at 23:04
1

Which DBMS?

If SQL Server, then it's almost what you wrote in the title...

SELECT *
FROM Table1
WHERE IDnumber NOT IN (SELECT IDnumber FROM Table2)
Jeremy Wiggins
  • 7,239
  • 6
  • 41
  • 56
0

If you want to compare multiple columns, you need an outer join:

select table1.*
from table1 left outer join
     table2
     on table1.id = table2.id and
        table1.SectionNumber = table2.SectionNumber and
        table1.date = table2.date
where table2.id is null

In the case where you might have many matches between the tables, then the join can be inefficient. Assuming you only want those three fields, you can use a trick that avoids the join:

select id, SectionNumber, date
from ((select 0 as IsTable2, id, SectionNumber, date
       from table1
      ) union all
      (select 1 as IsTable2, id, SectionNumber, date
       from table2
      )
     ) t
 group by id, SectionNumber, date
 having max(isTable2) = 0
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
SELECT *
FROM Table1 t1 left join Table2 t2
on t1.id=t2.id
where t2.id is null
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33