asked on

Using a Left Join to find records that are Not equal.

Experts,

I am trying to reconcile the data from two tables. Basically, I need to figure out which entries exists in one table (members), that don't exist in the other (access).

I know by using a LEFT JOIN I can compare the data when they are equal but, how do I display the information that is not equal?

My query is working but, I only want to show the rows that are not equal to each other.

Here is my query:

$get_missing_NUIDs = "SELECT members.NUID FROM members LEFT JOIN access ON members.NUID=access.NUID";

I need to show the members.NUID that are found in the members table but, are not found in the access table.

Thanks for your help!

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

evibesmusic

ASKER

@angelIII:

OK, this works but, help me understand some thing here.

Using 'IS NULL' usually represents a value that blank or empty. In this instance it seems to mean a value that isn't there, or is not in the table.

Am I thinking of this usage of IS NULL correctly?

Thanks for your help.

EVM

Guy Hengel [angelIII / a3]

yes, you understand correctly.

if one used a INNER join here, the condition IS NOT NULL cannot be true, as the field checked is the joining condition field, and CANNOT be null.
for left join, you want to find the rows that don't match, so where the resulting field is returned "null".

evibesmusic

ASKER

Perfect solution. To the point.

Explanation of usage of IS NULL in this instance helped cement the concept for me too.

Thanks angelIII!

zappafan2k2

If it makes it more intuitive, you could also do something like this:
$get_missing_NUIDs = "SELECT members.NUID FROM members
where members.NUID not in (select access.NUID from access)";