....What is the exact difference between innerjoin

[Former Member]

Hi...

1....What is the exact difference between innerjoin and outerjoin?when we use inner join and when we use outer join?what is the manditory condition to use these?

thanks and regards,

k.swaminath reddy

[Former Member]

Check these threads out. u will find all the info u need on joins...

reward if helpful

Regards,

K

[former_member189629]

Check these threads out. u will find all the info u need on joins...

reward if helpful

Regards,

K

[paruchuri_nagesh]

hi

swaminath

inner join

The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

outer join

The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

Note

If the same column name occurs in several database tables in a join expression, they have to be identified in all remaining additions of the SELECT statement by using the column selector ~.

reward if u find use ful

regards

Nagesh.Paruchuri

[Former Member]

Hi swaminath

Table A

Field1(F1) Field2(F2)

A 10

B 30

C 20

Table B

Field 1(F1) Field2(F3)

A 'Test'

B 'Good'

For inner join , If I join table A and B with condition Field1(F1) of A is the same as the field1(F1) of B

Output

Table (After Join)

Field(F1) Field(F2) Field(F3)

A 10 'Test'

B 30 'Good'

For outter join , that mean even the data in the table A is not in the table B. we still would like this data so output

Table (After outer Join)

Field(F1) Field(F2) Field(F3)

A 10 'Test'

B 30 'Good'

C

Regards

Wiboon

[Former Member]

If you use an inner join between 2 tables you would get all records that match exactly in both tables. If you use an outer join you would receive all records from table 1, although there is a match or not in table 2.

Example: Imagine 2 tables (Customer, Company)

TABLE Customer:

- Id Customer

- Customer Name

- Id Company (belongs to). Imagine that this field can contain NULLS

TABLE Company:

- Id Company

- Company name

Customers:

1 John Wilkinson NULL

2 Josh Fisher 1

3 Joana Blade 2

Company:

1 Microsoft

2 SAP

INNER JOIN BETWEEN Customers and Company on field (Id Company) results:

2 Josh Fisher 1 Microsoft

3 Joana Blade 2 SAP

OUTER JOIN BETWEEN Customers and Company on field (Id Company) results:

1 John Wilkinson NULL NULL

2 Josh Fisher 1 Microsoft

3 Joana Blade 2 SAP

[Former Member]

Hi,

Inner join and left outer join are only different in the situation where one of the involved tables does not contain any suitable record which meets the join conditions.

With an inner join (table 1 inner join table 2), no record is included in the result set in this case. However, this means that the corresponding record from tables 1 is not considered in the results set.

With an left outer join (table 1 left outer join table2), exactly one record is included in the results set in this case´. In this record, the fields from table 1 contain the values of the record from table 1 and the fields from table 2 are all filled with the initial value.

The order of the operands is very important with a left outer join. This means that the following joins describes different result sets:

The sequence must be adhered to when defining a left outer join. For an inner join, the sequence is not important.

You can always use a left outer join when

...

1. it cannot be ensured that at least one suitable record is found in the involved table in accordance with the join conditions, and

2. you want to avoid records being included in the results set, since one of the tables returns no entry.

From all these considerations, we can make the conclusion that a left outer join is frequently advantageous. This is your best preference. We also want to accentuate that a left outer join is only to be used when it is really necessary. A left outer join has a significantly worse performance than a corresponding inner join and is thus subject to certain restrictions (see functions).

If a left outer join is used, the following restriction applies to the right table (right operand):

· only join conditions with exactly one other table are allowed to be defined and

· this table in turn is not allowed to be a right table (right operand) of a left outer join.

Tables connected with left outer joins always form-figuratively speaking- the end of a chain of tables. In this ways as many tables as you want can be linked in an InfoSet with a left outer join to a core of tables that are connected using inner joins.

The restrictions made for the definition of left outer joins are due to technical limitations of the databases. These restrictions are not valid for inner joins.