5

This might be a novice question. But sometimes, I find it necessary to use things like (SELECT * FROM A) in doing relational algebra in SQL (instead of using the relation A itself).

For example, when trying to work on the UNION of two relations A and B having the same structure, the following

SELECT * FROM (A UNION B) t;

generates a syntax error (with PostgreSQL 9.x).

ERROR:  syntax error at or near "UNION"

And I had to do

SELECT * FROM ((SELECT * FROM A) UNION (SELECT * FROM B)) t;

My questions are:

Is the verbosity of the subquery (SELECT * FROM A) really necessary in the SQL language (or am I missing something about better way to write it)? Why can't the relations A and B be used directly here?

Is (SELECT * FROM A) equivalent to A in relational algebra? What does the expansion (SELECT * FROM A) buy us? When is the long form required?

7

If you want shorter syntax in SQL, there is the TABLE name syntax that you can use - where name is a table name (base table or view or cte):

SELECT * FROM ((TABLE A) UNION (TABLE B)) t ;

The parentheses are not needed:

SELECT * FROM (TABLE A UNION TABLE B) t ;

and you could even write:

TABLE A UNION TABLE B ;

Regarding whether the syntax is ISO/ANSI SQL, I found this in a (copy of a) draft of SQL 2011:

6.41 <multiset value constructor>

Function

Specify construction of a multiset.

Format

<multiset value constructor> ::=
    <multiset value constructor by enumeration>
    | <multiset value constructor by query>
    | <table value constructor by query>

<multiset value constructor by enumeration> ::=
    MULTISET <left bracket or trigraph> <multiset element list> <right bracket or trigraph>

<multiset element list> ::=
    <multiset element> [ { <comma> <multiset element> }... ]

<multiset element> ::=
    <value expression>

<multiset value constructor by query> ::=
    MULTISET <table subquery>

<table value constructor by query> ::=
    TABLE <table subquery>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.