Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
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

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us