March 17, 2010 at 3:11 am
I have 2 Tables. Say,
[font="Courier New"]Table1[/font] - with fields PId, SSN
[font="Courier New"]Table2[/font] - with fields PId, SSN, Name
I want to list records from [font="Courier New"]Table2[/font], that are not present in [font="Courier New"]Table1[/font]. For that I wrote an [font="Courier New"]Inner Join[/font] as follows:
Select Table2.PId, Table2.SSN, Table2.Name From Table1
Join
Table2
On Table1.PId<>Table2.PId And Table1.SSN<>Table2.SSN
Surprisingly, the query is not behaving as expected.
Doesn't the [font="Courier New"]<>[/font] operator works with [font="Courier New"]Inner Join[/font]?
What is the exact query to achieve the same?
Any help will be appreciated.
Thanks.
---
.NET Developer
Blog: Did you say .NET?[/url]
Follow me on Twitter
March 17, 2010 at 3:32 am
The query that you wrote should return every record in Table1 with every record in Table2 that doesn’t match the value in PID and SSN column. Even if there is a record in Table1 that has a match in table2 it will be returned to the client because there are also other records in Table2 that don’t match Table1. You should use an outer join with equal sign and in the where clause look for nulls in Table2
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 17, 2010 at 4:11 am
SELECT Table2.PId,
Table2.SSN,
Table2.Name
FROM Table2 T2
WHERE NOT EXISTS
(
SELECT *
FROM Table1 T1
WHERE T1.PId = T2.PId
AND T1.SSN = T2.SSN
);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 4:42 am
For getting records present in Table2 but not in Table1,
you need to join these two tables using left outer join as:
Select A.*
from Table2 A left outer join Table1
on A.PId = B.Pid and A.SSN = B.SSN
where B.Pid is NULL
March 17, 2010 at 4:54 am
puneet shadija (3/17/2010)
For getting records present in Table2 but not in Table1,you need to join these two tables using left outer join as:
Select A.*
from Table2 A left outer join Table1
on A.PId = B.Pid and A.SSN = B.SSN
where B.Pid is NULL
Not only are you missing the table alias 'B', you are also missing the second condition 'AND B.SSN IS NULL'.
Your code, once fixed, returns duplicate values from table A too.
Apart from that, it's great :rolleyes:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 2:51 pm
Paul White (3/17/2010)
puneet shadija (3/17/2010)
For getting records present in Table2 but not in Table1,you need to join these two tables using left outer join as:
Select A.*
from Table2 A left outer join Table1
on A.PId = B.Pid and A.SSN = B.SSN
where B.Pid is NULL
Not only are you missing the table alias 'B', you are also missing the second condition 'AND B.SSN IS NULL'.
Your code, once fixed, returns duplicate values from table A too.
Apart from that, it's great :rolleyes:
Actually, the second condition would be superfluous if included. 🙂
March 17, 2010 at 10:43 pm
Lamprey13 (3/17/2010)
Actually, the second condition would be superfluous if included. 🙂
Thank you for that. Maybe it is just personal habit, but I usually include the conditions that cover a unique/primary key. Not sure what the key is comprised of here, so I would go with the extra 13 characters of typing.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 20, 2010 at 2:37 am
puneet shadija (3/17/2010)
Select A.*
from Table2 A left outer join Table1
on A.PId = B.Pid and A.SSN = B.SSN
where B.Pid is NULL
Extremely sorry for being late.
Puneet, Good Code. And is working as expected.
By the way, my key comprises of PId and SSN. Just for you information. 🙂
Thanks for the big help, everyone.
Thanks again.
---
.NET Developer
Blog: Did you say .NET?[/url]
Follow me on Twitter
March 20, 2010 at 10:17 am
abhilashca (3/20/2010)
Thanks for the big help, everyone.
Ok, thanks for the feedback.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 20, 2010 at 10:30 am
Looking at the following query:
Select
Table2.PId,
Table2.SSN,
Table2.Name
From
Table1
Inner Join Table2
On Table1.PId <> Table2.PId
And Table1.SSN <> Table2.SSN
another alternative could be:
Select
Table2.PId,
Table2.SSN,
Table2.Name
From
Table1
Inner Join Table2
On Table1.PId <> Table2.PId
Or Table1.SSN <> Table2.SSN
Boolean logic:
NOT (A = B and C = D) == A <> B or C <> D
March 20, 2010 at 10:37 am
You might want to check that over Lynn 😉
The boolean logic expression is correct, but the code isn't...!
Usually we use this trick to transform ORs into ANDs, not the other way around. ANDs are much more SQL Server friendly 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 20, 2010 at 11:19 am
Paul White NZ (3/20/2010)
You might want to check that over Lynn 😉The boolean logic expression is correct, but the code isn't...!
Usually we use this trick to transform ORs into ANDs, not the other way around. ANDs are much more SQL Server friendly 😀
Well, I would but no test suite plus I'm looking at stuff between steps as I am working on my taxes. System here at home is slow accessing the disk system. I really need to backup this system and rebuild it.
Seems that surfing the web doesn't hurt the as much as trying to run SQL Server at the same time.
If I get a chance when I break for lunch, I'll give it a try then.
March 20, 2010 at 12:12 pm
Lynn Pettis (3/20/2010)
Paul White NZ (3/20/2010)
You might want to check that over Lynn 😉The boolean logic expression is correct, but the code isn't...!
Usually we use this trick to transform ORs into ANDs, not the other way around. ANDs are much more SQL Server friendly 😀
Well, I would but no test suite plus I'm looking at stuff between steps as I am working on my taxes. System here at home is slow accessing the disk system. I really need to backup this system and rebuild it.
Seems that surfing the web doesn't hurt the as much as trying to run SQL Server at the same time.
If I get a chance when I break for lunch, I'll give it a try then.
Okay, took a break from taxes and tested. I stand corrected, at least in this instance. I have done this before and it does work in the appropriate situations. It probably would have helped if the OP had also provided test data with which to work. If PID and SSN are both unique, why test for both? If a given PID can have multiple SSN's or an SSN multiple PID's, then I can understand it.
With that, I'd probably use either the not exists or an outer join to solve this problem.
Or, seeing as this is SQL Server 2008, the following:
with ExceptCTE as (
select
t2.PID,
t2.SSN
from
dbo.Table2 t2
except
select
t1.PID,
t1.SSN
from
dbo.Table1 t1
)
select
t2.*
from
ExceptCTE ec
inner join dbo.Table2 t2
on (ec.PID = t2.PID
and ec.SSN = t2.SSN)
Would need to test, test, test to determine the best solution.
March 20, 2010 at 12:18 pm
Thinking about it, it may have been done inside a WHERE clause, not the ON portion of JOIN clause. Just not enough time to go searching through the forum posts where I have done it before when assisting others with what could be a similar (but different) problem.
March 20, 2010 at 1:04 pm
Lynn Pettis (3/20/2010)
Thinking about it, it may have been done inside a WHERE clause, not the ON portion of JOIN clause. Just not enough time to go searching through the forum posts where I have done it before when assisting others with what could be a similar (but different) problem.
Don't worry about it, Lynn. The boolean transformation is pretty straight forward - I was just pointing out that in your posted code, all you did was swap an AND for an OR - you need to reverse the signs and introduce a NOT . Just an oversight on your part, I am sure...but thought I should mention it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply