Implementing Joins

Objectives

After completing this lesson, you will be able to:

  • Implement joins.
  • Differentiate between inner joins and outer joins.
  • Implement nested joins.

The Basics and Syntax of Joins

Definition of a Join

Sometimes it is necessary to combine two SQL data sources into one result set, consisting of columns of both data sources. This is called a join.

As an example, when reading flight connections, it can be necessary to read details on the flight carrier, too, for example, the name of the carrier.

The rows of the result set are determined by the join type and by join conditions between columns of the data sources. The join conditions start with keyword ON and define relations between columns of the data sources.

The join type is defined by the corresponding keyword. In ABAP SQL, supported join types are INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN.

We will discuss the different results of these join types soon. But let's have a look at the ABAP SQL syntax of joins, first.

The example combines carrier data (carrier ID and carrier name) and connection data (connection ID, departure airport, and destination airport) into one result set. The left data source is database table /DMO/CARRIER, whereas database table /DMO/CONNECTION serves as right data source. The keyword INNER JOIN between the two data source names specifies the join type.

The join condition after ON request the content of field CARRIER_ID of database table /DMO/CARRIER equals the content of field CARRIER_ID of database table /DMO/CONNECTION.

In the join conditions as well as in the field list, WHERE clause, and so on, the columns of data sources are fully identified by the name of the data source and the column name, separated by the tilde sign ( ~ ).

Note

From other SQL dialects, you may be used to the period or full stop sign ( . ). This had to be replaced by another sign in ABAP SQL, because the period sign already has the meaning of ending the statement.

It is possible to address the column names without the data source name. The pre-condition is that the column name only exists in one of the data sources and that the other data source does not contain a column of the same name.

In the example, only database table /DMO/CARRIER contains columns NAME and CURRENCY_CODE, whereas columns CONNECTION_ID, AIRPORT_FROM_ID, and AIRPORT_TO_ID only exist in database table /DMO/CONNECTION. Therefore, these column names are unique without preceding/DMO/CARRIER~ or /DMO/CONNECTION~.

On the other hand, both tables contain a column CARRIER_ID. Therefore, for this field /DMO/CARRIER~ or /DMO/CONNECTION~ is mandatory.

It is good practice to use aliases for the data source names to shorten the statement and increase readability. This is done using addition AS after the name of the data source. In the example, alias A is used for database table /DMO/CARRIER and alias C for database table /DMO/CONNECTION.

If a data source has an alias, it is mandatory to use the alias instead of the original data source name.

Required Aliases in Self-Joins

In some situations, aliases for data sources are more than just a syntax alternative. They become indispensable in statements that contain the same data source more than once.

Let's have a look at an example. Let's consider we have a table with flight connections (DMO/CONNECTION). We are interested in destinations that we can reach starting from Frankfurt International Airport (FRA). But instead of direct flights, we are looking for flights with one transfer.

The solution is a join of table /DMO/CONNECTION with itself. The first join condition requests that the second connection (2nd leg) starts from the airport at which the first connection (1st leg) ends. The second join condition, linked to the first with the AND operator, excludes combinations where the second connection is a direct return flight to the original departure airport.

The highlighted row in the result is a connection with LH 400 from Frankfurt (FRA) to New York (JFK) and from there with AA 0015 to San Francisco (SFO).

It is quite obvious that such a self-join would not work without the alias names a for the left usage of (DMO/CONNECTION and b for the right usage of /DMO/CONNECTION. Without the different aliases, it would not be possible to distinguish between the first connection and the second connection - neither in the join condition nor in the field list or WHERE clause.

Note

In this kind of self-join, the alias name is required in front of each column name because every column name exists in both data sources.

Try It Out: The Join Syntax

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code Snippet
    Copy code
    Switch to dark mode
    1234567891011121314151617181920
    
    SELECT FROM /dmo/carrier INNER JOIN /dmo/connection
*    SELECT FROM /dmo/carrier AS a INNER JOIN /dmo/connection AS c
             ON /dmo/carrier~carrier_id = /dmo/connection~carrier_id

         FIELDS /dmo/carrier~carrier_id,
                /dmo/carrier~name AS carrier_name,
                /dmo/connection~connection_id,
                /dmo/connection~airport_from_id,
                /dmo/connection~airport_to_id

          WHERE /dmo/carrier~currency_code = 'EUR'
           INTO TABLE @DATA(result).

    out->write(
      EXPORTING
        data   = result
        name   = 'RESULT'
    ).
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. Place the cursor in the first line (the line with keyword SELECT), and select CTRL + < to add a star sign (*) in the first column of that line.
  6. Remove the star sign (*) from the beginning of the next line. Analyze the syntax error that tells you that table name /DMO/CARRIER is obscured by an alias name.
  7. In the ON condition, replace table name /DMO/CARRIER with its alias name A and table name /DMO/CONNECTION with its alias name C.
  8. In the FIELDS list and the WHERE clause, remove the alias name and the tilde sign (~) in front of every field name where this is possible. You should find, that it is only mandatory in front of field carrier_id.
  9. Activate and execute again.

Join Types

Watch the following videos to know more about Inner Join, Right Outer Join and Left Outer Join.

This example illustrates the result of a left outer join. The two rows highlighted in the result set would not be there in an inner join. They correspond to carriers that do not have flight connections in database table /DMO/CONNECTION.

These rows only contain information about the carrier, that is, information from the left data source, table /DMO/CARRIER. The columns that usually contain information from the right data source, that is columns CONNECTION_ID, AIRPORT_FROM_ID, and AIRPORT_TO_ID, are filled with initial values. These values were entered by the ABAP runtime instead of the Null values returned by the database.

Note

The type of column CONNECTION_ID is based on ABAP built-in type N. The initial value for this type is not a series of blanks but a series of zeroes.

Try It Out: Inner Join and Outer Joins

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code Snippet
    Copy code
    Switch to dark mode
    1234567891011121314151617181920212223242526
    
 SELECT FROM /dmo/Agency AS a
                INNER JOIN /dmo/customer AS c
*           LEFT OUTER JOIN /dmo/customer AS c
*          RIGHT OUTER JOIN /dmo/customer AS c
             ON a~city         = c~city

         FIELDS agency_id,
                name AS Agency_name,
                a~city AS agency_city,
                c~city AS customer_city,
                customer_id,
                last_name AS customer_name

          WHERE ( c~customer_id < '000010' OR c~customer_id IS NULL )
            AND ( a~agency_id   < '070010' OR a~agency_id   IS NULL )

           INTO TABLE @DATA(result_Join).


    out->write(
      EXPORTING
        data   = result_join
        name   = 'RESULT_JOIN'
    ).
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.

    Note

    The code example retrieves pairs of customers and travel agencies with an address in the same city. It uses a WHERE clause to keep the result set small. So, with the INNER JOIN, only customer '000010' and travel agency '070010' are found. The WHERE clause is written in a form that works for outer joins, too. Conditions <field_name> IS NULL are needed to not exclude results with NULL values from the database.
  5. In the FROM clause of the SELECT statement, replace INNER JOIN with LEFT OUTER JOIN and activate again. Alternatively, you can comment out the line with INNER JOIN and uncomment the line with LEFT OUTER JOIN.
  6. Execute again, and compare the console output to the first output.
  7. In the FROM clause of the SELECT statement, replace LEFT OUTER JOIN with RIGHT OUTER JOIN and activate again. Alternatively, you can comment out the line with LEFT OUTER JOIN and uncomment the line with RIGHT OUTER JOIN.
  8. Execute again and compare the console output to the first two outputs.

Nested Joins

Join of Multiple Data Sources

In ABAP SQL, you can join more than two data sources at the same time. Let's begin with the join of two data sources, namely the join flight carriers and flight connections that we discussed earlier.

Now let's consider we want to add database table /DMO/AIRPORT to read the name and city of the departure airport (AIRPORT_FROM_ID).

To join the three data sources, we first surround the existing join with a pair of brackets. This first join now serves as left data source for the next join. We add the join keyword (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN), the right data source (/DMO/AIRPORT), an alias (AS apf), and the join condition for the additional join (ON c~airport_from_id = apf~airport_id).

Now let's consider we want to add database table /DMO/AIRPORT once again to also read the name and city of the destination airport (AIRPORT_TO_ID).

To implement this join of four data sources, we repeat what we did for the join of three data sources: We surround the existing join of three data sources with another pair of brackets and add the join keyword, right data source and join condition.

Note

You can repeat this until you reach the maximum number of data sources that can be linked to each other. Currently, up to 50 data sources (49 joins) are allowed in one SELECT statement.

Sequence of Joins

In the previous example, the next data source was always added on the right-hand side of the next join. The left data source was either a database table or a join. The right data source of all three joins was a database table.

Thanks to the brackets, it is also possible to use a join as the right data source of another join. In the lower example, we start with a join of /DMO/CONNECTION and /DMO/AIRPORT (AS apf). We surround it with brackets and use it as left data source of a join with /DMO/AIRPORT (AS apt). To add /DMO/AIRLINE, we surround the existing join with another pair of brackets. Then we add the left data source (/DMO/CARRIER) and the join keyword before the opening bracket and the ON-condition after the closing bracket.

Note

In our example, the two results are identical. But there are situations where the result can depend on the sequence in which the database evaluates the joins.

The Importance of Brackets

From a syntax point of view, the brackets are not necessary. But for the sake of readability, it is strongly recommended not to omit the brackets in nested joins. This should be illustrated by the following example.

The SELECT statement contains a join of four database tables but does not use any brackets. At a superficial glance, one could think that the join of /DMO/CARRIER and /DMO/CONNECTION is evaluated first because these tables are the first to appear in the FROM clause.

But this conclusion is wrong!

Without brackets, the sequence of evaluation is determined by the sequence of the ON conditions and not by the sequence of the data sources. On closer inspection, we see that the ON-condition that links tables /DMO/CARRIER (alias a) and /DMO/CONNECTION (alias c) to each other is placed at the very end of the FROM clause. Therefore, this join is the last to be evaluated.

Hint

Always use brackets in nested joins to avoid this kind of confusion.

Implement a Nested Join

In this exercise, you implement a nested join to read flight connections and airport details in the same SELECT statement.

Template:

  • /LRN/CL_S4D401_TCS_STRING_PROC (Global Class)

Solution:

  • /LRN/CL_S4D401_DBS_NESTED_JOIN (Global Class)

Task 1: Copy Template (Optional)

Copy the template class. If you finished the previous exercise, you can skip this task and continue editing your class ZCL_##_SOLUTION.

Steps

  1. Copy class /LRN/CL_S4D401_TCS_STRING_PROC to a class in your own package (suggested name: ZCL_##_SOLUTION, where ## stands for your group number).

    1. In the Project Explorer view, right-click class /LRN/CL_S4D401_TCS_STRING_PROC to open the context menu.

    2. From the context menu, choose Duplicate ....

    3. Enter the name of your package in the Package field. In the Name field, enter the name ZCL_##_SOLUTION, where ## stands for your group number.

    4. Adjust the description and choose Next.

    5. Confirm the transport request and choose Finish.

  2. Activate the copy.

    1. Press Ctrl + F3 to activate the class.

Task 2: Implement the Join

Adjust the implementation of method CLASS_CONSTRUCTOR of local class LCL_PASSENGER_FLIGHT. Extend the SELECT statement that reads from database table /LRN/CONNECTION. Add two joins to read the timezones of the departure airport and the destination airport.

Steps

  1. Navigate to the implementation of method CLASS_CONSTRUCTOR in local class LCL_PASSENGER_FLIGHT.

    1. For example, you can open the Local Types tab in the editor and search for METHOD class_constructor.

    2. Alternatively, you can expand ZCL_##_SOLUTION → LCL_PASSENGER_FLIGHT in the Outline view on the left and choose CLASS_CONSTRUCTOR.

  2. Find the SELECT statement that reads from database table /LRN/CONNECTION and add an alias for the data source (suggested alias name: c).

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      1234567
      
    SELECT
      FROM /lrn/connection 
    FIELDS carrier_id, connection_id,
           airport_from_id, airport_to_id, departure_time, arrival_time
      INTO TABLE @connections_buffer.

  3. Add a join with a appropriate JOIN type that reads details for the departure airport from database table /LRN/AIRPORT. Add an alias for the second data source (suggested alias name: f.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      1234567
      
    SELECT
      FROM lrn/connection AS c
    FIELDS carrier_id, connection_id,
           airport_from_id, airport_to_id, departure_time, arrival_time
      INTO TABLE @connections_buffer.

  4. Add the join condition.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678
      
    SELECT
      FROM /lrn/connection AS c
      LEFT OUTER JOIN /lrn/airport AS f
    FIELDS carrier_id, connection_id,
           airport_from_id, airport_to_id, departure_time, arrival_time
      INTO TABLE @connections_buffer.

  5. Add a similar join that reads details for the destination airport and add a different alias for the third data source (suggested alias name: t).

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789
      
    SELECT
      FROM /lrn/connection AS c
      LEFT OUTER JOIN /lrn/airport AS f
        ON c~airport_from_id = f~airport_id
    FIELDS carrier_id, connection_id,
           airport_from_id, airport_to_id, departure_time, arrival_time
      INTO TABLE @connections_buffer.

  6. Add the timezone of the departure airport and the timezone of the destination airport to the field list. Provide suitable aliases (suggested alias names: timzone_from and timzone_to).

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678910111213141516
      
    SELECT
      FROM /lrn/airport
    FIELDS airport_id, timzone
      INTO TABLE @DATA(airports).

    SELECT
      FROM /lrn/connection AS c
      LEFT OUTER JOIN /lrn/airport AS f
        ON c~airport_from_id = f~airport_id
      LEFT OUTER JOIN /lrn/airport AS t
        ON c~airport_to_id = t~airport_id
    FIELDS carrier_id, connection_id,
           airport_from_id, airport_to_id, departure_time, arrival_time
      INTO TABLE @connections_buffer.

  7. Navigate to the row type of static attribute connections_buffer and add two components for the timezones. Use the same names that you used as alias names in the field list of the SELECT statement.

    Caution

    Make sure you add the new components before component duration. This is important because the duration component is not filled in the SELECT statement. Alternatively, use the CORRESPONDING FIELDS OF addition in the INTO clause of the SELECT statement.

    1. Find the definition of structure type st_connections_buffer.

    2. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789101112
      
    TYPES:
      BEGIN OF st_connections_buffer,
        carrier_id      TYPE /dmo/carrier_id,
        connection_id   TYPE /dmo/connection_id,
        airport_from_id TYPE /dmo/airport_from_id,
        airport_to_id   TYPE /dmo/airport_to_id,
        departure_time  TYPE /dmo/flight_departure_time,
        arrival_time    TYPE /dmo/flight_departure_time,
       duration        TYPE i,
      END OF st_connections_buffer.

Task 3: Remove Redundant SELECT

In the implementation of method CLASS_CONSTRUCTOR of local class LCL_PASSENGER_FLIGHT, use the timezone information from the connection_buffer attribute in the calculation of the flight duration. Then remove the redundant read access to database table /LRN/AIRPORT.

Steps

  1. Return to the implementation of method CLASS_CONSTRUCTOR in local class LCL_PASSENGER_FLIGHT.

    1. Perform this step as before.

  2. In the TIME ZONE addition of the first CONVERT statement, replace the read access to local internal table airports with an access to component timzone_from of structure connection.

    1. Adjust the first CONVERT statement as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345
      
  CONVERT DATE today
          TIME connection-departure_time
          INTO UTCLONG DATA(departure_utclong).

  3. Adjust the second CONVERT statement in a similar way, using component timzone_to this time.

    1. Adjust the second CONVERT statement as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345
      
  CONVERT DATE today
          TIME connection-arrival_time
          INTO UTCLONG DATA(arrival_utclong).

  4. Scroll up and add comment signs in front of all code rows of the SELECT statement that reads from database table /LRN/AIRPORT.

    1. Scroll up to the beginning of the implementation of method CLASS_CONSTRUCTOR.

    2. Select the complete SELECT statement that reads from database table /LRN/AIRPORT and press Ctrl + <.

    3. The code should then look like this:

      Code Snippet
      Copy code
      Switch to dark mode
      123456
      
*    SELECT
*      FROM /lrn/airport
*    FIELDS airport_id, timzone
*      INTO TABLE @DATA(airports).

  5. Activate and test your global class as console app.

    1. Press Ctrl + F3.

    2. Press F9.

Log in to track your progress & complete quizzes