Inner join and outer join

vidyasaraswathi · New User Joined: 10 May 2005 Posts: 72

Hi,
Please explain me the difference between inner join and different outer joins.

Thanks in Advance,
Vidya Bhat

kkumarv · Posted: Fri May 27, 2005 4:28 pm

A JOIN is any SELECT statement that has more than one DBMS object listed in its FROM clause. Whereas

Inner Join: combine information from two or more tables by comparing all values that meet the search criteria in the designated column or columns of one table with all the values in corresponding columns of the other table or tables. This kind of join which involve a match in both columns are called inner joins.

Outer join : Is one in which you want both matching and non matching rows to be returned. DB2 has no specific operator for outer joins, it can be simulated by combining a join and a correlated sub query with a UNION

An outer join includes rows from tables when there are no matching values in the tables. It is of two type Left Outer join and right Outer join depending upon non matching right(in case of Lt join) and left(in case of Rt join) subsequently non matching rows for them

vidyasaraswathi · New User Joined: 10 May 2005 Posts: 72

Hi Kumar,
Thank u for your quick reply.

Thanks and regards,
Vidya Bhat

jkbytes · Posted: Fri May 27, 2005 5:15 pm

Hi,

Kumar has given a nice description for Joins.

I'd like to add that the Outer joins are classified in to three types

Full in the sense retrieval of first matching rows from both table and non matching rows from left table and then non matching from right table.

vasanthanc · New User Joined: 01 Apr 2005 Posts: 58

order of retrieval would be the order in which you specify the subqueries for union, as there is no operator for outer join in db2 as already mentioned by Kumar

sandy_mcs · New User Joined: 01 Apr 2006 Posts: 17 Location: Pune

can anybody give me syntax for left outer join &rt outer join
thans you

ajay_diaz · New User Joined: 12 Sep 2005 Posts: 28

Hi Sandy,
The syntax is
SELECT COL1,COL2...
FROM TABLE1
LEFT OUTER JOIN TABLE2
ON
TABLE1.COL1=TABLE2.COL1
AND TABLE1.COL2= TABLE2.COL2
......
......

Tthe columns in the SELECT clause may not be the part of the table joining criteria.