1. Subquery

1) A subquery is contained within a SELECT, UPDATE or DELETE statement. A subquery is formulated in the WHERE or HAVING clause, to check whether data from various datatbase tables or views fulfills certain conditions.

2) A SELECT statement with a subquery has a more restricted syntax than a SELECT statement without a subquery: SELECT ...FROM dbtab [WHERE...]  [GROUP BY...] [HAVING...]. If the subquery returns one value, standard relational operators can be used, with the exception of LIKE and BETWEEN.

3) If a subquery is used with a relational operator rather than with EXISTS, only one column can be specified in the SELECT clause of the subquery . This can be a field the database table, or an aggregate expersssion.

4) In the example above, the goal is for the subquery to return several rows with one value each. If you want to compare all values returned,use In.

5) Subquery in which fields from the main query are used in the WHERE clause are called correlated subqueries. If several subqueries are nested, a subquery can use all fields from the subqueries above it in the hierarchy.

6) Always use positive formulations for subqueries if possible, Negative formulations can cause unperformant accesses to the database if there is no suitable index.

2. INNER JOIN

1) In an ABAP Join, database tables are joined using the ON clause. You can use aliases for the relevant tables, as shown in the example above. You can also specify field lists (projection) and Where clause (selection) for ABAP joins.

2) Ensure that you clearly allocate the field name in the SELECT clause, in the ON clause, and in the Where clause to a table or to an alias for a table. If the database does not support Standard SQL, the ON conditions are converted to Where conditions.

3) ABAP JOINs also bypass the SAP table buffering. To implement a JOIN on a buffered table, it may be best to define a buffered datatbase view on the table.

4) If you use parentheses, an ABAP JOIN can link more than two tables.Do not join too many tables, because this increases the complexity of processing the SQL statement on the database.

5) In the example above, an INNER JOIN is implemented. Therefore, the resulting set(with a blue frame) contains only the data records with entries for field KUNNR
in both VVBAK and KKNA1.