Using a Join to return non matched rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using a Join to return non matched rows

Author

Topic

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-05-29 : 05:21:43

I have the following T-SQL.
This brings the rows matched on a column.
How do I go about returning non matched rows ?

Query
-------

SELECT a.SS_Number, a.First_Name, a.Last_Name
FROM EmployeCompare b INNER JOIN EmployeDetails a
ON a.First_Name = b.First_Name

If I change this "ON a.First_Name = b.First_Name"
to "ON a.First_Name != b.First_Name" I get allot of rows send back.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 05:47:23

Select SS_Number, First_Name, Last_Name from
(SELECT a.SS_Number, a.First_Name, a.Last_Name, b.First_Name as bFname
FROM EmployeCompare b LEFT JOIN EmployeDetails a
ON a.First_Name = b.First_Name) as b where b.First_Name is null

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-05-29 : 06:56:20

Thanks for the help.
But I only get Null Values returned.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-29 : 07:44:06

quote:
Originally posted by u2envy1

Thanks for the help.
But I only get Null Values returned.

so, what u want to get?

u r getting such null values due to:

Select SS_Number, First_Name, Last_Name from
(SELECT a.SS_Number, a.First_Name, a.Last_Name, b.First_Name as bFname
FROM EmployeCompare b LEFT JOIN EmployeDetails a
ON a.First_Name = b.First_Name) as b where b.First_Name is null

Mahesh

vladimir.stokic
Starting Member

25 Posts

Posted - 2007-05-29 : 07:44:21

What you are trying to do here is what is called theta join in relational algebra. Generally, it is a join of two tables where specified columns satisfy certain operator (<, >, =, !=, etc.) Natural join is a special case of theta join, where the operator is =. Now, changing an operator = to != is basically using a theta join instead of natural join, and it should return you all non matched rows, the number of which usually is greater than that of the matched rows. However, if you doubt this, you can simply check it with this query:

SELECT SS_Number, First_Name, Last_Name FROM EmployeDetails WHERE First_Name NOT IN (SELECT a.First_Name FROM EmployeCompare b INNER JOIN EmployeDetails a
ON a.First_Name = b.First_Name)

Hope this helps.

Being a genius has its advantages...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-29 : 08:04:59

You can use FULL JOIN to this effect:

select 
	Coalesce(t1.firstname, 'No-Match'), Coalesce(t2.firstname, 'No-Match')
from 
	EmployeCompare t1 Full Join EmployeDetails  t2 on t1.FirstName = t2.FirstName
where 
	t1.FirstName is null or t2.FirstName is Null

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-29 : 09:29:17

No FULL OUTER JOINS!!!