Confused with "NOT IN" - 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 2005 Forums

Transact-SQL (2005)

Confused with "NOT IN"

Author

Topic

sulman
Starting Member

20 Posts

Posted - 2007-11-05 : 06:21:43

Hi,

I am trying to extract from one table every ID that is not in another:


select * from tabel1 where ID NOT IN
(
	select distinct(ClubID) as ID from tabel2
)

tabel1 has over 4000 rows and my inner query returns about 250 rows. So I thought I should be getting about 3750 rows returned.
But I don't get any.

Can anyone see what I have done wrong?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 06:24:38

try this first

SELECT * FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t2.ClubID = t1.ID
WHERE t2.ClubID IS NULL

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 06:25:11

I suspect that all the CLUBID in Table1 are present in ID for table2.

E 12°55'05.25"
N 56°04'39.16"

sulman
Starting Member

20 Posts

Posted - 2007-11-05 : 06:57:29

Thanks Peso,

Yes that's right the Id's match up.

That works well thanks.
How come the NOT IN clause doesn't work though?

I will at some point also need to filter table 2 a bit further so that my original statement would now look like this:


select * from tabel1 where ID NOT IN
(
	select distinct(ClubID) as ID from tabel2 where Date='08/26/2007'
)

Modifying your code to :


	SELECT * FROM tblClubs AS t1
	LEFT JOIN tblFixtureData AS t2 ON t2.HostClub = t1.ClubID
	WHERE t2.HostClub IS NULL AND (t2.Date='08/26/2007')

Doesn't work?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 07:00:48

[code]SELECT *
FROM tblClubs AS t1
LEFT JOIN tblFixtureData AS t2 ON t2.HostClub = t1.ClubID
AND t2.Date='08/26/2007'
WHERE t2.HostClub IS NULL[/code]For your query, try to remove the ALIAS part after the DISTINCT(ClubID) part.

E 12°55'05.25"
N 56°04'39.16"

sulman
Starting Member

20 Posts

Posted - 2007-11-05 : 08:16:46

Thanks Peso,

That is working perfectly.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-05 : 10:21:43

you can also use a exists statment. Check the execution plan on which is better.


SELECT		*
FROM		tblClubs AS t1
where
NOT EXISTS(Select * from TblFixtureData as t2 where t2.HostClub = t1.ClubID
			AND t2.Date='08/26/2007')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources