Nested SELECT “works” when using nonexistant column – why?

folks, I have the following query in SQLite:

select license, username from check_table where
(
    username not in (
        select username from Address
    )
) order by license, username;

Address is another table. The fun part is: Address has no usernamecolumn!!

Details:

• Result: Query finished in 0.004 second(s)
• If I modify the username part (e.g. to userrname) I get a no such column error, which is totally fine
• it never returns any results, even when I replace username with mail_username (which actually exists) in the sub-select – which is totally strange, because it really should.

Now, my question is: Why don’t I get an error here?! And does it have something to do with the fact that I never get any results?

Answer

You’re selecting username from the check_table, not from the address table.

Try to add aliases and check it out:

select ct.license, ct.username 
from check_table as ct
where
(
    ct.username not in (
        select ct.username 
        from Address as a
    )
) order by ct.license, ct.username;

I bet if you will try to use select a.username... you’ll get an error about not existing column.

For this purpose, all the time when you’re using multiple tables in the query is good to use aliases.