Solved

Oracle Query help: I want to be able to identify the values in TableA that are NOT located in TableB

Posted on 2011-03-01
4
407 Views
Last Modified: 2012-08-14
Question: What query can I use for this? This is for an oracle database, and I'm using PL/SQL.
I want to be able to identify the values in TableA that are NOT located in TableB

TableA

FirstName            LastName
David            Smith
Mike            Smith
Arron            Smith


TableB

FirstName            LastName
Mark            Smith
Mike            Smith
Arron            Smith
Bubba        Smith


So after I run my query, it will return...
David            Smith
0
Comment
Question by:silentthread2k
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35011036
select * from tableA
minus
select * from tableB;
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35011044
or specify the columns:

select firstname,lastname from tableA
minus
select firstname,lastname from tableB;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35011052
if there are other columns that you want to use but aren't shown in your example

select * from tableA
where (first_name,last_name) not in (select first_name,last_name from tableB)

I'm assuming the name columns are not null
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35011069
if there might be nulls try NOT EXISTS,  modify the subquery where clause based on how you want to evaluate NULL comparisons (if at all)


select * from tableA
where not exists (select null from tableB where tablea.last_name=tableb.last_name and tableb.first_name=tablea.first_name)
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

615 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