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
405 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

733 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