SELECT 和 TSelectSqlStatement类

各种数据库中使用的SELECT语句, 在SQL ANSI中对应<query specifcation>,或更复杂的<query expression>。 我们常说的SUBQUERY,是<left paren> <query expression> <right paren>,代表从<query expression>衍生出来的一个标量、一行记录、或一个表。


1. table expression

<table expression>te1, Specify a table or a grouped table.

<table expression> ::=
    <from clause>
    [ <where clause> ]
    [ <group by clause> ]
    [ <having clause> ]
    [ <window clause> ]

2. table reference

<table reference>tr1,tr2, Reference a table.

Since the result of a query is a Table derived by evaluating that query, not all SQL Tables have an explicit <Table name> – so SQL allows you to refer to any Table using a <Table reference>. (重点是SQL中的table不仅仅是数据库中存在的base table, 还有其它例如subquery, VALUES CLAUSE, function table等)。

<table reference> ::=
    <table factor>
    | <joined table>
<table factor> ::=
    <table primary> [ <sample clause> ]

3. query specifcation

<query specifcation>qs1, Specify a table derived from the result of a <table expression>.

<query specification> ::=
    SELECT [ <set quantifier> ] <select list> <table expression>

4. query expression

<query expression>qe1, Specify a table.

<query specifcation>的基础上,支持<with clause> 和 SET OPERATOR(UNION,EXCEPT,INTERSECT),同时支持<table value constructor>,<explicit table>.

<query expression>用括号括起来后,可以用在SQL中任何需要Table的地方。

<query expression> ::=
    [ <with clause> ] <query expression body>
<query expression body> ::=
    <query term>
    | <query expression body> UNION [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query term>
    | <query expression body> EXCEPT [ ALL | DISTINCT ]
    [ <corresponding spec> ] <query term>
<query term> ::=
    <query primary>
    | <query term> INTERSECT [ ALL | DISTINCT ] [ <corresponding spec> ] <query primary>
<query primary> ::=
    <simple table>
    | <left paren> <query expression body> <right paren>
<simple table> ::=
    <query specification>
    | <table value constructor>
    | <explicit table>    

5. subquery

<subquery>s1, Specify a scalar value, a row, or a table derived from a <query expression>.

<scalar subquery> ::=

<row subquery> ::=

<table subquery> ::=
<subquery> ::=
    <left paren> <query expression> <right paren>

GSP 中的对应类

SQL语句在GSP中都有对应的类,其中对应<query expression>的是TSelectSqlStatementTSelectSqlStatement也可以表示<query specification>,<table value constructor>及用SET OPERATOR连接的<query specification>

1. TSelectSqlStatement表示<query specification>

这是最常见的用法,TSelectSqlStatement类的属性包含<table expression>的各个组成部分<from clause>,<where clause>,<group by clause>,<having clause>,<window clause>,也可以包含<with clause>

2. TSelectSqlStatement表示 SET OPERATOR连接的<query specification>

这种情况下,只有TSelectSqlStatement getLeftStmt() and TSelectSqlStatement getRightStmt()可用,分别表示

SELECT boss_name, salary FROM Bosses
SELECT employee_name, salary FROM Employees;


SELECT boss_name, salary FROM Bosses
SELECT employee_name, salary FROM Employees;

其中,getLeftStmt()表示的TSelectSqlStatement可以再次表示SET OPERATOR连接的<query specification>,形成递归。而getRightStmt()表示的TSelectSqlStatement只能是<query specification>

public boolean isCombinedQuery()可以用来确定TSelectSqlStatement是否为SET OPERATOR连接。

3. TSelectSqlStatement表示<table value constructor>

<table value constructor>最长见的用法是在INSERT语句中,但也可以用在需要SELECT的地方。 这就是为什么<table value constructor>在GSP中是用TSelectSqlStatement类来表示的原因。


因此,当我们处理表示<table value constructor>TSelectSqlStatement类实例时,必须要小心, 因为很多SELECT语句中的元素这时在TSelectSqlStatement类实例中不存在, 访问这些类属性将会导致NullPointerException的错误。

isValueClause() 可以用来检查一个TSelectSqlStatement类是否表示<table value constructor>,如果是,用 TValueClause getValueClause()来获取进一步的详细内容。


  • te1. ISO/IEC 9075-2 (SQL/Foundation) 2006. p318, 7.4
  • tr1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p321, 7.6
  • tr2, crate-sql99.pdf, p462
  • qs1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p361, 7.12
  • qe1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p371, 7.13
  • s1, ISO/IEC 9075-2 (SQL/Foundation) 2006. p390, 7.15