2

According to Using Subqueries Oracle SQL accepts a subquery in the from-clause of a select statement, like

SELECT * FROM ( SELECT a FROM b );

However, looking at the SELECT documentation, I see no possibility to get to select/subquery in the from clause (e.g. from the rules table_reference or join_clause).

Am I missing something here, is this part of the SQL grammar documented elsewhere? Or is this another incomplete part of the documentation?

azrdev
  • 225
  • 1
  • 13

3 Answers3

4

In the documentation you linked, you can see that table_reference can be a query_table_expression which can be a ( subquery ).

Thilo
  • 257,207
  • 101
  • 511
  • 656
3

Oracle syntax diagrams are pretty thorough:

The names you are looking for are:

  • table-reference --> query-table_expression
  • query-table-expression --> subquery

It is a bit confusing because of the optional lateral.

I do wonder if the lateral is allowed everywhere that expression is allowed.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Every derived table must have its own alias.

You can use something like 

  SELECT * FROM b
  WHERE a >
     (SELECT a FROM b
      WHERE a='India')

Or simply use 

SELECT * FROM b
  WHERE (SELECT a FROM b)

But in this way, you will have more than 1 row.If you will not use Where clause

Sunny Mittal
  • 1
  • 1
  • 6
  • 2
    This is not true for Oracle. In Oracle you can have a derived table without giving it an alias name (which is quite convenient at times.) Your second query is invalid in Oracle, because Oracle's SQL doesn't know a boolean type. To be valid in other DBMS the subquery would have to return no more than one record and column `a` would have to be boolean. – Thorsten Kettner Oct 23 '17 at 12:35