SQL Server bug?

  • Thread starter Thread starter BLUE
  • Start date Start date
B

BLUE

SELECT * FROM Actions WHERE NOT EXISTS
(SELECT Actions.ActionID
FROM Actions, Tracks
WHERE Actions.ActionID <> Tracks.ActionID)

SELECT * FROM Actions WHERE NOT EXISTS
(SELECT Actions.ActionID
FROM Actions, Tracks
WHERE Actions.ActionID = Tracks.ActionID)

Always 0 rows: I have to use NOT IN why?

These prevents from doing DELETE FROM Actions WHERE NOT EXISTS (SELECT
1...).
 
I don't have answer for why it doesn't work but
at first glance, I believe your query would be slower than

select * from Actions
where ActiveID not in (select ActionID from Tracks)

My "guess" is that it would be slower because
you are performing a join and test for every
ActiveID in Actions to Tracks.ActionID
versus not performing a join and doing a straight lookup.

If I were you, I'd look at the execution plan and
test the performance before making your finally decision.
 
Sorry for the post, there was no data in the DB :)

This works and is very fast:

DELETE FROM Actions
WHERE NOT EXISTS
(SELECT 1
FROM Tracks
WHERE Tracks.ActionID = Actions.ActionID)


Thanks,
Luigi.
 
Perhaps but that isn't the exact same thing
you were doing above... I'd interested
in your timing experiments to see if the
query I posted is faster or slow than
your original post. Particularly on
a table with a large number of rows.
 
Back
Top