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

Posted on 2012-08-22
Last Modified: 2012-08-22

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!
Question by:evibesmusic
    LVL 142

    Accepted Solution

    this query will return the result you need:

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

    Author Comment


    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.

    LVL 142

    Expert Comment

    by: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".

    Author Closing Comment

    Perfect solution. To the point.

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

    Thanks angelIII!
    LVL 12

    Expert Comment

    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)";

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    779 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

    17 Experts available now in Live!

    Get 1:1 Help Now