what is joins?where we use it. is it DD concept? abap

[Former Member]

hi abapers,

what is joins?where we use it. is it DD concept?

regards,

anjan

[dani_mn]

HI,

Joins are used when we want to fetch data from more than one table by comparision of some field values from the joined tables.

this is database concepts. and is a optimize way of fetching data.

JOINS are alternative of NESTED SELECT queries.

see the example below

Here is a join between vendor master table 'LFA1' and address table 'ADRC'. on address number field.

  select li~lifnr li~name1
         li~name2 li~name3
         li~name4 li~sortl
         li~stras ad~sort2
  into   table ven_mas
<b>  from   lfa1 as li
  join   adrc as ad</b>
  on     li~adrnr = ad~addrnumber.

Regards,

[Former Member]

Hi,

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.

P.S. mark all helpful answers for points.

JLN

[Former Member]

Really there are two types, inner join and left outter join. Inner join is when you know that there is a 1.1 or 1.n link between the tables. Here you will only get a hit if there is a link in the two tables. When it is not known whether there is a match in the second table, then here is where the left outer join comes in. It will still give you the values from the first table, even though it might not find a match in the second table, the fields from the second table will simply be blank. Which is most efficient is not really a question here as they provide two separate functionalities.

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

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

[Former Member]

Hi srinivas,

1. what is joins

It is nothing but a LINK

to CONNECT two two database tables,

in sql query,

so that data can be fetched from both tables,

having some COMMON field(s) between them.

2. where we use it.

We use it in SQL queries.

Such sql statements

are used in programs, views.

3. is it DD concept?

No. Its a normal database concept.

regards,

amit m.

[Former Member]

Hi,

instead of using nested loops and lots select statements

we can go for joins.

Inner join will match two or more tables with the given Key and will display the matched records...

Left outer join will match the records and will display all from left table...

Select a_{MATNR b}WERKS into itab

from Mara as a inner join Marc as B

on a_{MATNR = b}MATNR

where WERKS = 'NOKB'.

[Former Member]

BOSS try to give points someone..............:)

regs

rams

[anversha_s]

hi srinivas.

chk this simple example.

u will get good idea.

table emp

empno name

a sasi

b xxx

c yyy

table sal

empno salary

a 1000

b 2000

Inner join

****************

select e_{empno e}name

s~sal

into table int_table

from emp as e

inner join sal

on

e_{empno = s}empno.

if you made inner join between table a and b by emp no

the selection retrives only if the condition satisfy the output will be

a sasi 1000

b xxx 2000

Outer join

*************************

select e_{empno e}name

s~sal into table int_table

from emp as e

LEFT OUTER JOIN sal

on

e_{empno = s}empno.

if you made outer join (left /right ) the left table kept as it is the

if the condition satisfy the right table entries will fetch else leave it blank

the output will be

a sasi a 1000

b xxx b 2000

c yyy

rgds

anver

if helped mark points

[Former Member]

hi,

when u need information from more than one table we need joins. this is concept in <b>database management</b>.

example.

u need to know materials existing in different plants.

u join MARA and MARC to know all the plants in which the material exists.

SELECT a_{MATNR b}WERKS INTO CORRESPONDING FIELDS OF TABLE <ITAB> FROM mara AS a INNER JOIN marc AS b ON A_{MATNR = B}MATNR.

hope it helps...

reward if useful...

[Former Member]

Hi,

Joins are used if you need to get values from more than one tables and gets complicated when using more than three tables.

To overcome this you can use transaction SQ01, SQ02, SQ03 which is called SAP Query and can create joins for any number of tables dynamically.

Hope this migh thave helped you.

Thanks and Regards,

Prashanth