Exclude duplicate records in a JOIN

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 429,970 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,598 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 8824 | Replies: 8

•

Join Date: Dec 2004

Location: Hiawassee, Georgia

Posts: 129

Reputation: Geek-Master is an unknown quantity at this point

Rep Power: 4

Solved Threads: 1

Geek-Master Geek-Master is offline

Offline

Junior Poster

Exclude duplicate records in a JOIN

Apr 10th, 2007

I have a view that looks at new student records and a table that stores those records and information related to their Active Directory logins. What I need to do is filter out any duplicate records between the two "tables". So I only see unique records in the view to be added to the table.

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Dec 2004

Location: Hiawassee, Georgia

Posts: 129

Reputation: Geek-Master is an unknown quantity at this point

Rep Power: 4

Solved Threads: 1

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: Exclude duplicate records in a JOIN

Apr 11th, 2007

I found the EXCEPT operator for SQL 2005, but I don't think it's available in SQL 2000. What alternative can you use?

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Feb 2005

Location: Braintree, UK

Posts: 1,165

Reputation: hollystyles will become famous soon enough

Rep Power: 7

Solved Threads: 59

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Exclude duplicate records in a JOIN

Apr 11th, 2007

try DISTINCT

eg: select distinct * from table1 join table2 on blah...

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Dec 2004

Location: Hiawassee, Georgia

Posts: 129

Reputation: Geek-Master is an unknown quantity at this point

Rep Power: 4

Solved Threads: 1

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: Exclude duplicate records in a JOIN

Apr 11th, 2007

The ON clause will only look for ID numbers that are equal to each other.

ON a.id_num = b.id_num

how can I join the two tables to show only the distinct records in table [a].

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Dec 2004

Location: Hiawassee, Georgia

Posts: 129

Reputation: Geek-Master is an unknown quantity at this point

Rep Power: 4

Solved Threads: 1

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: Exclude duplicate records in a JOIN

Apr 11th, 2007

I did find this to work, but would like to know if there is a better way.

SELECT tableA.* FROM tableA LEFT JOIN tableB
ON tableA.id = tableB.id
WHERE tableB.id IS NULL

Last edited by Geek-Master : Apr 11th, 2007 at 10:18 am.

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Feb 2005

Location: Braintree, UK

Posts: 1,165

Reputation: hollystyles will become famous soon enough

Rep Power: 7

Solved Threads: 59

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Exclude duplicate records in a JOIN

Apr 11th, 2007

•

The ON clause will only look for ID numbers that are equal to each other.

No the default JOIN specifys only records that match, the default join is INNER.

LEFT OUTER JOIN (can be abreviated to LEFT JOIN) means all records in left table whether there's a match in the right table or not, RIGHT JOIN is the opposite FULL OUTER JOIN being both.

Your query:

Help with Code Tags

(Toggle Plain Text)

SELECT tableA.* FROM tableA LEFT JOIN tableB
ON tableA.id = tableB.id
WHERE tableB.id IS NULL

Has nothing to do with distinct records, using a left join and a where is null clause is the classic way to get UNMATCHED records from the left table.

Having written all the above I now think I know what you're after, you want to insert only records from table a into table b where they don't exist in table b already ?

If so then you are on the right track with the unmatched type query, that is the way to do it, I;m not aware of a better way to be honest.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Dec 2004

Location: Hiawassee, Georgia

Posts: 129

Reputation: Geek-Master is an unknown quantity at this point

Rep Power: 4

Solved Threads: 1

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: Exclude duplicate records in a JOIN

Apr 11th, 2007

On another forum they showed me that this was supposed to be better than what I was coding.

SELECT *
FROM tableA a
WHERE NOT EXISTS
(
SELECT *
FROM tableB b
WHERE a.id_num = b.id_num
)

It gives the same results and may or may not be better.

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Feb 2005

Location: Braintree, UK

Posts: 1,165

Reputation: hollystyles will become famous soon enough

Rep Power: 7

Solved Threads: 59

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Exclude duplicate records in a JOIN

Apr 11th, 2007

Hmm my gut feeling is it isn't, but I also know the only way to be sure is to test it for ourselves using profiler and looking at the query plan for each scenario, which could give me some material for another Sql Article, this could be quite an interesting thread actually. I only have Sql 2005 having finally evicted Sql 2000 from my box only yesterday, but the results should still be indicative for both versions.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Feb 2005

Location: Braintree, UK

Posts: 1,165

Reputation: hollystyles will become famous soon enough

Rep Power: 7

Solved Threads: 59

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Exclude duplicate records in a JOIN

Apr 11th, 2007

OK so I set up a database as follows:

 Help with Code Tags 
 tsql Syntax (Toggle Plain Text) 
IF EXISTS(SELECT * FROM sysdatabases WHERE name='DBmyUnmatchedTest')  
	DROP DATABASE DBmyUnmatchedTest  
GO
 
 
CREATE DATABASE DBmyUnmatchedTest 
GO 
 
USE DBmyUnmatchedTest
GO 
 
CREATE TABLE a 
(
	iD INT IDENTITY(1,1) PRIMARY KEY,
	val INT 
)
 
CREATE TABLE b 
(
	iD INT NOT NULL, 
	val INT 
)
 
DECLARE @val INT 
SET @val = 1 
 
WHILE @val <= 1000 
BEGIN 
	INSERT INTO a (val) 
	VALUES (@val)
 
	SELECT @val = @val + 1
END
 
INSERT INTO b (iD, val) 
SELECT iD, val FROM a WHERE iD <= 500 
IF EXISTS(SELECT * FROM sysdatabases WHERE name='DBmyUnmatchedTest')  
	DROP DATABASE DBmyUnmatchedTest  
GO


create database DBmyUnmatchedTest 
GO 

use DBmyUnmatchedTest
GO 

create table a 
(
	iD int identity(1,1) primary key,
	val int 
)

create table b 
(
	iD int NOT NULL, 
	val int 
)

declare @val int 
set @val = 1 

while @val <= 1000 
begin 
	insert into a (val) 
	values (@val)

	select @val = @val + 1
end

insert into b (iD, val) 
select iD, val from a where iD <= 500 

This creates a database and two related tables, we insert half the records in table a to table b.

I then executed the two types of statement and looked at the query plans. The second statement using the where not exists syntax did appear to have a slightly better plan, the where b.iD is null had an additional filter at the end of the plan, but it only representes 1% of the total cost so really there wasn't much between them. When I added an index to the iD column in table b both queries' performance improved equally.

Due to limitations in the express edition of Sql Server 2005 I can't attach Sql Profiler. But the client statistics reveal a slightly better average Total Execution Time of 108ms for the original left join query as opposed to an average of 130ms for the not exists method.

Last edited by hollystyles : Apr 11th, 2007 at 5:41 pm.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey