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

Posted on 2012-09-22
Last Modified: 2012-09-24
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.
Question by:anumoses
    LVL 37

    Expert Comment

    by:Gerwin Jansen
    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);
    LVL 15

    Expert Comment

    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
    LVL 6

    Assisted Solution

    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
    LVL 28

    Accepted Solution

    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 )
    LVL 6

    Author Comment


    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
    LVL 6

    Author Closing Comment


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    Via a live example, show how to take different types of Oracle backups using RMAN.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now