Multi Table NOT EQUAL in Access Query

Question

I have two tables. VEHICLES and OWNERSHIP. I am trying to make a query that will give me a list of all VEHICLES NOT in the OWNERSHIP table. I basically need a report of my available VEHICLE inventory. I tried this query:

SELECT VEHICLE.*
FROM VEHICLE, OWNERSHIP
WHERE (VEHICLE.VEH_ID <> OWNERSHIP.VEH_ID);

Im getting:

When I do an equal I get all vehicles which are listed in the ownership so that works. But the NOT Equal does not. Any ideas?

Answer 1

Try

SELECT VEHICLE.*
FROM VEHICLE
WHERE NOT EXISTS
(SELECT NULL FROM OWNERSHIP WHERE VEHICLE.VEH_ID= OWNERSHIP.VEH_ID);

Answer 2

The NOT EXISTS approach can be slow if your tables contain many rows. An alternative approach which can be much faster is to use a LEFT JOIN with a WHERE clause to return only the rows where the right-hand join field is Null.

SELECT VEHICLE.*
FROM
    VEHICLE AS v
    LEFT JOIN OWNERSHIP AS o
    ON v.VEH_ID = o.VEH_ID 
WHERE o.VEH_ID Is Null;

You could use Access' "Find Unmatched Query Wizard" to create a similar query.

If both tables are small you probably won't notice a difference. But it should be easy to check whether the difference is noticeable. And this approach will serve you better if your tables grow substantially over time.