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
404 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
  • 2
  • 2
4 Comments
 
LVL 76

Expert Comment

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

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Read about achieving the basic levels of HRIS security in the workplace.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

792 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