OPEN SQL - Join Restrictions

[Former Member]

Hello,

Does anyone have a complete list of the restrictions/rules around using OUTER JOINS?

From an error message I know that a table can only be joined with a max of one other table using a left outer join. Does this mean that there can only be one LEFT OUTER JOIN in an entire SQL? I'm talking about the restrictions within one SQL statement rather then what can be done in ABAP with the use of internal tables and the like.

Thanks in advance,

Charles

[Former Member]

some info on outer join

... FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond

Effect

Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary.

In order to determine the result of a SELECT command where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left-hand table (tabref1) are then added to this table, and their corresponding fields from the right-hand table are filled with ZERO values. The system then applies the WHERE condition to the table.

Left outer join between table 1 and table 2 where column D in both tables set the join condition:

Table 1 Table 2

A

B

C

D

D

E

F

G

H

a1

b1

c1

1

1

e1

f1

g1

h1

a2

b2

c2

1

3

e2

f2

g2

h2

a3

b3

c3

2

4

e3

f3

g3

h3

a4

b4

c4

3

|--|||--|

\ /

\ /

\ /

\ /

\/

Left Outer Join

|--||||||||--|

| A | B | C | D | D | E | F | G | H |

|--||||||||--|

| a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 |

| a2 | b2 | c2 | 1 | 1 | e1 | f1 | g1 | h1 |

| a3 | b3 | c3 | 2 |NULL|NULL|NULL|NULL|NULL|

| a4 | b4 | c4 | 3 | 3 | e2 | f2 | g2 | h2 |

|--||||||||--|

Example

Output a list of all custimers with their bookings for October 15th, 2001:

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 = '20011015'

ORDER BY SCUSTOM_{NAME SBOOK}FLDATE.

WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID.

ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or using an alias.

Note

For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems:

Only a table or view may come after the JOIN operator, not another join statement.

The only logical operator allowed in the ON condition is AND.

Each comparison in the ON condition must contain a field from the right-hand table.

Comparisons in the WHERE condition must not contain a field from the right-hand table.

The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs).

[Former Member]

From the help on 'SELECT':

If there are columns in both tables with the same name,  
you must distinguish between them by prefixing the field  
descriptor with the table name, or using an alias.  

Note 
When you use a left outer join in the FROM clause of a  
SELECT command, it makes a crucial difference whether the
logical condition is in the ON or the WHERE clause. Since 
not all of the database systems supported by SAP 
themselves support the standard syntax and semantics of 
the left outer join, the syntax has been restricted to 
those cases that return the same solution in all database 
systems: 



Only a table or view may come after the JOIN statement,  
not another join expression 

The only logical operator allowed in the ON condition is  
AND 

Each comparison in the ON comdition must contain a field  
from the right-hand table 

Comparisons in the WHERE condition may not contain fields 
from the right-hand table 

You can only use EQ (or =) as comparisons in the ON  
condition 

The ON condition must contain at least one "real" JOIN  
condition (a condition containing a field from both  
tabref1 and tabref2) 



Note 

If you specify '*' as the field list in the SELECT 
clause, and an internal table or work area instead of a 
field list in the INTO clause, the fields are inserted 
into the target area in the sequence in which they occur 
in the table in the FROM clause but according to the 
structure of the target work area. This may, however, 
contain gaps due to alignment requirements. For this 
reason, you should define your work area with reference 
to the type of a database table instead of as a list of 
fields. An example of this appears later in the 
documentation. 

[Former Member]

Hi Charles,

1. U are right.

ORA-01417:

a table may be outer joined to at most one other table

2. This will not work (in oracle / or open sql in abap)

select e.emp_code

from emp_mst_hr e, dept_mst_hr d , desg_mst_hr dg

where e.dept_code = d.dept_code

and e.desg_code(+) = dg.desg_code

THE LAST LINE IS PROBLEMATIC

3. Becuase the table emp_mst_hr

can be out joined to ONLY one table

4. At the same time, this will work

select e.emp_code

from emp_mst_hr e, dept_mst_hr d , desg_mst_hr dg

where e.dept_code = d.dept_code

and e.desg_code = dg.desg_code

Bcos out join is on table desg_mst_hr

and not on emp_mst_hr

5. All this does not mean that there can

be only one outer join in the whole sql.

6. There can be many

7. But the point is,

ONE TABLE -

---

ONE OUTER JOIN

regards,

amit m.