[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

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

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
silentthread2k
Asked:
silentthread2k
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
select * from tableA
minus
select * from tableB;
0
 
slightwv (䄆 Netminder) Commented:
or specify the columns:

select firstname,lastname from tableA
minus
select firstname,lastname from tableB;
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now