CODEWITHSUNDEEP
sqlsql-server
user1572544
user1572544

Reputation: 11

Select from table not in another table

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

Upvotes: 1

Views: 106

Answers (4)

AnandPhadke
AnandPhadke

Reputation: 13486

SELECT *
FROM Table1 t1 left join Table2 t2
on t1.id=t2.id
where t2.id is null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269453

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

Upvotes: 0

Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

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)

Upvotes: 1

Taryn
Taryn

Reputation: 247620

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)

Upvotes: 1

Related Questions