NOT IN or EXCEPT query - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

NOT IN or EXCEPT query

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-02 : 15:24:44

I have the following table:

CREATE TABLE [dbo].[MSDN_Email](
	[Email] [nvarchar](255) NULL,
	[Last_Name] [nvarchar](255) NULL,
	[First_Name] [nvarchar](255) NULL
)

The query below looks complicated, but it works, and all it does is select the Email, LAST_NAME, FIRST_NAME, and EMPLID. All I want to do is use the below query as a basis for an outer query - that is to list all MSDN_Email rows that are not in the below query. But, even though the below query works and returns the proper columns, I get error after error in every imaginable variation. I have put the whole thing in parentheses and made it a derived table (again), but didn't know how to do the NOT IN. I have also tried an EXCEPT query and couldn't get the columns to line up or some other error. It was always something.

select dt.Email, dt.Last_Name, dt.First_Name, dt.EMPLID from (
select d.* from (
select MSDN.Email, MSDN.Last_Name, MSDN.First_Name, h.EMPLID from MSDN_Email MSDN
left join HR_NSV_PM H on H.Email_ID = MSDN.Email
) d
where nullif(d.emplid,'') is not null
union all
select 'MSDN email NOT Found - Name Match' as Email, h.LAST_NAME, h.FIRST_NAME, d.EMPLID from (
select MSDN.Email, MSDN.First_Name FN, MSDN.Last_Name LN, H.EMPLID from MSDN_Email MSDN
left join HR_NSV_PM H on H.Email_ID = MSDN.Email
where nullif(H.EMPLID,'') is null
) d
left join HR_NSV_PM h on h.LAST_NAME = d.LN and h.FIRST_NAME = d.FN
WHERE NULLIF(h.EMPLID,'') IS NOT NULL
) dt
order by CASE WHEN dt.Email Like 'MSDN%' THEN 1 ELSE 0 END, CASE WHEN nullif(dt.EMPLID,'') IS NULL THEN 1 ELSE 0 END, dt.LAST_NAME, dt.FIRST_NAME

Thanks for any suggestions.

Duane

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-06-02 : 16:53:52

Can you provide the example table, with data, and the expected output?

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-02 : 17:19:10

"Why don't you just say the name of the movie you'd like to see?"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-06-02 : 17:31:57

[code]

IF OBJECT_ID('tempdb..#MSDN_Email') IS NOT NULL DROP TABLE #MSDN_Email;
IF OBJECT_ID('tempdb..#HR_NSV_PM') IS NOT NULL DROP TABLE #HR_NSV_PM

CREATE TABLE #MSDN_Email(
[Email] [nvarchar](255) NULL,
[Last_Name] [nvarchar](255) NULL,
[First_Name] [nvarchar](255) NULL
);

CREATE TABLE #HR_NSV_PM(
Email_ID [nvarchar](255) NULL,
[Last_Name] [nvarchar](255) NULL,
[First_Name] [nvarchar](255) NULL,
EMPLID INT
);

INSERT #MSDN_Email
VALUES
('ab@abc.com','Apple','Banana'),
('cr@abc.com','Carrot','Radish'),
('kr@abc.com','Kale','Radichio'),
('sp@abc.com','Spearmint','Pear'),
('pc@abc.com','Pork','Chicken')

INSERT #HR_NSV_PM
VALUES
('ab@abc.com','Apple','Banana',1),
('cr@abc.com','Carrot','Radish',2),
('ui@abc.com','Kale','Radichio',3),
('pc@abc.com','Spearmint','Pear',4);

select dt.Email,
dt.Last_Name,
dt.First_Name
FROM #MSDN_Email
AS dt
EXCEPT
select dt.Email,
dt.Last_Name,
dt.First_Name
--, dt.EMPLID
from (
select MSDN.Email,
MSDN.Last_Name,
MSDN.First_Name,
h.EMPLID
from #MSDN_Email
AS MSDN
join #HR_NSV_PM
AS H
on H.Email_ID = MSDN.Email
where nullif(H.emplid,'') is not null

union all

select 'MSDN email NOT Found - Name Match' as Email,
MSDN.LAST_NAME,
MSDN.FIRST_NAME,
h2.EMPLID
from #MSDN_Email
AS MSDN
left join #HR_NSV_PM
AS H1
on H1.Email_ID = MSDN.Email
left join #HR_NSV_PM
AS h2
on h2.LAST_NAME = MSDN.LAST_NAME
and h2.FIRST_NAME = MSDN.FIRST_NAME
where nullif(H1.EMPLID,'') is null
AND NULLIF(h2.EMPLID,'') IS NOT NULL
) dt

/*
Email Last_Name First_Name
kr@abc.com Kale Radichio
sp@abc.com Spearmint Pear
*/
[/code]

I reformatted your original query to perform better, but it's the same result.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-06-02 : 17:35:11

[code]IF OBJECT_ID('tempdb..#MSDN_Email') IS NOT NULL DROP TABLE #MSDN_Email;
IF OBJECT_ID('tempdb..#HR_NSV_PM') IS NOT NULL DROP TABLE #HR_NSV_PM

CREATE TABLE #MSDN_Email(
[Email] [nvarchar](255) NULL,
[Last_Name] [nvarchar](255) NULL,
[First_Name] [nvarchar](255) NULL
);

CREATE TABLE #HR_NSV_PM(
Email_ID [nvarchar](255) NULL,
[Last_Name] [nvarchar](255) NULL,
[First_Name] [nvarchar](255) NULL,
EMPLID INT
);

INSERT #MSDN_Email
VALUES
('ab@abc.com','Apple','Banana'),
('cr@abc.com','Carrot','Radish'),
('kr@abc.com','Kale','Radichio'),
('sp@abc.com','Spearmint','Pear'),
('pc@abc.com','Pork','Chicken')

INSERT #HR_NSV_PM
VALUES
('ab@abc.com','Apple','Banana',1),
('cr@abc.com','Carrot','Radish',2),
('ui@abc.com','Kale','Radichio',3),
('pc@abc.com','Spearmint','Pear',4);

select MSDN.Email,
MSDN.Last_Name,
MSDN.First_Name
FROM #MSDN_Email
AS MSDN
WHERE NOT EXISTS
(
select *
from (
select MSDN.Email,
MSDN.Last_Name,
MSDN.First_Name,
h.EMPLID
from #MSDN_Email
AS MSDN
join #HR_NSV_PM
AS H
on H.Email_ID = MSDN.Email
where nullif(H.emplid,'') is not null

union all

select 'MSDN email NOT Found - Name Match' as Email,
MSDN.LAST_NAME,
MSDN.FIRST_NAME,
h2.EMPLID
from #MSDN_Email
AS MSDN
left join #HR_NSV_PM
AS H1
on H1.Email_ID = MSDN.Email
left join #HR_NSV_PM
AS h2
on h2.LAST_NAME = MSDN.LAST_NAME
and h2.FIRST_NAME = MSDN.FIRST_NAME
where nullif(H1.EMPLID,'') is null
AND NULLIF(h2.EMPLID,'') IS NOT NULL
) dt

WHERE dt.Email = MSDN.Email
AND dt.First_Name = MSDN.First_Name
AND dt.Last_Name = MSDN.Last_Name
);

/*
Email Last_Name First_Name
kr@abc.com Kale Radichio
sp@abc.com Spearmint Pear
*/
[/code]

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-06-02 : 17:40:27

FYI, you'll notice that Mr. Chicken Pork doesn't show up because in the sample dataset I provided, Mrs. Pear Spearmint had his email address and that's the way your logic works.

You'll also notice that I used NOT EXISTS instead of NOT IN. NOT EXISTS achieves the desired result but isn't going to have the performance hit and one column limitation as NOT IN.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-02 : 18:50:41

Thank you lazerath the detailed effort you put into this. I am still on the front end of a long learning curve and this example helps clarify some new things for me. I did come up with something finally that seems to work, but it kind of evolved step by step and I am not sure whether I covered the bases correctly or efficiently. I'll see what you think:

 -- select the last names and first names that have no match. Those with matching emails not considered.
select M.Email Email, M.Last_Name Last_Name, M.First_Name First_Name from MSDN_Email M
 -- Join MSDN_Email again to get the email address which had to be eliminated temporarily in below query.
 -- d stands for inner derived table
 -- dt stands for outer derived table
 -- m stands for the outer most MSDN_Email
 -- m2 stands for the whole derived table unit below that is joined to m
inner join (
 -- All MSDN_Email rows
select MSDN_Email.Last_Name Last_Name, MSDN_Email.First_Name First_Name
from MSDN_Email
 -- Except all rows that either match by email (first group) or by last and first names (second group)
except
select dt.Last_Name Last_Name, dt.First_Name First_Name from (
select d.* from (
select MSDN.Email, MSDN.Last_Name, MSDN.First_Name, h.EMPLID from MSDN_Email MSDN
left join HR_NSV_PM H on H.Email_ID = MSDN.Email
) d
where nullif(d.emplid,'') is not null
union all
select 'MSDN email NOT Found - Name Match' as Email, h.LAST_NAME, h.FIRST_NAME, d.EMPLID from (
select MSDN.Email, MSDN.First_Name FN, MSDN.Last_Name LN, H.EMPLID from MSDN_Email MSDN
left join HR_NSV_PM H on H.Email_ID = MSDN.Email
where nullif(H.EMPLID,'') is null
) d
left join HR_NSV_PM h on h.LAST_NAME = d.LN and h.FIRST_NAME = d.FN
WHERE NULLIF(h.EMPLID,'') IS NOT NULL
) dt
 --order by LAST_NAME, FIRST_NAME
) M2
ON M2.Last_Name = M.Last_Name AND M2.First_Name = M.First_Name
order by M.Last_Name, M.First_Name

Thanks again for all the time devoted to this. I am definitely going to run your script and see how I can apply it to mine.

Duane

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-06-03 : 14:46:54

Duane,

Your example is similar to the NOT EXISTS example I provided, except you'll need to change this part in my code:

		WHERE	dt.Email = MSDN.Email
		AND		dt.First_Name = MSDN.First_Name
		AND		dt.Last_Name = MSDN.Last_Name

with this:

		WHERE		dt.First_Name = MSDN.First_Name
		AND		dt.Last_Name = MSDN.Last_Name

The one thing I want to mention is that you need to start thinking about how to eliminate as many derived tables in your code as possible. The reason is because once you start nesting derived tables you complicate the execution plan and cause more work for the optimizer. It may work great for small recordsets, but databases grow and/or testing is often done on a database that doesn't have a realistic number of records. Time after time a developer has come to me asking why there code suddenly takes so much longer to execute and it comes down to excessive derived tables (and/or views).

I eliminated some unnecessary derived tables for you and you should find that it matches the results of your query once you make the above change.

Also, when it doubt, do some performance testing.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-06-03 : 15:07:07

Thank you again. Your code worked and is much more efficient than mine. Of course, I had to take out the temp table references (#). That was about it. I got the same results so I am pretty confident both are correct logically.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources