嵌套 SELECT “作品"使用不存在的列时 - 为什么? [英] Nested SELECT "works" when using nonexistant column - why?

问题描述

伙计们，我在 SQLite 中有以下查询:

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 是另一个表格.有趣的部分是:Address 没有 username 列！！

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

详情:

• 结果:查询在 0.004 秒内完成
• 如果我修改 username 部分(例如到 userrname)，我会收到 no such column 错误，这完全没问题
• 它永远不会返回任何结果，即使我在子选择中将 username 替换为 mail_username(实际上存在) - 这完全奇怪，因为它确实应该.

• 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?

推荐答案

您正在从 check_table 中选择 username，而不是从 address> 表.

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

尝试添加别名并查看:

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;

我敢打赌，如果您尝试使用 select a.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.

这篇关于嵌套 SELECT “作品"使用不存在的列时 - 为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！