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

hi

what is the inner join ,outrjoin ,leftouterjoin,rightoutrjoin with exmples

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Posted on Nov 29, 2007 at 05:11 PM

    Wikipedia" target="_blank">http://en.wikipedia.org/wiki/Join_%28SQL%29">Wikipedia has some good examples

    Message was edited by:

    Matthew Billingham

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 29, 2007 at 06:45 PM
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 07:33 AM

    Hi

    go through these links

    http://help.sap.com/saphelp_46c/helpdata/EN/50/a71ec8f65911d296390000e82de14a/content.htm

    http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm

    Inner join

    IF p_bsart IS INITIAL.

    SELECT ekko~bukrs

    ekko~lifnr

    ekko~ebeln

    ekko~waers

    ekko~bsart

    ekko~ekorg

    ekko~ekgrp

    ekpo~ebelp

    ekpo~txz01

    ekpo~matnr

    ekpo~werks

    ekpo~menge

    ekpo~meins

    ekpo~netpr

    ekpo~netwr

    INTO TABLE t_itab1 FROM

    ekko INNER JOIN ekpo ON ekkoebeln = ekpoebeln

    WHERE ekko~ebeln IN s_ebeln AND

    ekko~bukrs IN s_bukrs AND

    ekko~lifnr IN s_lifnr AND

    ekko~ekorg IN s_ekorg AND

    ekko~ekgrp IN s_ekgrp AND

    ekpo~matnr IN s_matnr.

    The difference between an INNER JOIN and an OUTER JOIN is the following. If a query on an INNER JOIN of VBAK (outer table) and VBAP (inner table) finds a record in VBAK but no matching records in VBAP, then no data is retrieved from the database because the inner table is empty. If you still want to keep VBAK rows for which there are no matching VBAP rows, you need to use the OUTER JOIN construct available in ABAP/4 Open SQL in 4.x..

    Hi

    Syntax

    ... [(] {dbtab_left [AS tabalias_left]} | join

    {[INNER] JOIN}|{LEFT [OUTER] JOIN}

    {dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

    Effect

    The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of [INNER] JOIN or LEFT [OUTER] JOIN . Depending on the type of join, a join expression can be either an inner ( INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

    On the left-hand side, either a single database table, a view dbtab_left, or a join expression join can be specified. On the right-hand side, a single database table or a view dbtab_right as well as join conditions join_cond can be specified after ON. In this way, a maximum of 24 join expressions that join 25 database tables or views with each other can be specified after FROM.

    AS can be used to specify an alternative table name tabalias for each of the specified database table names or for every view. A database table or a view can occur multiple times within a join expression and, in this case, have various alternative names.

    The syntax of the join conditions join_cond is the same as that of the sql_cond conditions after the addition WHERE, with the following differences:

    At least one comparison must be specified after ON.

    Individual comparisons may be joined using AND only.

    All comparisons must contain a column in the database table or the view dbtab_right on the right-hand side as an operand.

    The following language elements may not be used: BETWEEN, LIKE, IN.

    No sub-queries may be used.

    For outer joins, only equality comparisons (=, EQ) are possible.

    If an outer join occurs after FROM, the join condition of every join expression must contain at least one comparison between columns on the left-hand and the right-hand side.

    In outer joins, all comparisons that contain columns as operands in the database table or the view dbtab_right on the right-hand side must be specified in the corresponding join condition. In the WHERE condition of the same SELECT command, these columns are not allowed as operands.

    Resulting set for inner join

    The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

    Resulting set for outer join

    The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

    Example

    Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

    PARAMETERS: p_cityfr TYPE spfli-cityfrom,

    p_cityto TYPE spfli-cityto.

    DATA: BEGIN OF wa,

    fldate TYPE sflight-fldate,

    carrname TYPE scarr-carrname,

    connid TYPE spfli-connid,

    END OF wa.

    DATA itab LIKE SORTED TABLE OF wa

    WITH UNIQUE KEY fldate carrname connid.

    SELECT ccarrname pconnid f~fldate

    INTO CORRESPONDING FIELDS OF TABLE itab

    FROM ( ( scarr AS c

    INNER JOIN spfli AS p ON pcarrid = ccarrid

    AND p~cityfrom = p_cityfr

    AND p~cityto = p_cityto )

    INNER JOIN sflight AS f ON fcarrid = pcarrid

    AND fconnid = pconnid ).

    LOOP AT itab INTO wa.

    WRITE: / wa-fldate, wa-carrname, wa-connid.

    ENDLOOP.

    Example

    Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

    PARAMETERS p_cityfr TYPE spfli-cityfrom.

    DATA: BEGIN OF wa,

    carrid TYPE scarr-carrid,

    carrname TYPE scarr-carrname,

    connid TYPE spfli-connid,

    END OF wa,

    itab LIKE SORTED TABLE OF wa

    WITH NON-UNIQUE KEY carrid.

    SELECT scarrid scarrname p~connid

    INTO CORRESPONDING FIELDS OF TABLE itab

    FROM scarr AS s

    LEFT OUTER JOIN spfli AS p ON scarrid = pcarrid

    AND p~cityfrom = p_cityfr.

    LOOP AT itab INTO wa.

    IF wa-connid = '0000'.

    WRITE: / wa-carrid, wa-carrname.

    ENDIF.

    ENDLOOP.

    /community [original link is broken]

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 02:40 PM

    Syntax

    ... [(] {dbtab_left [AS tabalias_left]} | join

    {[INNER] JOIN}|{LEFT [OUTER] JOIN}

    {dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

    Effect

    The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of [INNER] JOIN or LEFT [OUTER] JOIN . Depending on the type of join, a join expression can be either an inner ( INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

    On the left-hand side, either a single database table, a view dbtab_left, or a join expression join can be specified. On the right-hand side, a single database table or a view dbtab_right as well as join conditions join_cond can be specified after ON. In this way, a maximum of 24 join expressions that join 25 database tables or views with each other can be specified after FROM.

    AS can be used to specify an alternative table name tabalias for each of the specified database table names or for every view. A database table or a view can occur multiple times within a join expression and, in this case, have various alternative names.

    The syntax of the join conditions join_cond is the same as that of the sql_cond conditions after the addition WHERE, with the following differences:

    At least one comparison must be specified after ON.

    Individual comparisons may be joined using AND only.

    All comparisons must contain a column in the database table or the view dbtab_right on the right-hand side as an operand.

    The following language elements may not be used: BETWEEN, LIKE, IN.

    No sub-queries may be used.

    For outer joins, only equality comparisons (=, EQ) are possible.

    If an outer join occurs after FROM, the join condition of every join expression must contain at least one comparison between columns on the left-hand and the right-hand side.

    In outer joins, all comparisons that contain columns as operands in the database table or the view dbtab_right on the right-hand side must be specified in the corresponding join condition. In the WHERE condition of the same SELECT command, these columns are not allowed as operands.

    Resulting set for inner join

    The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

    Resulting set for outer join

    The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

    Example

    Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

    PARAMETERS: p_cityfr TYPE spfli-cityfrom,

    p_cityto TYPE spfli-cityto.

    DATA: BEGIN OF wa,

    fldate TYPE sflight-fldate,

    carrname TYPE scarr-carrname,

    connid TYPE spfli-connid,

    END OF wa.

    DATA itab LIKE SORTED TABLE OF wa

    WITH UNIQUE KEY fldate carrname connid.

    SELECT ccarrname pconnid f~fldate

    INTO CORRESPONDING FIELDS OF TABLE itab

    FROM ( ( scarr AS c

    INNER JOIN spfli AS p ON pcarrid = ccarrid

    AND p~cityfrom = p_cityfr

    AND p~cityto = p_cityto )

    INNER JOIN sflight AS f ON fcarrid = pcarrid

    AND fconnid = pconnid ).

    LOOP AT itab INTO wa.

    WRITE: / wa-fldate, wa-carrname, wa-connid.

    ENDLOOP.

    Example

    Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

    PARAMETERS p_cityfr TYPE spfli-cityfrom.

    DATA: BEGIN OF wa,

    carrid TYPE scarr-carrid,

    carrname TYPE scarr-carrname,

    connid TYPE spfli-connid,

    END OF wa,

    itab LIKE SORTED TABLE OF wa

    WITH NON-UNIQUE KEY carrid.

    SELECT scarrid scarrname p~connid

    INTO CORRESPONDING FIELDS OF TABLE itab

    FROM scarr AS s

    LEFT OUTER JOIN spfli AS p ON scarrid = pcarrid

    AND p~cityfrom = p_cityfr.

    LOOP AT itab INTO wa.

    IF wa-connid = '0000'.

    WRITE: / wa-carrid, wa-carrname.

    ENDIF.

    ENDLOOP.

    Hope dis helps..

    Reward all helpful queries

    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.