Explain Innerjoin and left outer join with example

[Former Member]

Regards

Gayathri

[Former Member]

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 ekko_{ebeln = ekpo}ebeln

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 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 ).

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 s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

AND p~cityfrom = p_cityfr.

LOOP AT itab INTO wa.

IF wa-connid = '0000'.

WRITE: / wa-carrid, wa-carrname.

ENDIF.

ENDLOOP.

[former_member386202]

Hi,

Outer join used to join the tables even there is no entry in all the tables used in the view.

Inner join used to join the tables but there should be an entry in all the table used in the view.

ex.

INNER JOIUN :-

SELECT a~vbeln "Billing document

a~fkdat "Billing date

a~fktyp "Billing category

a~fkart "Billing type

a~vtweg "Distribution channel

a~knumv "Number of doc condition

b~posnr "Billing item

b~matnr "Material Number

b~werks "Plant

b~vgbel "Referance

b~kzwi2 "Subtotal 2

b~wavwr "Cost in document currency

c~kunnr "Partner function

FROM vbrk AS a

INNER JOIN vbrp AS b

ON a_{vbeln EQ b}vbeln

INNER JOIN vbpa AS c

ON b_{vbeln EQ c}vbeln

INTO CORRESPONDING FIELDS OF TABLE it_vbrp

WHERE fkdat IN s_erdat

AND parvw EQ 'ZS'.

OUTER JOIN :-

SELECT a~vbeln "Billing document

a~fkdat "Billing date

a~fktyp "Billing category

a~fkart "Billing type

a~vtweg "Distribution channel

a~knumv "Number of doc condition

b~posnr "Billing item

b~matnr "Material Number

b~werks "Plant

b~vgbel "Referance

b~kzwi2 "Subtotal 2

b~wavwr "Cost in document currency

c~kunnr "Partner function

FROM vbrk AS a

OUTER JOIN vbrp AS b

ON a_{vbeln EQ b}vbeln

OUTER JOIN vbpa AS c

ON b_{vbeln EQ c}vbeln

INTO CORRESPONDING FIELDS OF TABLE it_vbrp

WHERE fkdat IN s_erdat

AND parvw EQ 'ZS'.

Regards,

Prashant

[Former Member]

Hi,

Look at this

Specifying Two or More Database Tables as an Inner Join

In a relational database, you normally need to read data simultaneously from more than one database table into an application program. You can read from more than one table in a single SELECT statement, such that the data in the tables all has to meet the same conditions, using the following join expression:

SELECT ...

...

FROM <tab> [INNER] JOIN <dbtab> [AS <alias>] ON <cond> <options>

...

where <dbtab> is a single database table and <tab> is either a table or another join expression. The database tables can be specified statically or dynamically as described above. You may also use aliases. You can enclose each join expression in parentheses. The INNER addition is optional.

A join expression links each line of <tab> with the lines in <dbtab> that meet the condition <cond>. This means that there is always one or more lines from the right-hand table that is linked to each line from the left-hand table by the join. If <dbtab> does not contain any lines that meet the condition <cond>, the line from <tab> is not included in the selection.

The syntax of the <cond> condition is like that of the WHERE clause, although individual comparisons can only be linked using AND. Furthermore, each comparison must contain a column from the right-hand table <dbtab>. It does not matter on which side of the comparison it occurs. For the column names in the comparison, you can use the same names that occur in the SELECT clause, to differentiate columns from different database tables that have the same names.

The comparisons in the condition <cond> can appear in the WHERE clause instead of the ON clause, since both clauses are applied equally to the temporary table containing all of the lines resulting from the join. However, each join must contain at least one comparison in the condition <cond>.

Specifying Two or More Database Tables as a Left Outer Join

In an inner join, a line from the left-hand database table or join is only included in the selection if there is one or more lines in the right-hand database table that meet the ON condition <cond>. The left outer join, on the other hand, reads lines from the left-hand database table or join even if there is no corresponding line in the right-hand table.

SELECT ...

...

FROM <tab> LEFT [OUTER] JOIN <dbtab> [AS <alias>] ON <cond> <options>

...

<tab> and <dbtab> are subject to the same rules and conditions as in an inner join. The OUTER addition is optional. The tables are linked in the same way as the inner join with the one exception that all lines selected from <tab> are included in the final selection. If <dbtab> does not contain any lines that meet the condition <cond>, the system includes a single line in the selection whose columns from <dbtab> are filled with null values.

In the left outer join, more restrictions apply to the condition <cond> than in the inner join. In addition to the above restrictions:

EQ or = is the only permitted relational operator.

There must be at least one comparison between columns from <tab> and <dbtab>.

The WHERE clause may not contain any comparisons with columns from <dbtab>. All comparisons using columns from <dbtab> must appear in the condition <cond>.

[Former Member]

Inner join

It ll take rows dat r common to both the tables in da join..

eg

table 1 has data table 2 has data

1 ABC 1 ABC

2 BCD 2 BCD

3 DEF

So if u Inner join dese 2 tables ull get common data betn dem which is

1 ABC

2 BCD

while the third record willl omitted..

Left Outer Join

It ll take all records form left table n only matching records from right table

eg

table 1 has data table 2 has data

1 ABC 1 ABC

2 BCD 2 BCD

3 DEF 4 GHI

Its output will b

1 ABC

2 BCD

3 DEF

all records from left n matching records from right table

Hope dis helps..Reward if it does

[Former Member]

Hi,gayathri g .

I found this in SAP Library, I hope it will help you.

Inner join:

DATA: BEGIN OF WA,

CARRID TYPE SPFLI-CARRID,

CONNID TYPE SPFLI-CONNID,

FLDATE TYPE SFLIGHT-FLDATE,

BOOKID TYPE SBOOK-BOOKID,

END OF WA,

ITAB LIKE SORTED TABLE OF WA

WITH UNIQUE KEY CARRID CONNID FLDATE BOOKID.

SELECT P_{CARRID P}CONNID F_{FLDATE B}BOOKID

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM ( ( SPFLI AS P

INNER JOIN SFLIGHT AS F ON P_{CARRID = F}CARRID AND

P_{CONNID = F}CONNID )

INNER JOIN SBOOK AS B ON B_{CARRID = F}CARRID AND

B_{CONNID = F}CONNID AND

B_{FLDATE = F}FLDATE )

WHERE P~CITYFROM = 'FRANKFURT' AND

P~CITYTO = 'NEW YORK' AND

F_{SEATSMAX > F}SEATSOCC.

LOOP AT ITAB INTO WA.

AT NEW FLDATE.

WRITE: / WA-CARRID, WA-CONNID, WA-FLDATE.

ENDAT.

WRITE / WA-BOOKID.

ENDLOOP.

This example links the columns CARRID, CONNID, FLDATE, and BOOKID of the table SPFLI, SFLIGHT, and SBOOK, and creates a list of booking numbers for all flights from Frankfurt to New York that are not fully booked. An alias name is assigned to each table.

Left outer join:

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 S_{CARRID S}CARRNAME P~CONNID

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM SCARR AS S

LEFT OUTER JOIN SPFLI AS P ON S_{CARRID = P}CARRID AND

P~CITYFROM = 'FRANKFURT'.

LOOP AT ITAB INTO WA.

WRITE: / WA-CARRID, WA-CARRNAME, WA-CONNID.

ENDLOOP.

You can search in SAP Library,it will help you a lot.

Regards,

feng.