x

How to Select unmatched data from two column in two table

I have two table(T1,T2) in my database,in those table column 'ID' is common.The problem is that when I inter join them,I can't find those ID that are not match.How I can find those ID that are not common in two table.

more ▼

asked Oct 17, 2012 at 05:21 AM in Default

avatar image

Farzana
30 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

With SQL Server 2012 you can use the EXCEPT keyword

 USE [CentralDB]
 GO

 CREATE TABLE T1 ( ID INT, DName VARCHAR(20)-- column_name data_type,...
    )
 CREATE TABLE T2 ( ID INT, DName VARCHAR(20)-- column_name data_type,...
    )

 INSERT T1
 VALUES ( 1, 'Bill' ),
  ( 3, 'Barry' ),
  ( 4, 'Bryan' ),
  ( 6, 'Bob' ),
  ( 7, 'Bert' )
 
 INSERT T2
 VALUES ( 1, 'Charles' ),
  ( 2, 'Chris' ),
  ( 3, 'Colin' ),
  ( 6, 'Clive' ),
  ( 8, 'Clint' )
 
 ( SELECT ID
   FROM T1
   EXCEPT
   SELECT ID
   FROM t2
 )
 UNION
 ( SELECT ID
   FROM T2
   EXCEPT
   SELECT ID
   FROM t1
 )

 DROP TABLE T1
 DROP TABLE T2
more ▼

answered Oct 17, 2012 at 08:42 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

I believe that EXCEPT is available in SQL Server 2005 and higher, just as an FYI.

Oct 17, 2012 at 03:41 PM JohnM

was it indeed? Why on earth didnt I use it more often then?!

Oct 17, 2012 at 03:42 PM Fatherjack ♦♦

Yup, I believe so! http://msdn.microsoft.com/en-us/library/ms188055(v=sql.90).aspx ;-)

Unless of course I'm reading that wrong . .which is entirely possible. :D

Oct 17, 2012 at 03:47 PM JohnM

Oh, I'm quite prepared to believe it.

Oct 17, 2012 at 03:50 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

To find the records in T1 that are not in T2 you can LEFT JOIN T1 to T2 and limit to where there is a NULL in the ID column of T2. Is that what you're trying to do?

 SELECT *
 FROM T1 
 LEFT JOIN T2
     ON T1.Id = T2.Id
 WHERE T2.Id IS NULL
more ▼

answered Oct 17, 2012 at 07:34 AM

avatar image

mjharper
2k 3 8 13

(comments are locked)
10|1200 characters needed characters left

If you need the intersection, use the INNER join. If you need the records which are in left side table and not in right side table which we can say disjunction, use the LEFT join and put the condition RIGHT TABLE.ID IS NULL in where clause. This way you can read the record which are in left side table bit not in right side table.

more ▼

answered Oct 17, 2012 at 01:30 PM

avatar image

vipin001
40 2 2 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x150

asked: Oct 17, 2012 at 05:21 AM

Seen: 11372 times

Last Updated: Oct 17, 2012 at 03:50 PM

Copyright 2016 Redgate Software. Privacy Policy