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.
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
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 )
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);
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
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
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
anumosesAuthor Commented:

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

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