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
402 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 73

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database connection opened on a machine 8 54
Update in Sql 7 30
SQL Query 34 82
Powershell script 13 72
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

895 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

18 Experts available now in Live!

Get 1:1 Help Now