How do I correctly use "Not Equal" in MS Access?

Question

Objective:

The intent of this query is to select all of the distinct values in one column that don't exist in a similar column in a different table.

Current Query:

SELECT DISTINCT Table1.Column1
FROM Table2, Table1
WHERE Table1.Column1 <> Table2.Column1 

Results From Query:

What happens when I try to run this query is the progress bar fills up almost immediately but then it pretty much freezes and doesn't do anything else as far as I can see. When I use an = sign instead of <> it outputs the values that are equal just fine and if I replace Table2.Column1 with an actual actual value it works just fine.

I just ran it again while typing this question and the above query gave me an answer this time but it has all of the DISTINCT values for the column not all of the values unique to just that table like it should.

Any ideas on what I'm doing wrong or missing here?

Dulaney

BTW, it's possible to do non-equi-joins, though you have to dip into the SQL window as it cannot be represented in the Access QBE's design view. What I always do is create an equi-join, then go to the SQL and change the = in the join to <> or > or < or >= and so forth. – Queensland

Answer 1

Like this

SELECT DISTINCT Table1.Column1
FROM Table1
WHERE NOT EXISTS( SELECT * FROM Table2
    WHERE Table1.Column1 = Table2.Column1  )

You want NOT EXISTS, not "Not Equal"

---

By the way, you rarely want to write a FROM clause like this:

FROM Table1, Table2

as this means "FROM all combinations of every row in Table1 with every row in Table2..." Usually that's a lot more result rows than you ever want to see. And in the rare case that you really do want to do that, the more accepted syntax is:

FROM Table1 CROSS JOIN Table2

Biolysis

This works thanks. But why doesn't "NOT Equal" work for this and how is it supposed to be used? – Dulaney

Because "Equal" doesn't mean what you think it does. "Equals" is a scalar comparison operator, but you need to compare Sets and their elements. EXISTS is one of the operators that lets you extend a scalar to a set. – Biolysis

CROSS JOIN is not supported in Jet/ACE SQL. – Queensland

Well, you got me there, I guess I just never tried it in Access. that's how rare it is. – Biolysis

Answer 2

In Access, you will probably find a Join is quicker unless your tables are very small:

SELECT DISTINCT Table1.Column1
FROM Table1 
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1  
WHERE Table2.Column1 Is Null

This will exclude from the list all records with a match in Table2.

Caroncarotene

Would I be able to do this with three tables easily? I used two tables to get the point across but I'm actually doing this with three tables. I'm using RBarry's way with two separate NOT EXISTS statements. – Dulaney

Barry's way is certainly good, but not so good for Access, unless time is not important. Yes, it is easy enough with three tables. It may be necessary to create a sub query, it depends on how complex the whole thing is. You might like to try building this in the query design window. You can create the join by dragging fields from one table to the next and choose the join type by right-clicking on the line. Otherwise, update your question to show the three tables and the way you want them to work together. – Caroncarotene

Actually Barry's way doesn't really take long at all so I'm just going to stick with the NOT EXISTS. – Dulaney

If you're lucky, NOT EXISTS will use the indexes on both sides of the comparison. If you're not, you'll have a real performance hog. It's not entirely predictable when the indexes will or will not be used, and as the table stats change, the performance may change. Both NOT EXISTS and NOT IN are something I try to avoid whenever there's an alternative (as there certainly is in this case). – Queensland

Answer 3

I have struggled to get a query to return fields from Table 1 that do not exist in Table 2 and tried most of the answers above until I found a very simple way to obtain the results that I wanted.

I set the join properties between table 1 and table 2 to the third setting (3) (All fields from Table 1 and only those records from Table 2 where the joined fields are equal) and placed a Is Null in the criteria field of the query in Table 2 in the field that I was testing for. It works perfectly.

Thanks to all above though.

Brogan

If you switch to SQL view, you will find that this is identical to the LEFT JOIN answer by Fionnuala. – Morphia