Joins 

Advantages:

• Joins are directly executed at the database level (Limits network traffic, increases efficiency)
  

    
Disadvantages:

• When Joins are used, the query is executed at the database level bypassing SAP’s internal database buffer
  

Restrictions:

• Joins can only be used for fields in transparent tables. The access to fields in Pool / Cluster tables is limited to fields that physically exist at the database level.

    
    Example: Use of INNER JOIN
    SELECT T1~MATNR T2~WERKS
           INTO TABLE T_MAT
           FROM MARA       AS T1
           INNER JOIN MARC AS T2
           ON T1~MATNR = T2~MATNR
           WHERE MATNR IN O_MATNR.
    
The initial design of the SQL statement with INNER JOINs should start with the smaller table as a general rule. However there can be some exceptions to this, based on the business data pattern and filter conditions:

• The starting table (Primary table in the join) should have specific where clause on the primary key fields or a clean secondary index. This will also be taken care of by the cost based optimizer in most of the cases
• The starting table (Primary table in the join) should be the one with maximum filter criteria. This reduces the database I/O and disk reads. However a comparison for response time and estimated cost is suggested in this case.

Use LEFT OUTER JOINs with caution since they can impact performance. A LEFT OUTER JOIN produces a resulting set containing all data records from the outer table, regardless of whether matching records exist in the inner table. If there are no matching data records in the inner table, NULL values are returned for the fields in the inner table.

The most inefficient way to query data in SAP is using the nested select.
Example:
    
    SELECT * FROM MARA
           WHERE MATNR IN O_MATNR.
    
      SELECT * FROM MARC
             WHERE MATNR = MARA-MATNR.
        
        SELECT * FROM MARD
               WHERE MATNR = MARA-MATNR
                AND  WERKS = MARC-WERKS   
    
        ENDSELECT.
      ENDSELECT.
    ENDSELECT.
    
Disadvantages:

• Connection to database stays open for each record in the outer select loop
• If the hit by the outer selection is large, there will be extremely high communication costs
• Logical Databases use nested selects in the background. Every GET statement equates to another select loop when the program is executed. Therefore, Logical Databases have the same disadvantages as do nested selects.

Do not use SELECT inside a LOOP.
Example:
    
    LOOP AT T_MAT.
      SELECT MATNR WERKS EKGRP FROM MARC
             INTO TABLE T_MARC
              WHERE MATNR = T_MAT-MATNR.
    ENDLOOP.
    
    SELECT MATNR FROM MARA
           INTO TABLE T_MARA
           WHERE MATNR IN O_MATNR.
    
Disadvantage:

• A SQL statement is executed for every record in the outer table

Related Items:

• Accessing data in Packages
• Describe Select-Options
• Displaying an IDoc in a report
• Dynamic selection screen based
• Dynamic selection screens