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 '12 at 05:21 AM in Default

Farzana gravatar image

Farzana
30 1 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 '12 at 08:42 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

I believe that EXCEPT is available in SQL Server 2005 and higher, just as an FYI.
Oct 17 '12 at 03:41 PM JohnM
was it indeed? Why on earth didnt I use it more often then?!
Oct 17 '12 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 '12 at 03:47 PM JohnM
Oh, I'm quite prepared to believe it.
Oct 17 '12 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 '12 at 07:34 AM

mjharper gravatar image

mjharper
1.3k 2 4 7

(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 '12 at 01:30 PM

vipin001 gravatar image

vipin001
40 1 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x103

asked: Oct 17 '12 at 05:21 AM

Seen: 3788 times

Last Updated: Oct 17 '12 at 03:50 PM