topic Re: Difference between inner join and outer join in Application Development and Automation Discussions

Difference between inner join and outer join

Former Member — Fri, 19 Jan 2007 06:34:28 GMT

1.Difference between inner join and outer join

2.wht is the difference in using hide and get crusor value in interactive.

3. Using join is better or views in writting program . Which is better.

Re: Difference between inner join and outer join

Former Member — Fri, 19 Jan 2007 06:40:56 GMT

Table 1 Table 2

|--|||--|

\ /

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

|--|||--|

\ /

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 |

|--||||||||--|

Regards

Prabhu

Re: Difference between inner join and outer join

Former Member — Fri, 19 Jan 2007 06:41:29 GMT

Check these to know the difference..

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

Re: Difference between inner join and outer join

Former Member — Fri, 19 Jan 2007 06:43:19 GMT

With an inner join, you only get those records which have an entry in all the tables included in the view.

With an outer join, on the other hand, those records that do not have a corresponding entry in some of

the tables included in the view are also selected.With an inner join, you only get those records which have an entry in all the tables included in the view.

Re: Difference between inner join and outer join

Former Member — Fri, 19 Jan 2007 06:54:56 GMT

for first one

http://help.sap.com/saphelp_nw2004s/helpdata/en/cf/21ec77446011d189700000e8322d00/content.htm

for second one

http://www.sts.tu-harburg.de/teaching/sap_r3/ABAP4/

for third one.

It depends upon the tables and the its data

for header atable and item table the Join is the best.

In some cases the view will come into picture

otherwise direct fetch of the data from tables using for allentries of theitab.

Thanks

Shiva

Re: Difference between inner join and outer join

Former Member — Fri, 19 Jan 2007 06:56:48 GMT

HI,

According to mathmatical term

Inner Join is equlent to Intersection ie. in join condition only those field take part which r available in both tables

Outer join is equlent to union ie. in join condition all of those field take part which r available in all tables in join condition.

HIDE

This statement stores - in the current list level - the content of the variable dobj together with the current list line whose line number is contained in sy-linno. The data type of the variables dobj must be flat and no field symbols can be specified that point to rows of internal tables, and no class attributes can be specified. The stored values can be read as follows:

For each user action in a displayed screen list that leads to a list result, all the row values stored using HIDE - that is, the row on which the screen cursor is positioned at the time of the event - are assigned to the respective variables.

If a list row of an arbitrary list level is read or modified using the statements READ LINE or MODIFY LINE, all the values of this row stored using HIDE are assigned to the respective variables.

REPORT zgaurav.

DATA: square TYPE i,

cube TYPE i.

START-OF-SELECTION.

FORMAT HOTSPOT.

DO 10 TIMES.

square = sy-index ** 2.

cube = sy-index ** 3.

WRITE / sy-index.

HIDE: square, cube.

ENDDO.

AT LINE-SELECTION.

WRITE: square, cube.

GET CURSER

If this statement is specified during PAI processing, depending on the specification for FIELD or LINE, it transfers either the name of the screen element or the number of the row of a table control or of the group of a step loop (on which the screen cursor is positioned after a user action) into the data objects field or line. For field, a character-type (before Release 6.10 flat) variable is expected. For line, a variable of the type i is expected. In field_properties, further properties of the screen element can be defined.

If the cursor is in the input field of the standard toolbar or on a pushbutton in the screen, the statement is ignored and the specified variables remain unchanged. If the cursor in the first variant is not in a screen element of the current dynpro but on a bar in the GUI status, the contents of field and the variables specified in field_properties are initialized. If the cursor in the second variant is not on a screen element that is part of a table control or a step loop, the variable line is initialized.

Re: Difference between inner join and outer join

Former Member — Fri, 19 Jan 2007 06:57:18 GMT

hi vighnesh,

1.The difference between an Inner join and outer join

inner join:

if two tabs outer tab and inner tab are present inner join checks all the matching records of outer tab in inner tab.

if matches displays the matched records.....

outer join: if even no records match all the records of the outer tab are displayed..

2.The HIDE statement is one of the fundamental statements for interactive reporting.

You use the HIDE technique when creating a basic list.

It defines the information that can be passed to subsequent detail lists.

Use the statements GET CURSOR FIELD and GET CURSOR LINE to pass the output field or output line on which the cursor was

positioned during the interactive event to the ABAP program.

3.Views have a small upper hand,

View are faster than abap-sql statements as the join conditions are available at the database level itself.

Data from several table can be fetched and can be shown as an output.

view can be used in other programs.

dbms creates the link between tables in join and can therefore be optimized.

views can be buffered as db tables

Inner joins always bypass the table buffer and read directly from the database. For this reason,

it is better to use a view from the ABAP Dictionary when accessing tables that are mostly only read.

a few sites u can go thru............

http://www.ecediinc.com/la/sap_abap4@yahoogroups.com/att-0465/Abap_performance_tuning.doc

http://www.sap-img.com/abap/a-sample-hide-get-cursor-in-interactive-programming.htm

http://help.sap.com/saphelp_di471/helpdata/EN/9f/dba42335c111d1829f0000e829fbfe/content.htm

hope this helps u ..all the best

*reward if helpful

regards,

sampath