在SQL语法中,我发现了一个奇怪的规则,表明select * from ONLY (t1)
是有效的SQL。
我的问题是:在这种情况下ONLY
意味着什么?
它在规范的"7.6 表引用"部分:
<table primary> ::=
<table or query name> [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]
| <lateral derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]
| <collection derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]
| <table function derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]
| <only spec> [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]
| <left paren> <joined table> <right paren>
<only spec> ::= ONLY <left paren> <table or query name> <right paren>
ONLY 关键字用于限制查询中使用的表(如果表参与表继承(。
在规格的下方,它被解释为:
如果指定了 ONLY,则 TP 的结果是一个由 T 中的每一行组成的表,除了那些在 T 的正确子表中具有子行的行
据我所知,目前只有Postgres支持此功能
效果可以通过以下示例看到:
create table base (id integer, some_data varchar(100));
create table child () inherits (base);
insert into base values (1, 'base');
insert into child values (2, 'child');
以下:
select *
from base;
返回:
id | some_data
---+----------
1 | base
2 | child
而以下:
select *
from only (base);
返回:
id | some_data
---+----------
1 | base
在线示例:http://rextester.com/JVUM87016