compare two tables select not matching coloum in table b

Question

I have two tables with exactly the same columns

table ' a'
sno id name mobile
1 1001 shashi 8985500134
2 1002 raju 7896543211

3 2009 vamshi 9867567899

4 2012 pranay 9346352389

table 'b'
sno id name mobile
1 1001 shashi 8985500134
2 1002 raju 7896543211

3 2009 vamshi 9867567899

4 2012 pranay 9849313820

5 2014 raju 9876523411

6 2009 vinay 8883332012

compare two tables a& b mobile coloum and select ' table a 'not matching mobile in talble b

i want output like this..................

sno id name mobile

4 2012 pranay 9346352389

Sanjeeb Lenka · Answer

select * from A where Mobile not in (select Mobile from B)

Hanook · Answer

Hi, Alternate approach is as follows: select * from a where NOT EXISTs (select 1 from b WHERE b.Mobile = a.Mobile) Do you know one thing regarding NOT IN clause of SQL Server.. NOT EXISTS clause is more optimised one comaring to NOT IN operator... For suppose there is NULL value in the Mobile column of table B, then NOT IN query results NO more records... where as NOT EXISTS will give you correct result For the below sample data NOT IN query gives NO records where as NOT EXISTS query gives you correct result.. Check once table ' a' sno id name mobile 1 1001 shashi 8985500134 2 1002 raju 7896543211 3 2009 vamshi 9867567899 4 2012 pranay 9346352389 table 'b' sno id name mobile 1 1001 shashi 8985500134 2 1002 raju 7896543211 3 2009 vamshi 9867567899 4 2012 pranay 9849313820 5 2014 raju 9876523411 6 2009 vinay NULL

JAYRAM · Answer

thanx............

Sanjay Kumar · Answer

http://www.sqlservercurry.com/2008/01/compare-data-between-two-tables.html select * from tableA minus select * from tableB

Kiran Kumar Talikoti · Answer

Hi ani, try this, select * from table_a except select * from table_b Hope it helps you

compare two tables select not matching coloum in table b

Answers (5)