Inner join

[Former Member]

Hi ,

Please explain the concept of inner join with example

[Former Member]

inner join used for combin two table.

support u can fetch two table. u can write select query two time .data base fetch time take twotime read from db.

so using primary key we easy write inner join. but both table primary key and both field refer same field.

Inner Join and Outer Join

The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. With an outer join, records are also selected for which there is no entry in some of the tables used in the view.

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.

[Former Member]

hai,

Inner Join and Outer Join

The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. With an outer join, records are also selected for which there is no entry in some of the tables used in the view.

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.

Specifying Database Tables

The FROM clause determines the database tables from which the data specified in the SELECT clause is read. You can specify either a single table or more than one table, linked using inner or outer joins. The names of database tables may be specified statically or dynamically, and you can use alias names. You can also use the FROM clause to bypass the SAP buffer and restrict the number of lines to be read from the database.

"Database table" can equally mean an ABAP Dictionary view. A view links two or more database tables in the ABAP Dictionary, providing a static join that is available systemwide. You can specify the name of a view wherever the name of a database table may occur in the FROM clause.

The FROM clause has two parts - one for specifying database tables, and one for other additions:

SELECT... FROM <tables> <options>...

In <tables>, you specify the names of database tables and define joins. <options> allows you to specify further additions that control the database access.

Specifying Database Tables Statically

To specify the name of a database table statically, use the following:

SELECT... FROM <dbtab> [AS <alias>] <options> . ..

The database table <dbtab> must exist in the ABAP Dictionary. The AS addition allows you to specify an alternative name <alias> that you can then use in the SELECT; FROM, WHERE, and GROUP BY clauses. This can eliminate ambiguity when you use more than one database table, especially when you use a single database table more than once in a join. Once you have defined an alias, you may no longer use the real name of the database table

Specifying Database Tables Dynamically

To specify the name of a database table dynamically, use the following:

SELECT... FROM (<name>) <options> . ..

The field <name> must contain the name of a database table in the ABAP Dictionary. The table name must be written in uppercase. When you specify the name of a database table dynamically, you cannot use an empty INTO clause to read all of the columns into the work area <dbtab>. It is also not possible to use alternative table names.

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

Client Handling

As already mentioned, you can switch off the automatic client handling in Open SQL statements using a special addition. In the SELECT statement, the addition comes after the options in the FROM clause:

SELECT... FROM <tables> CLIENT SPECIFIED. ..

If you use this addition, you can then address the client fields in the individual clauses of the SELECT statement.

Disabling Data Buffering

If buffering is allowed for a table in the ABAP Dictionary, the SELECT statement always reads the data from the buffer in the database interface of the current application server. To read data directly from the database table instead of from the buffer, use the following:

SELECT... FROM <tables> BYPASSING BUFFER. ..

This addition guarantees that the data you read is the most up to date. However, as a rule, only data that does not change frequently should be buffered, and using the buffer where appropriate improves performance. You should therefore only use this option where really necessary.

Restricting the Number of Lines

To restrict the absolute number of lines included in the selection, use the following:

SELECT... FROM <tables> UP TO <n> ROWS. ..

If <n> is a positive integer, the system reads a maximum of <n> lines. If <n> is zero, the system reads all lines that meet the selection criteria. If you use the ORDER BY clause as well, the system reads all lines belonging to the selection, sorts them, and then places the first <n> lines in the selection set.

Examples

Specifying a database table statically:

REPORT demo_select_static_database.

DATA wa TYPE scarr.

SELECT *

INTO wa

FROM scarr UP TO 4 ROWS.

WRITE: / wa-carrid, wa-carrname.

ENDSELECT.

The output is:

The system reads four lines from the database table SCARR.

Specifying a database table dynamically:

REPORT demo_select_dynamic_database.

DATA wa TYPE scarr.

DATA name(10) TYPE c VALUE 'SCARR'.

SELECT *

INTO wa

FROM (name) CLIENT SPECIFIED

WHERE mandt = '000'.

WRITE: / wa-carrid, wa-carrname.

ENDSELECT.

A condition for the MANDT field is allowed, since the example uses the CLIENT SPECIFIED option. If NAME had contained the value ‘scarr’ instead of ‘SCARR’, a runtime error would have occurred.

Inner join:

REPORT demo_select_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:

REPORT demo_select_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.

The output might look like this:

The example links the columns CARRID, CARRNAME, and CONNID of the tables SCARR and SPFLI using the condition in the left outer join that the airline must fly from Frankfurt. All other airlines have a null value in the CONNID column in the selection.

If the left outer join is replaced with an inner join, the list looks like this:

Only lines that fulfill the ON condition are included in the selection.

[Former Member]

<b>INNER JOIN</b>

One or more lines on the right-hand table is linked to a line in the

left-hand table. Lines from the left-handed table

is only selected if they meet the ON criteria.

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.

Check the below help document.

http://help.sap.com/saphelp_46c/helpdata/EN/cf/21ec5d446011d189700000e8322d00/frameset.htm

Regards,

Maha

[Former Member]

Inner Join:

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:

JOIN Syntax:

SELECT <DBT1>~field1

<DBT1>~field2

<DBT1>~field3

<DBT2>~field4

<DBT2>~field5

<DBT1>~field6

INTO TABLE <ITAB>

FROM <DBT1> INNER[LEFT OUTER JOIN] <DBT2>

ON <DBT1>_{field1 = <DBT2>}field2 (Join Condition)

WHERE <Condition>. (Other than the common fields).

INNER JOIN:

Inner Join expression links each line of <DBT1> (Left hand side table/master table) with the lines in <DBT2> (Right hand side table/transactional table That meet the Join Condition. 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 <DBT2> does not contain any lines tthat meet the Join Condition, the line from <DBT1> is not included in the selection.

Rewards if useful,.

[Former Member]

Hi

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.

say lets have 2 tables VBAP and VBAK. they are as follows.

content of VBAK.

VBELN ERDAT ERNAM AUART

1001 20021011 10 12

1002 20021012 12 12

1003 20021011 13 14

Content of VBAP.

VBELN POSNR MATNR MATKL

1001 10 12 12

1002 13 12 14

1002 10 1 1

Now we have VBELN as the Key for these two tables. so we write the select query as

SELECT vbak~vbeln

vbak~erdat

vbak~ernam

vbak~auart

vbap~posnr

vbap~matnr

vbap~matkl

INTO TABLE tbl_values

FROM vbak JOIN vbap ON vbap_{vbeln = vbak}vbeln.

The output will be

VBELN ERDAT ERNAM AUART POSNR MATNR MATKL

1001 20021011 10 12 10 12 12

1002 20021012 12 12 13 12 14

1002 20021012 12 12 10 1 1

SYNTAX:

Select tab1_{field1 tab2}field2

into table t_table

from tab1

inner join tab2

on tab1_{field1 = tab2}field2

where field1 in s_field1.

copy paste the below program..

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.

rewards if useful,

[paruchuri_nagesh]

hi

REPORT ZINNERJOINS no standard page heading line-count 60(4).

TABLES : LFA1,EKKO,EKPO.

SELECT-OPTIONS : S_LIFNR FOR LFA1-LIFNR.

DATA : BEGIN OF ITAB OCCURS 0,

LIFNR LIKE LFA1-LIFNR,

NAME1 LIKE LFA1-NAME1,

EBELN LIKE EKKO-EBELN,

AEDAT LIKE EKKO-AEDAT,

EBELP LIKE EKPO-EBELP,

MENGE LIKE EKPO-MENGE,

END OF ITAB.

SELECT LFA1_{LIFNR LFA1}NAME1 EKKO_{EBELN EKKO}AEDAT EKPO_{EBELP EKPO}MENGE INTO TABLE ITAB FROM LFA1 INNER JOIN EKKO ON LFA1_{LIFNR = EKKO}LIFNR

INNER JOIN EKPO ON EKKO_{EBELN = EKPO}EBELN WHERE LFA1~LIFNR IN S_LIFNR.

LOOP AT ITAB.

WRITE:/ ITAB-LIFNR,ITAB-NAME1,ITAB-EBELN,ITAB-AEDAT,ITAB-EBELP,ITAB-MENGE.

ENDLOOP.

top-of-page.

write : / 'this is vendors report'.

reward for useful points

Regards

Nagesh.Paruchuri

[Former Member]

hi kumar..

with help of inner join we can retrive the common data from tables

we can use <b>'~'</b> as a key word

see database concepts........like primory key and foreign key relations