compared two table with

Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

Be careful with nulls but try something like:

select *

from dbo.Employees E

where not exists (select *

from dbo.ModuleLog L

where L.EmployeeName = E.EmployeeName

and L.DOB = E.DOB)

or

select E.*

from dbo.Employees E

left join dbo.ModuleLog L on E.EmployeeName = L.EmployeeName and E.DOB = L.DOB

where L.EmployeeName is null

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

SSCarpal Tunnel

Points: 4727

August 14, 2006 at 9:57 am

Thanks. It is well done.

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 3

or if you are on sql 2005 you can use

select EmployeeName ,DOB

from Employees

EXCEPT

select EmployeeName ,E.DOB

from modulelog

Probably more efficient

www.sql-library.com[/url]

Sergiy SSC Guru Points: 110208 More actions · Answer 4

> Probably more efficient

Did you compare?

_____________
Code for TallyGenerator

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 5

nope thats why i said probably.

Would be interesting to see, if you have time.

I suspect it would be faster because no joins are required particulary no outer joins.

Perhaps the original poster could do a test and let us know how the 3 methods compare.

www.sql-library.com[/url]

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 6

I did not have SQL server 2005. I used the first method.

select *

from dbo.Employees E

where not exists (select *

from dbo.ModuleLog L

where L.EmployeeName = E.EmployeeName

and L.DOB = E.DOB).

Since I felt no join is better. It is fast. Thanks.