SQL Subquery in FROM clause

[Former Member]

Hi,

some of my complex extractions that I am currently working on require nested SELECT statements. As far as I can see OpenSQL only supports subqueries in the WHERE clause, not in the FROM clause. Is this assumptions correct or is there a way around it? I have managed to use Native SQL for these nested expressions but the preference of the client is to use OpenSQL.

Any ideas?

Thanks,

Tobias

[Former Member · Accepted solution]

Hi

In from clause you can use join.

You can read data from more than one database table in a single SELECT statement by using inner or left outer joins in the FROM clause.

The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server. Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need. Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data.

The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables. However, joins are nearly always quicker than using nested SELECT statements.

thanks

jagan