Join Statements

[Former Member]

HI

PLZ TEL ME THE DIFFERENCE BETWEEN INERJOINS AND UTER JOINS

AND HOW TO WRITE CODE TO EXTRACT DATA FROM TWO TABLES

HOW TO MAKE THE LINK BETWEEN TWO OR THREE TABLES

PLZ TEL ME

THANK U IN ADVANCE

[gopi_narendra]

SELECT - join

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 additions not be used: NOT, 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.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

Regards

- Gopi

[Former Member]

simply inner join is intersection of two tables.

outer join is first table + intersection in second table.

If no value , filled with null entries.

Mostly inner joins are used for querying in reports. Only in analysis kind, the latter is used.

regards,

Sandeep Josyula

*Mark helpful answers

[Former Member]

Will try to keep it simple.

Joins:

  Table1 Values:
  Field1 Field2
  ABC    123
  BCD    234
  CDE    345

  Table2 Values:
  Field1 Field3
  ABC    lmn
  CDE    nop

  Inner Join: TABLE1 inner join TABLE2
     Returns values when the key record exists in both tables.
     Internal Table
      Field1 Field2 Field2
      ABC    123    lmn
      CDE    345    nop

 Here the record BCD is excluded as itz not existing in table2.

   Outer Join:
      Returns values irrespective of existence in second table.
      Internal Table
      Field1 Field2 Field3
      ABC    123    lmn
      BCD    234
      CDE    345    nop

   Here the record BCD is still extracted but leaves it as blank where the record is not found.

For all Entries:
   Itz almost the same as inner join but here the first1 is internal table. Internal Table <b>inner join</b> DB Table.

One more important thing to remember is while using the for all entries statement, we have to check that internal table is not initial otherwise it extracts all the details from the second database table.

Hope the above info can give you some idea.

Kind Regards

Eswar