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: enter image description here

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?

score 2 · Accepted Answer · answered Nov 25 '12 at 18:42

2

Try

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

answered Nov 25 '12 at 18:42

a1ex07

36,826
12
90
103

Im trying to learn this so let me know if I explain it wrong please. The subquery is finding which vehicles are in both VEHICLE AND OWNERSHIP. Whereas the Outer Query is selecting all those Vehicles that were not identified as being in both VEHICLE and OWNERSHIP. Is this correct? Would this be considered a correlated subquery? – Batman Nov 25 '12 at 18:50
1

@Batman : Yes, that's correct - such subquery can be considered as correlated. However, that doesn't always mean poor performance, optimizer is smart enough not to run it for every single row... I'm not 100% sure that MS=Access supports `left join`, but the query above is the same as `SELECT v.* FROM vehicle v LEFT JOIN ownership o ON (o.veh_id = v.veh_id) WHERE o.veh_id IS NULL` and should generate the same execution plan. – a1ex07 Nov 25 '12 at 19:17

score 1 · Answer 2 · answered Nov 25 '12 at 19:14

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.

Multi Table NOT EQUAL in Access Query

2 Answers2