Skip to Content
author's profile photo Former Member
Former Member

hi gurus

How many types of table joins are there?

difference between inner join & outer join?

hi..can u give some examples for the joins....

Difference between "select * from mara" and "select

single * from mara"?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 29, 2007 at 07:43 AM

    "select single * " will pick only only a single record with all the fieleds

    "Select * " will pick all the records with all the fields.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 29, 2007 at 08:25 AM

    hi pavan,

    inner join: only matched records in both tables are given.

    outer join: matched records in both tables and records from 1st table are also displayed.

    select single * -> picks only the top most record from a table which satisfies the given condition.

    select * -> picks all the records from table that satisfies the given condition.

    if helpful reward some points.

    with reagrds,

    suresh babu aluri.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 29, 2007 at 07:45 AM

    http://en.wikipedia.org/wiki/Inner_join#Inner_join

    Select single : read the first occurence for the condition

    Select * : read all occurences for the condition

    regards,

    Hans

    Please reward all helpful answers !!!!!

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 29, 2007 at 07:47 AM
    ... FROM tabref1 [INNER] JOIN tabref2 ON cond 
    
    
    
    
    Effect 
    The data is to be selected from transparent database tables and/or views determined by tabref1 and tabref2. tabref1 and tabref2 each have the same form as in variant 1 or are themselves Join expressions. The keyword INNER does not have to be specified. The database tables or views determined by tabref1 and tabref2 must be recognized by the ABAP Dictionary. 
    
    
    
    In a relational data structure, it is quite normal for data that belongs together to be split up across several tables to help the process of standardization (see relational databases). To regroup this information into a database query, you can link tables using the join command. This formulates conditions for the columns in the tables involved. The inner join contains all combinations of lines from the database table determined by tabref1 with lines from the table determined by tabref2, whose values together meet the logical condition (join condition) specified using ON>cond. 
    
    
    
    Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same: 
    
    
    
    Table 1                      Table 2 
    |----|----|----|----|        |----|----|----|----|----| 
    | A  | B  | C  | D  |        | D  | E  | F  | G  | H  | 
    |----|----|----|----|        |----|----|----|----|----| 
    | a1 | b1 | c1 | 1  |        | 1  | e1 | f1 | g1 | h1 | 
    | a2 | b2 | c2 | 1  |        | 3  | e2 | f2 | g2 | h2 | 
    | a3 | b3 | c3 | 2  |        | 4  | e3 | f3 | g3 | h3 | 
    | a4 | b4 | c4 | 3  |        |----|----|----|----|----| 
    |----|----|----|----| 
    
                                / 
                               / 
                              / 
                             / 
                            / 
        Inner Join 
        |----|----|----|----|----|----|----|----|----| 
        | A  | B  | C  | D  | D  | E  | F  | G  | H  | 
        |----|----|----|----|----|----|----|----|----| 
        | a1 | b1 | c1 | 1  | 1  | e1 | f1 | g1 | h1 | 
        | a2 | b2 | c2 | 1  | 1  | e1 | f1 | g1 | h1 | 
        | a4 | b4 | c4 | 3  | 3  | e2 | f2 | g2 | h2 | 
        |----|----|----|----|----|----|----|----|----| 
    
    
    
    Example 
    Output a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out: 
    
    
    
    DATA: DATE   LIKE SFLIGHT-FLDATE, 
          CARRID LIKE SFLIGHT-CARRID, 
          CONNID LIKE SFLIGHT-CONNID. 
    
    SELECT F~CARRID F~CONNID F~FLDATE 
        INTO (CARRID, CONNID, DATE) 
        FROM SFLIGHT AS F INNER JOIN SPFLI AS P 
               ON F~CARRID = P~CARRID AND 
                  F~CONNID = P~CONNID 
        WHERE P~CITYFROM = 'FRANKFURT' 
          AND P~CITYTO   = 'NEW YORK' 
          AND F~FLDATE BETWEEN '20010910' AND '20010920' 
          AND F~SEATSOCC < F~SEATSMAX. 
      WRITE: / DATE, CARRID, CONNID. 
    ENDSELECT. 
    
    
    
    If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or a table alias. 
    
    
    
    Note 
    In order to determine the result of a SELECT command where the FROM clause contains a join, the database system first creates a temporary table containing the lines that meet the ON condition. The WHERE condition is then applied to the temporary table. It does not matter in an inner join whether the condition is in the ON or WHEREclause. The following example returns the same solution as the previous one. 
    
    
    
    Example 
    Output of a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out: 
    
    
    
    DATA: DATE   LIKE SFLIGHT-FLDATE, 
          CARRID LIKE SFLIGHT-CARRID, 
          CONNID LIKE SFLIGHT-CONNID. 
    
    SELECT F~CARRID F~CONNID F~FLDATE 
        INTO (CARRID, CONNID, DATE) 
        FROM SFLIGHT AS F INNER JOIN SPFLI AS P 
               ON F~CARRID = P~CARRID 
        WHERE F~CONNID = P~CONNID 
          AND P~CITYFROM = 'FRANKFURT' 
          AND P~CITYTO   = 'NEW YORK' 
          AND F~FLDATE BETWEEN '20010910' AND '20010920' 
          AND F~SEATSOCC < F~SEATSMAX. 
      WRITE: / DATE, CARRID, CONNID. 
    ENDSELECT. 
    
    
    
    Note 
    Since not all of the database systems supported by SAP use the standard syntax for ON conditions, the syntax has been restricted. It only allows those joins that produce the same results on all of the supported database systems: 
    
    
    
    Only a table or view may appear to the right of the JOIN operator, not another join expression. 
    
    Only AND is possible in the ON condition as a logical operator. 
    
    Each comparison in the ON condition must contain a field from the right-hand table. 
    
    If an outer join occurs in the FROM clause, all the ON conditions must contain at least one "real" JOIN condition (a condition that contains a field from tabref1 amd a field from tabref2. 
    
    
    
    Note 
    In some cases, '*' may be specified in the SELECT clause, and an internal table or work area is entered into the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the FROM clause, according to the structure of each table work area. There can then be gaps between table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, not simply by counting the total number of fields. For an example, see below: 
    
    
    
    Variant 3 
    ... FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond 
    
    
    
    
    Effect 
    Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary. 
    
    
    
    In order to determine the result of a SELECT command where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left-hand table (tabref1) are then added to this table, and their corresponding fields from the right-hand table are filled with ZERO values. The system then applies the WHERE condition to the table. 
    
    
    
    Left outer join between table 1 and table 2 where column D in both tables set the join condition: 
    
    
    
    Table 1                      Table 2 
    |----|----|----|----|        |----|----|----|----|----| 
    | A  | B  | C  | D  |        | D  | E  | F  | G  | H  | 
    |----|----|----|----|        |----|----|----|----|----| 
    | a1 | b1 | c1 | 1  |        | 1  | e1 | f1 | g1 | h1 | 
    | a2 | b2 | c2 | 1  |        | 3  | e2 | f2 | g2 | h2 | 
    | a3 | b3 | c3 | 2  |        | 4  | e3 | f3 | g3 | h3 | 
    | a4 | b4 | c4 | 3  |        |----|----|----|----|----| 
    |----|----|----|----| 
    
                                / 
                               / 
                              / 
                             / 
                            / 
        Left Outer Join 
        |----|----|----|----|----|----|----|----|----| 
        | A  | B  | C  | D  | D  | E  | F  | G  | H  | 
        |----|----|----|----|----|----|----|----|----| 
        | a1 | b1 | c1 | 1  | 1  | e1 | f1 | g1 | h1 | 
        | a2 | b2 | c2 | 1  | 1  | e1 | f1 | g1 | h1 | 
        | a3 | b3 | c3 | 2  |NULL|NULL|NULL|NULL|NULL| 
        | a4 | b4 | c4 | 3  | 3  | e2 | f2 | g2 | h2 | 
        |----|----|----|----|----|----|----|----|----| 
    
    
    
    Example 
    Output a list of all custimers with their bookings for October 15th, 2001: 
    
    
    
    DATA: CUSTOMER TYPE SCUSTOM, 
          BOOKING  TYPE SBOOK. 
    
    SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
           SBOOK~FLDATE SBOOK~CARRID SBOOK~CONNID SBOOK~BOOKID 
           INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY, 
                 BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID, 
                 BOOKING-BOOKID) 
           FROM SCUSTOM LEFT OUTER JOIN SBOOK 
             ON SCUSTOM~ID = SBOOK~CUSTOMID AND 
                SBOOK~FLDATE = '20011015' 
           ORDER BY SCUSTOM~NAME SBOOK~FLDATE. 
      WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY, 
               BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID, 
               BOOKING-BOOKID. 
    ENDSELECT. 
    
    
    
    If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or using an alias. 
    
    
    
    Note 
    For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems: 
    
    
    
    Only a table or view may come after the JOIN operator, not another join statement. 
    
    The only logical operator allowed in the ON condition is AND. 
    
    Each comparison in the ON condition must contain a field from the right-hand table. 
    
    Comparisons in the WHERE condition must not contain a field from the right-hand table. 
    
    The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs). 
    
    
    
    Note 
    In some cases, '*' may be specivied as the field list in the SELECT clause, and an internal table or work area is entered in the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the llen in der FROM clause, according to the structure of each table work area. There can be gaps between the table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, as in the following example (not simply by counting the total number of fields). 
    
    
    
    Example 
    Example of a JOIN with more than two tables: Select all flights from Frankfurt to New York between September 10th and 20th, 2001 where there are available places, and display the name of the airline. 
    
    
    DATA: BEGIN OF WA, 
            FLIGHT TYPE SFLIGHT, 
            PFLI   TYPE SPFLI, 
            CARR   TYPE SCARR, 
          END OF WA. 
    
    SELECT * INTO WA 
        FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P 
                 ON F~CARRID = P~CARRID AND 
                    F~CONNID = P~CONNID ) 
               INNER JOIN SCARR AS C 
                 ON F~CARRID = C~CARRID 
        WHERE P~CITYFROM = 'FRANKFURT' 
          AND P~CITYTO   = 'NEW YORK' 
          AND F~FLDATE BETWEEN '20010910' AND '20010920' 
          AND F~SEATSOCC < F~SEATSMAX. 
      WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID, 
               WA-FLIGHT-CONNID. 
    ENDSELECT.
    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      <b>Difference between "select * from mara" and "select

      single * from mara"?</b>

      select * from mara: will select all the rows and all the fields of mara .

      select single * from mara : will select only one rows and all the fields of mara .

  • Posted on May 29, 2007 at 07:57 AM

    hi,

    <u><b>table joins are there</b></u>

    table emp

    empno name

    a sasi

    b xxx

    c yyy

    table sal

    empno salary

    a 1000

    b 2000

    Inner join

    ****************

    select eempno ename

    s~sal

    into table int_table

    from emp as e

    inner join sal

    on

    eempno = sempno.

    if you made inner join between table a and b by emp no

    the selection retrives only if the condition satisfy the output will be

    a sasi 1000

    b xxx 2000

    Outer join

    *************************

    select eempno ename

    s~sal into table int_table

    from emp as e

    LEFT OUTER JOIN sal

    on

    eempno = sempno.

    if you made outer join (left /right ) the left table kept as it is the

    if the condition satisfy the right table entries will fetch else leave it blank

    the output will be

    a sasi a 1000

    b xxx b 2000

    c yyy

    <u><b>Difference between "select * from mara" and "select

    single * from mara"?</b></u>

    There are many different variations of the SELECT statement in ABAP.

    You have SELECT/ENDSELECT, SELECT Single, SELECT into TAble, inner joins, outter joins, etc.

    Here is some help.

    http://help.sap.com/saphelp_470/helpdata/en/fc/eb3983358411d1829f0000e829fbfe/frameset.htm

    Regards

    Anversha

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 29, 2007 at 08:36 AM

    Hi Pavan

    <b>Inner join</b>

    When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

    Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

    Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

     Select a~airln a~lnnam b~fligh b~cntry into table int_airdet
    
                From zairln as a inner join zflight as b on a~airln = b~airln.

    In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join

    Along this u can check this links

    http://help.sap.com/saphelp_46c/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm

    Rewards If helpfull

    Regards

    Pavan

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.