Search for same Values in two SQL TABLES

Question

I have two tables:

Table 1 with columns Number and TEXT
Table 2 with columns Number and TEXT

Now Table 1 has Nr = 12345AB and Table 2 Nr = 00012345AB

How can I find all columns from Table 1 that are not in Table 2?

NOT IN or NOT EXISTS might be of help – apomene Sep 03 '14 at 13:32 — apomene, Sep 03 '14 at 13:32
Are you trying to find columns? or rows? – crthompson Sep 03 '14 at 13:35 — crthompson, Sep 03 '14 at 13:35

score 2 · Accepted Answer · edited Sep 03 '14 at 13:41

2

Try this select:

select 
  * 
from 
  table1 t1 
  left join table2 t2 on t1.number=t2.number 
where 
  t2.number is null

edited Sep 03 '14 at 13:41

crthompson

15,653
6
58
80

answered Sep 03 '14 at 13:32

Jens

67,715
15
98
113

score 1 · Answer 2 · answered Sep 03 '14 at 13:32

1

Try exists:

  select t1.*
    from Table1 t1
   where not exists (select 1
                       from Table2 t2
                      where t2.Number = t1.Number)

answered Sep 03 '14 at 13:32

Dmitry Bychenko

180,369
20
160
215

Yes you're right, minus is not a standard solution. Thanks – Aramillo Sep 03 '14 at 13:41

score 0 · Answer 3 · edited May 23 '17 at 10:26

0

I think he is looking for a fuzzy match. In which case =, LIKE, CONTAINS will not work. You will need to roll your own similar to this solution.

edited May 23 '17 at 10:26

Community

1
1

answered Sep 03 '14 at 14:36

nshah

340
1
5

score 0 · Answer 4 · answered Sep 03 '14 at 14:45

0

    This is also a method but its too lengthy :-)

        SELECT table1.*
        FROM table2
        WHERE (number NOT IN
            (SELECT number 
             FROM table2)) AND (text NOT IN
            (SELECT text 
             FROM table2))

answered Sep 03 '14 at 14:45

Nikhil T Nair

101
4

Search for same Values in two SQL TABLES

4 Answers4