Ask Question
Help Others
Home › Blogs › Lepa's blog

START WITH and CONNECT BY in Oracle

Thu, 04/10/2008 - 4:05pm — Lepa
Your rating: None Average: 4 (41 votes)

Here is a quick example to help you understand how oracle Start With analytical function works. The start with .. connect by clause can be used to select data that has a hierarchical relationship. Usually, it is some sort of parent child relationship like supervisor and an employee.

Let’s assume we have a table called employees. I would like to know employees that directly report to supervisor Id 1122456 –simple, right?

SELECT emplid, supervisor_id
  FROM ps_employees
 WHERE supervisor_id = '1122456';

EMPLID  SUPERVISOR_ID
0119676 1122456
0112356 1122456
0120022 1122456

Now I would like to know all employees that report to supervisor Id 1122456 (directly or indirectly).

SELECT     emplid, supervisor_id
      FROM ps_employees
START WITH supervisor_id = '1122456'
CONNECT BY PRIOR emplid = supervisor_id;

EMPLID  SUPERVISOR_ID
0119676 1122456
0112356 1122456
0120022 1122456
0120033 0120022

Notice that with the 1st sql we had 3 rows total while using Start With gave us 4 rows back. If you look at the last row on the 2nd select, you notice that employee 0120033 reports to 0120022 who in return reports to 1122456.

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Wed, 09/10/2008 - 3:47pm
#1
Guest's picture
Guest (not verified)
Re: START WITH and CONNECT BY in Oracle

Very nicely explained

Top
Tue, 09/08/2009 - 6:09am
#2
Guest's picture
Guest (not verified)
Re: START WITH and CONNECT BY in Oracle

Nice and simple explanation. Thanks.

Top
Thu, 10/08/2009 - 5:40pm
#3
Beto's picture
Beto (not verified)
Re: START WITH and CONNECT BY in Oracle

So good. Thanks

Top
Wed, 06/23/2010 - 6:37am
#4
Guest's picture
Guest (not verified)
Re: START WITH and CONNECT BY in Oracle

Hi,

my problem with connect by is more difficult. Let say we have a table for bils of material decribing following structure:
prod
|
|-> semif1
| |
| |-> semif3
| | |
| | |->comp4
| |
| |-> comp2
|
|-> semif2
| |
| |->comp3
|
|-> comp1

where prod is final product, semifx are semifinished items on different levels in stucture and compx are components on different levels.
The table for this structure looks like follow:
prod mater
-----------------
prod semif1
prod semif2
prod comp1
semif1 semif3
semif1 comp2
semif2 comp3
semif3 comp4

there is also another table where are stored information about acqusition method of particular items. Let say acq. method for prod and all semifinished items is 1=manufactured and acq.method for components is 2=purchased.
What I would need is to exclude items semif3 and comp4 from result when acq.method for semif1 is changed on 2. Is somethink like this possible in simple SQL?

Top
Thu, 01/13/2011 - 7:41pm
#5
Jo's picture
Jo (not verified)
Re: START WITH and CONNECT BY in Oracle

This is awesome!!!
love the simple explanation!
Thanks heaps!

Top
Mon, 02/21/2011 - 2:51am
#6
Stephen's picture
Stephen (not verified)
Re: START WITH and CONNECT BY in Oracle

Simple and straight to the point explanation, thanks.

Top
Mon, 09/12/2011 - 5:12am
#7
Yuva Raja's picture
Yuva Raja (not verified)
Re: START WITH and CONNECT BY in Oracle

Awesome!!! u have done a great job.. Simple and effective explanation.. Keep it up...

Thanks

Top
Thu, 11/17/2011 - 2:36am
#8
Sanjeev's picture
Sanjeev (not verified)
Re: START WITH and CONNECT BY in Oracle

Very simple and well explainaition.
Thanks a lot .

Top
Thu, 05/31/2012 - 3:42am
#9
Guest's picture
Guest (not verified)
Re: START WITH and CONNECT BY in Oracle

Yes

Top
Sun, 06/10/2012 - 3:47pm
#10
kannadhasan's picture
kannadhasan
User offline. Last seen 7 years 45 weeks ago. Offline
Joined: 06/10/2012
Posts: 4
Re: START WITH and CONNECT BY in Oracle

Nice Explanation.Thanks !!!

Top
Thu, 07/26/2012 - 12:55am
#11
Guest's picture
Guest (not verified)
Re: START WITH and CONNECT BY in Oracle

good one

Top
Fri, 09/14/2012 - 10:26am
#12
Guest's picture
Guest (not verified)
Re: START WITH and CONNECT BY in Oracle

gr8 explanation.. thanks buddy

Top
Sat, 10/20/2012 - 10:43am
#13
Soundara Pandian Sundaram's picture
Soundara Pandian Sundaram (not verified)
Re: START WITH and CONNECT BY in Oracle

superrrrrr explaination... love this :)

Top
Wed, 01/02/2013 - 9:26am
#14
Guest's picture
Guest
Re: START WITH and CONNECT BY in Oracle

After looking into many posts in various websites at last i got it here..

Top
Wed, 04/10/2013 - 12:26am
#15
Guest's picture
Guest
Re: START WITH and CONNECT BY in Oracle

Really nice and simple explanation. Was struggling to understand this concept. And all other websites has more confusing explanations and examples. this one made this concept so clear and easy! Thanks a lot!!

Top
Tue, 01/28/2014 - 3:03am
#16
Guest's picture
Guest
Re: START WITH and CONNECT BY in Oracle

itni saralta se samjhaya hai ke kya kahna....bahut sundar....very nicely explained. I was looking for this kind of explanation only....

Top