CODEWITHSUNDEEP
sqlsql-server
kstubs
kstubs

Reputation: 808

SqlServer Select Where In

I'm thrown for a loop on this one, and wondering if I don't fully understand the select where in usage or if I've just made a boo boo in this code:

DECLARE @driver TABLE (ID INT)
INSERT INTO @driver select eventid from event where event_code_name IS NULL
select eventid from event where eventid in (select eventid from @driver)

There are 3137 records total in the event table. There are 458 records who's event_code_name field is null. In the select above I'm expecting 458, but I'm getting all event records back instead.

What did I miss?

Upvotes: 0

Views: 125

Answers (5)

mordack550
mordack550

Reputation: 502

Since you are using 2 tables, one virtual and one physical, the solution is to use the INNER JOIN clause: 

DECLARE @driver TABLE (ID INT)
INSERT INTO @driver SELECT eventid FROM event WHERE event_code_name IS NULL
SELECT eventid FROM event INNER JOIN @driver ON id = event.eventid

This way you only get the IDs that are present in both the event table and the @driver table.
INNER JOIN is also much more efficent than using an IN (SELECT ...)

Without the use of the @driver table you can just obtain the same result using the SELECT query you used in the INSERT statement: 

SELECT eventid FROM event WHERE event_code_name IS NULL

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

Using IN operator can be less efficient sometimes, Specially if your column in sub query is Nullable IN operator can show some really unexpected results.

I would suggest using EXSITS, something like this...

select eventid 
from [event] 
where EXISTS (select 1 
              from @driver
              WHERE id = [event].eventid)

For your this specific query you dont really need a table variable. you can simple do a select againt your table, something like this...

SELECT eventid 
FROM [event]
WHERE event_code_name IS NULL

Upvotes: 0

Marc
Marc

Reputation: 992

It might be worth performing an ISNULL on all columns that may contain a null value and filtering out the default value of the ISNULL.

Upvotes: 0

mhn
mhn

Reputation: 2750

Are you missing a DISTINCT?

select DISTINCT eventid from event where eventid in (select eventid from @driver)

Upvotes: 0

JBrooks
JBrooks

Reputation: 10013

I think (select eventid from @driver) should be (select id from @driver)

Upvotes: 3

Related Questions