Question: Suppose i have 2 tables for user & task. have user\_id & status in task with status having possible values "complete" & "not complete".
Now I want to retrieve users who have not completed even 1 task. 
The most crude way is to first find users who have atleast 1 complete task and run a "not in" query.

Any better ways to achieve this without an "in" subquery. Please note that the data set is huge and i can't afford to have a lock on the task table for long time!

Answer: 
```
SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT * FROM tasks t
    WHERE t.user_id = u.user_id
    AND t.status = 'complete'
    );

```
When `task.user_id` cannot contain NULL (i.e. has a `NOT NULL` constraint), `LEFT JOIN` with `IS NULL` is your [best choice](http://planet.mysql.com/entry/?id=24888):

```
SELECT user.* FROM user
LEFT JOIN task ON (task.user_id = user.id AND task.status = 'complete')
WHERE task.user_id IS NULL

```