joins

[Former Member]

hi

can any one tell me how many tables we can join

[vinod_gunaware2]

<b>Specifying Two or More Database Tables as an Inner Join</b>

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.

regards

vinod

[Former Member]

There is no limit , but depends upon database.

But for the SAP performace point of dont use join with so many tables. Instead of that select data from tables separately and store into internal tables then use FOR ALL ENTRIES for other select options.

Regards

Aman

Message was edited by: Amandeep Singh

[Former Member]

Hi,

I think there is no limit.But performance wise use min number of tables in SELECT...JOIN.

Cheers,

Bujji

[Former Member]

Hi

Yes there is a limit for making joins on database tables.

The maximum number of database tables you can use for joins is <b>25</b> tables.

if there are more than 3 or 4 tables for selecting the data then you have a to make a for all entries and proceed.

Do reward me the points....

Regards,

Sampath.

[Former Member]

Hello Pramod,

You can join as many tables as you want...But remember if you try to join more table then it could result in performance problem.

You can four types of joins.

1. Inner Join

2. Left Join

3. Right Join

4. Self Join

Make sure to join tables on primary keys...otherwise it will degrade performance.

let me know if you want any more info.

Cheers,

Nilesh

[Former Member]

Hi,

You can join any number of tables using the keyword INNER JOIN(or)JOIN, OUTER JOIN. But when you are trying to join multiple internal tables, you have to use it with appropriate paranthesis.

Thank you,

Aswin

[Former Member]

hii

there is a maximum limit on joins i.e. 25

Using joins places a heavy load on the database engine (although no heavier that if you coded a nested select), so be very parsimonious about the use of joins.

<b>The performance of the join depends on the database optimizer used especially if there are more than two tables used for joins.</b>

Try to give maximum number of conditions in the ON clause. This is because the ON conditions are evaluated first and the virtual table created as a result is the one on which the WHERE clause applies.

if there are more tables then probably go for all entries for performance

Thanks & Regards

Naresh