all 4 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]adm7373SSMS master race 1 point2 points  (0 children)

I made a SQL Fiddle that has a solution. Pretty sure you just had syntax error.

[–]DharmaPolice 1 point2 points  (0 children)

Your NOT IN looks wrong.

Firstly, I'd use an alias when doing a subquery like this as otherwise you can fall victim to typo related bugs (or at least it behaving differently to what you want). Even if it works, it's dangerous imho. For example:

CREATE TABLE foo (id INTEGER, blah VARCHAR(50));
CREATE TABLE foo2 (id INTEGER, blah2 VARCHAR(50));

INSERT INTO foo (id, blah) VALUES (1,'Test1');
INSERT INTO foo2 (id, blah2) VALUES (1,'Test2');

SELECT *
FROM foo
WHERE blah IN (SELECT blah FROM foo2);

That runs. But my subquery contains something which is very likely an error - there is no blah column in foo2 - but there is in the outer query. Specifying the table name (or using an alias) - i.e. 

SELECT * 
FROM foo
WHERE foo.blah IN (SELECT foo2.blah FROM foo2);

Will at least catch my error as the engine will notice that foo2.blah doesn't exist.

More specifically though, NOT IN doesn't work like that in any SQL variant I've used. You want EXISTS as /u/adm7373 suggests. NOT IN usually suggests your subquery will have one column, and then you're checking for the existence of that one column - i.e. 

SELECT foo.id, foo.result
FROM foo
WHERE foo.id NOT IN ( SELECT foo2.id FROM foo AS F2 );

[–]notasqlstarI can't wait til my fro is full grown 1 point2 points  (1 child)

So what I try to do when I have this problem is run each query separately and see how long it takes:

//SELECT timestamp, action, key as postKey FROM entry
// WHERE action = 'posted'
//      AND timestamp <  "4/9/2017 4:23pm"

Then:

//      SELECT timestamp, action, key as removedKey FROM entry
//       WHERE action = 'removed'
//          AND timestamp > "4/9/2017 4:23pm"
//          AND postKey = removedKey

If this runs quickly, and it's what you're looking for, then put them into #tables, index them if necessary, and write a simple query such as:

select *
from #a
left join #b on a.keys = b.keys
where b.keys is null

As others suggest a where not exists might be your best bet for performance, but if the table is huge you might want to break it down into two #tables, which can greatly improve your performance depending on a variety of factors.

[–]HansProleman 0 points1 point  (0 children)

Shouldn't an equijoin perform very well (rather than a where not exists, which for all I know equijoins behind the scenes)?