Skip to Content

ABAP SQL | Client handling in Joins

Hi all,

while reading the HA400 training course script (course version 12) I encountered following passage:

At first I thought: wow, never heard of it, apparently all our joins are badly wrong! But since this passege describes changes as of 7.4, I just tried it in a 7.0 system. I created two tables - head and items, then I filled both of them, but the first in one client, and the second in another client. No matter in which client and whether inner or outer join - I'm getting no data from the other client.

So what does this improvement mean and is it reasonable in systems < 7.4 to add client field to the on-condition?

I could not find any clues in the documentation so I would be very thankful, if one of You experts could put some light on this mysterious improvement.

Best regards

Michał

Add comment
10|10000 characters needed characters exceeded

1 Answer

  • Posted on Mar 25 at 08:06 AM

    In versions < 7.40 maybe the SELECT sent to the database was only including the client selection (example for client 100):

    FROM t1 INNER JOIN t2 
    ON ... 
    WHERE t1~client = '100' and t2~client = '100' ...    " <====== only here

    In versions >= 7.40 the join between clients has been added:

    FROM t1 INNER JOIN t2 
    ON t1~client = t2~client AND ...                     " <====== here (new in 7.40)
    WHERE t1~client = '100' and t2~client = '100' ...    " <====== and here 

    The returned rows are the same for both queries BUT the database may sometimes better optimize the query if the join includes explicitly t1~client = t2~client. It's what is explained in the consulting note 621640 - SELECT with JOIN and automatic client handling:

    • "When you use a SELECT statement with a JOIN statement for two or more tables, the database access is slower than expected."
    • "To assist the database optimizer, you can add a comparison in the 'TAB1~MANDT equals TAB2~MANDT' format to the ON condition."
    Add comment
    10|10000 characters needed characters exceeded