Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Inner join and outer join

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vidyasaraswathi

New User


Joined: 10 May 2005
Posts: 72

PostPosted: Fri May 27, 2005 2:44 pm    Post subject: Inner join and outer join
Reply with quote

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

Thanks in Advance,
Vidya Bhat
Back to top
View user's profile Send private message

kkumarv

New User


Joined: 13 May 2005
Posts: 20
Location: Bangalore/India

PostPosted: Fri May 27, 2005 4:28 pm    Post subject: Re: Inner join and outer join
Reply with quote

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
Back to top
View user's profile Send private message
vidyasaraswathi

New User


Joined: 10 May 2005
Posts: 72

PostPosted: Fri May 27, 2005 4:34 pm    Post subject: Re: Inner join and outer join
Reply with quote

Hi Kumar,
Thank u for your quick reply.

Thanks and regards,
Vidya Bhat
Back to top
View user's profile Send private message
jkbytes

Active User


Joined: 19 Feb 2005
Posts: 141
Location: South Africa

PostPosted: Fri May 27, 2005 5:15 pm    Post subject: Re: Inner join and outer join
Reply with quote

Hi,

Kumar has given a nice description for Joins.

I'd like to add that the Outer joins are classified in to three types
    *Right outer join
    *Left outer join
    *Full outer join

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.

Quote:
i'm not sure about the order of retrieval.


Thanks & regards,

JKBYTES.
Back to top
View user's profile Send private message
vasanthanc

New User


Joined: 01 Apr 2005
Posts: 58

PostPosted: Mon May 30, 2005 1:43 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sandy_mcs

New User


Joined: 01 Apr 2006
Posts: 17
Location: Pune

PostPosted: Mon Apr 17, 2006 6:46 pm    Post subject:
Reply with quote

can anybody give me syntax for left outer join &rt outer join
thans you
Back to top
View user's profile Send private message
ajay_diaz
Warnings : 1

New User


Joined: 12 Sep 2005
Posts: 28

PostPosted: Sat May 06, 2006 9:22 pm    Post subject: LEFT OUTER JOIN
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Join key - Populate Zeros when Unpair... rexx77 DFSORT/ICETOOL 6 Thu May 12, 2016 12:22 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us