首页 文章

子查询中忽略表名

提问于
浏览 1038
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抱怨未知列 .

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 .

    问候,

    詹姆士

相关问题