topic Left Outer Join in Application Development and Automation Discussions

Left Outer Join

Former Member — Mon, 13 Feb 2012 16:31:55 GMT

Dear experts,

I would like to find all the rows of table "A" that are not stored in table "B".

In Oracle I would use the Left Outer Join specifying "B.whtaeverfield is null" in the WHERE clause.

Unfortunately it seems that this is not possibile in ABAP ("No fields from the right-hand table of a LEFT OUTER JOIN may appear in the WHERE contintion ...).

Have you ever faced this situation? How did you manage it (without splitting the query into two sub-queries)?

Thanks a lot.

Alex

Re: Left Outer Join

Former Member — Mon, 13 Feb 2012 17:20:03 GMT

Look at subquery technique (see tips & tricks button in transaction SE30...SQL Interface select over more than one table )...for example:

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

WHERE SEATSOCC < F~SEATSMAX

AND NOT EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID

AND CONNID = F~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT.

With subquery, using the not exists would return only the first table rows that did not match. A limitation of subquery is that you cannot retain any of the data from the second select....

Re: Left Outer Join

Clemenss — Mon, 13 Feb 2012 22:04:32 GMT

... and using formatted code without use of alias AS F makes it even nicer:


SELECT * FROM SFLIGHT INTO SFLIGHT_WA
  WHERE SEATSOCC < SFLIGHT~SEATSMAX
AND NOT EXISTS ( SELECT * FROM SPFLI
WHERE CARRID = SFLIGHT~CARRID
  AND CONNID = SFLIGHT~CONNID
  AND CITYFROM = 'FRANKFURT'
  AND CITYTO = 'NEW YORK' )
  AND FLDATE BETWEEN '19990101' AND '19990331'.
ENDSELECT.

Regards

Clemens

Re: Left Outer Join

Former Member — Tue, 14 Feb 2012 04:14:19 GMT

Hi,

In ABAP joins can be used for 3 tables if there are more than 3 tables it is better to go with For All Entries, And according to your requiorement left outer join is possible in ABAP with a select statement to ignore un necessary records use where condition.

Regards,

Goutam Kolluru.

Re: Left Outer Join

Former Member — Tue, 14 Feb 2012 10:44:22 GMT

hiii,

you can try this below example code of left outer join query.....

DATA: CUSTOMER TYPE SCUSTOM,
      BOOKING  TYPE SBOOK.

SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY
       SBOOK~FLDATE SBOOK~CARRID SBOOK~CONNID SBOOK~BOOKID
       INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
             BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
             BOOKING-BOOKID)
       FROM SCUSTOM LEFT OUTER JOIN SBOOK
         ON SCUSTOM~ID = SBOOK~CUSTOMID AND
            SBOOK~FLDATE = '20081015'
       ORDER BY SCUSTOM~NAME SBOOK~FLDATE.
  WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
           BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
           BOOKING-BOOKID.
ENDSELECT.

i hope from above example you vil get some idea.....

Re: Left Outer Join

Former Member — Tue, 14 Feb 2012 11:01:48 GMT

Hi,

Please check this link to get an idea about left outer join in ABAP.

http://help.sap.com/saphelp_470/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm

Regards.

Re: Left Outer Join

Former Member — Wed, 09 May 2012 07:01:01 GMT

Hello,

I have the same question but unfortunately none of these replies provide the answer.

As the Original Poster says, we would like to get the non-matching rows in an outer join.

Sure, you can do it with a subquery but that could mean 10,000 selects instead of 1.

Yes, you can do it in ABAP itself but that would involve retrieving 99% useless data from the database.

Is it not possible in ABAP SQL?

We know what an outer join is, thanks.

regards

Rick

Re: Left Outer Join

robert_shandley — Thu, 03 Apr 2014 10:00:39 GMT

Thanks Rick, for summing that up perfectly

Re: Left Outer Join

Former Member — Fri, 29 Apr 2016 10:06:11 GMT

A little late to the party but in case you're still looking for an answer. First do an outer join so you get the complete set of data, including the records that have a match (the ones you don't want). Then exclude those in the where clause by means of an inner 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.

gives as result:

AA American Airlines 0000

AB Air Berlin 0000

AC Air Canada 0000

AF Air France 0000

AZ Alitalia 0000

BA British Airways 0000

CO Continental Airlines 0000

DL Delta Airlines 0000

FJ Air Pacific 0000

JL Japan Airlines 0408

LH Lufthansa 0400

LH Lufthansa 0402

LH Lufthansa 2402

NG Lauda Air 0000

NW Northwest Airlines 0000

QF Qantas Airways 0006

SA South African Air. 0000

SQ Singapore Airlines 0000

SR Swiss 0000

UA United Airlines 3517

UA United Airlines 0941

select s~carrid, s~carrname, p~connid

into corresponding fields of table @itab

from scarr as s

inner join spfli as p on s~carrid = p~carrid and p~cityfrom = 'FRANKFURT'.

gives as result:

JL Japan Airlines 0408

LH Lufthansa 2402

LH Lufthansa 0402

LH Lufthansa 0400

QF Qantas Airways 0006

UA United Airlines 3517

UA United Airlines 0941

So if you exlude that set you get the following query:

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'

where s~carrid not in ( select s~carrid

from scarr as s

inner join spfli as p on s~carrid = p~carrid and p~cityfrom = 'FRANKFURT' ).

You end up with the result you're looking for, all entries that don't have a match:

AA American Airlines 0000

AB Air Berlin 0000

AC Air Canada 0000

AF Air France 0000

AZ Alitalia 0000

BA British Airways 0000

CO Continental Airlines 0000

DL Delta Airlines 0000

FJ Air Pacific 0000

NG Lauda Air 0000

NW Northwest Airlines 0000

SA South African Air. 0000

SQ Singapore Airlines 0000

SR Swiss 0000

Still a subquery but should be a lot more efficient since you only use 2 selects.