a followup to my last question. Note this is not a duplicate, the other question was missing a WHERE clause which I have ;) .
I think I have two variants of the same thing, with a sanity check. My problem is - one returns nothing whereas the other one returns what I would expect. My problem is I don't understand why - can somebody help me out here?
Everything is SQLite3.
Variant 1:
select
z.license,
z.username
from
check_table as z
where
(
z.username not in (
select a.mail_username
from Address as a
)
)
order by
license, username;
Variant 2:
select
z.license,
z.username
from
check_table as z
where
(
not exists (
select 1
from Address as a
where z.username = a.mail_username
)
)
order by
license, username;
Sanity check:
select
z.username,
a.mail_username
from
check_table as z
left join Address as a
on z.username = a.mail_username
where
a.mail_username is null
order by
mail_username
Results:
- Variant 1 returns nothing, but should IMO (0.004 sec)
- Variant 2 returns 265 results (0.097 sec)
- Variant 3 returns 265 results (0.004 sec)
And I am completely lost, because NOT IN works just fine in other combinations and I just can't see the difference.
Update: Okay, it is a duplicate :D . To my defense - the actual original has a very nondescript name ;)
