Oracle syntax for subselect in FROM clause?

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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