Sql to get orphans

Hi Experts,

The database is Oracle 11g
I have table Parent  with column  ( ID ,name)
 The table Child has column ChildID , ParentID  ,Name
sample data
    P1   Parent1
     P2  Parent2

   C1 P1  Child1
   C2  P1  Child2
   C3  P3  Child3

it is expected for each ChildID there has to be a corresponding
There are some  orphaned rows in the table Child  . In the sample data Child3 is an orphan because there is no P3 in Parent table

I need help in getting the Sql that can give the orphan children
Sam OZAsked:
Who is Participating?
PortletPaulConnect With a Mentor Commented:
from child c
left join parent p on c.ParentID = p.id
where p.id IS NULL

Open in new window

Sam OZAuthor Commented:
Thanks .  Infact in my table it is p.UID instead of p.ID    For some reason , p.UID throws error ( Because UID is a reserved word it looks ( Even select p.UID from parent p throws error)
What is the exact error message?
Is p.uid an integer?
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

johnsoneSenior Oracle DBACommented:
You would need to put it in double quotes:

select p."UID" from parent p

The double quotes makes is case sensitive, so make sure it is upper case.

This is a total design flaw, you should never have column names with reserved words.

An alternate way to find duplicates:

Create an exception table by running this in SQL*Plus:

Then create a referential integrity constraint (something you should have to prevent this in the first place):
alter table child add foreign key (id) references parent("UID") exceptions into exceptions;

Then you can get all the invalid rows with this:
select * from child where rowid in (select row_id from exceptions);

Let Oracle do the work for you.
Do you have foreign key constraints on the child tables?
UID returns an integer that uniquely identifies the session user (the user who logged on).

certainly not a good idea to use that as a column name
GhunaimaConnect With a Mentor Commented:
select * from child where parentid not in (select p."UID" from Parent p)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.