1

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?

flypenguin
  • 655
  • 2
  • 7
  • 21
  • I can't imagine that this is actually reproducible. What happens when you run `SELECT username FROM Address` on the command line? Does that return anything or give an error? – Tim Biegeleisen May 31 '19 at 10:14
  • This code works also in MySql, SQL Server, Postgresql etc. `username` is the column from `check_table` – forpas May 31 '19 at 10:20
  • `check_table` has the column and without explicitly telling the parser which table it should take for a column it searches from most local, here `address`, to most global, here `check_table`, and voilá, there is a column `username` found in `check_table`. To circumvent that explicitly qualify columns like `.`.
     – sticky bit May 31 '19 at 10:22

1 Answers1

2

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.

Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • that's the thing :) . I had no idea about lookup rules in SQL. don't understand the use case, too - but I'm also a SQL noob ;) . Thanks! – flypenguin May 31 '19 at 17:43
  • the really annoying thing is - it still does not return any results. but when I do this: `select ct.username, a.mail_username from check_table as ct left join Address as a` I get a LOT of `NULL`s on the right side. – flypenguin May 31 '19 at 17:51