子查询中忽略表名

我有2张 table : table1 +-----------------+------------+--------+-------+-----------+--------- + | Field | Type | Null | Key | Default | Extra | |-----------------+------------+--------+-------+-----------+--------- | | id | binary(16) | NO | | <null> | | +-----------------+------------+--------+-------+-----------+---------+

table2 +-----------------+------------+--------+-------+-----------+---------+ | Field | Type | Null | Key | Default | Extra | |-----------------+------------+--------+-------+-----------+--------- | | id | binary(16) | NO | | <null> | | +-----------------+------------+--------+-------+-----------+---------+

为什么这个查询有效(并从table1中检索所有记录)? select * from table1 where id in (select table1.id from table2);

如果我只这样做: select table1.id from table2 MySQL抱怨未知列 .

回答(1)

2 years ago

该表可以在子查询中引用为 table1 ,因为它已在外部查询中被引用,除非在外部查询中引用了表,该引用对子查询不可用,请参阅此示例:

CREATE TABLE table1 (field CHAR(1));
INSERT INTO table1 VALUES ('A'), ('A'), ('A');

CREATE TABLE table2 (field CHAR(1));
INSERT INTO table2 VALUES ('A'), ('B');

# Works as table1 has been referenced in the query
SELECT * FROM table1 WHERE field IN (SELECT table1.field FROM table2);

# Doesn't work as table1 now has the alias 'alias', so table1 is not available to reference by this name in the subquery
SELECT * FROM table1 AS alias WHERE field IN (SELECT table1.field FROM table2);

# Works as table1 now has the alias 'alias', so it can be referred to as such in the subquery
SELECT * FROM table1 AS alias WHERE field IN (SELECT alias.field FROM table2);

查看官方文档here .

问候,

詹姆士