Sql ONLY keyword

Question

In SQL grammar I found strange rule that suggest that select * from ONLY (t1) is valid SQL.

My question is: what does ONLY mean in this context?

It is in "7.6 table reference" section of spec:

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

@juergend This is part of SQL Standard - Optional features: 2.1.2.145 S111, ONLY in query expressions — Lukasz Szozda
– Lukasz Szozda, Commented Jun 4, 2018 at 13:23
@juergend I'm have no specific engine. It is in SQL 2003 spec. I try to create generic sql parser. — talex
– talex, Commented Jun 4, 2018 at 13:28
The standard says: The <table reference> references the table that consists of every row of T. If ONLY is specified, then the <table reference> references the table that consists of every row in T, except those rows that have a subrow in a proper subtable of T. But I never came across that keyword being used anywhere. — juergen d
– juergen d, Commented Jun 4, 2018 at 13:34

user330315 · Accepted Answer · 2018-06-04 13:54:38Z

15

The ONLY keyword is used to restrict the tables used in a query if the table(s) participate in table inheritance.

Further down in the specs it is explained as:

If ONLY is specified, then the result of TP is a table that consists of every row in T, except those rows that have a subrow in a proper subtable of T

To my knowledge this is currently only supported by Postgres

The effect can be seen with the following example:

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');

The following:

select *
from base;

returns:

id | some_data
---+----------
 1 | base     
 2 | child

Whereas the following:

select *
from only (base);

returns:

id | some_data
---+----------
 1 | base

Online example: http://rextester.com/JVUM87016

answered Jun 4, 2018 at 13:54

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

talex Over a year ago

World is full of wonders. Use SQL for 10 years and only today I learn that it support inheritance.

user330315 Over a year ago

@talex: if you had used Postgres you would already have known ;)

Collectives™ on Stack Overflow

Sql ONLY keyword

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related