首页 文章

子查询中忽略表名

提问于
浏览 次
0

我有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抱怨未知列 .

mysql subquery

1 回答

  • 0

    该表可以在子查询中引用为 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 .

    问候，

    詹姆士

    回复于

相关问题