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.
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.
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.
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.
IF EXISTS(SELECT * FROM sysdatabases WHERE name='DBmyUnmatchedTest')
DROPDATABASE DBmyUnmatchedTest
GO
CREATEDATABASE DBmyUnmatchedTest
GO
USE DBmyUnmatchedTest
GO
CREATETABLE a
(
iD INTIDENTITY(1,1)PRIMARYKEY,
val INT
)
CREATETABLE b
(
iD INT NOT NULL,
val INT
)
DECLARE @val INT
SET @val = 1
WHILE @val <= 1000
BEGIN
INSERTINTO a (val)
VALUES(@val)
SELECT @val = @val + 1
END
INSERTINTO 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 6:41 pm.
No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Unsolved 
