getting data that exists in one table but not in the other

I have two tables

1. dept_staff_od
2. dept_staff_od_plus

first table has 2421 records
second table has 2362 records.


I want to get the rows not in second tbale but exist in the first. I used minus, not exists but not getting the right data.
LVL 6
anumosesAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
if both of your tables have the same number/data type of columns, then you can use the below...

select * from dept_staff_od
minus
select * from dept_staff_od_plus

or you can try below exists query...

select * from dept_staff_od a
where not exists ( select 1 from dept_staff_od_plus b
where a.key_column = b.key_column )
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Please describe the tables for us and what you've tried already.

Assuming you have a key in both tables, let's call it staff_id for now, try this:

select * from dept_staff_od
where dept_staff_od.staff_id not in
(select distinct staff_id from dept_staff_od_plus);
0
 
Deepak ChauhanSQL Server DBACommented:
use this :

select * from dept_staff_od

where staff_id -- assuming key column
IN (select staff_id from dept_staff_od
except
select staff_id from dept_staff_od_plus)

Hope so you will got desired result as this is tested query.
Please let me know for any concern
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ianmills2002Connect With a Mentor Commented:
Like above, I will assume you have a key in both tables, let's call it staff_id.

select *
from dept_staff_od
     left outer join dept_staff_od_plus
        on dept_staff_od.staff_id = dept_staff_od_plus.staff_id
where dept_staff_od_plus.staff_id is NULL

Open in new window


All columns in the 2nd table after the LEFT OUTER JOIN will have a NULL value
0
 
anumosesAuthor Commented:
@deepakChauhan

select * from dept_staff_od
where social_security_no -- assuming key column
IN (select social_security_no from dept_staff_od
except
select social_security_no from dept_staff_od_plus)

ORA-00907: missing right parenthesis
0
 
anumosesAuthor Commented:
thanks
0
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.