topic Re: select or inner join in Application Development and Automation Discussions

select or inner join

Former Member — Mon, 05 May 2008 17:13:29 GMT

hi all,

i have to extract data from 3 different tables.

is a single inner join better option or separate select statements ?

thanks

Re: select or inner join

Former Member — Mon, 05 May 2008 17:17:13 GMT

Hi,

Inner join used upon 3 tables is fine and the performance is also better. But dont use for more than 3 as the efficiency degrades.

Below is the sample code for an inner join on 3 tables.

SELECT stpo~stlnr stpo~idnrk mast~matnr mara~mtart stpo~menge  
INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast  
JOIN stpo ON stpo~stlnr = mast~stlnr  
JOIN mara ON mara~matnr = mast~matnr  
WHERE stpo~stlty = 'M' "AND stpo~idnrk IN s_matnr  
AND mast~werks = 1000.

Pls reward if useful.

Thanks,

Sirisha.

Re: select or inner join

Former Member — Mon, 05 May 2008 17:17:30 GMT

hi check this..

for all entries is always better than the joins ..so kindly use the for all entries in the select statements..you can check the response time in the ST05 tcode.

regards,

venkat

Re: select or inner join

former_member156446 — Mon, 05 May 2008 17:25:37 GMT

If you are using Inner join u need to join with all the possible keys...then its affeciant, else using for all entries is fine..

Re: select or inner join

Former Member — Mon, 05 May 2008 17:29:06 GMT

Hi,

Better to go with the FOR ALL ENTRIES.

Thanks,

Sriram Ponna.

Re: select or inner join

Former Member — Mon, 05 May 2008 17:30:46 GMT

Hi,

If there are common key fields in the three tables, then inner join is a better option else its better to use for all entries to fetch the data.

If you want an example to join 3 tables, you can find it at this path in SAP

ABAPDOCU(Transaction)~~>ABAP Database Access~~>Open SQL~~>Read data~~>Inner Join.

you can find the following code*

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.

Reward if helpfull

Regards,

Adithya M