Problem with Not In Clause

  • roon

    Grasshopper

    Points: 11

    I want to find all entries in one table that are not in another.

    The query is below

    SELECT *

    FROM Figtree a

    Where VehicleNo not in (Select Figtree_id from Vehicle)

    It returns no rows even though there are rows that dont match.

    The alternative not exists syntax works however.

    SELECT *

    FROM Figtree a

    Where not exists (Select * from Vehicle where VehicleNo = Figtree_id )

    Anybody else seen this?

  • Antares686

    SSC Guru

    Points: 125444

    Yes, an it can be a pain to figure out exactly why. Can you post the DDL of both tables please.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • shaley1

    SSC Rookie

    Points: 44

    Maybe this would work

    SELECT *

    FROM Figtree a

    left join VehicleNo b.Figtree_id

    Where b.Figtree_id is null

    I want to find all entries in one table that are not in another.

    The query is below

    SELECT *

    FROM Figtree a

    Where VehicleNo not in (Select Figtree_id from Vehicle)

    It returns no rows even though there are rows that dont match.

    The alternative not exists syntax works however.

    SELECT *

    FROM Figtree a

    Where not exists (Select * from Vehicle where VehicleNo = Figtree_id )

    Anybody else seen this?

    [/quote]

  • shaley1

    SSC Rookie

    Points: 44

    Sorry posted my last reply in error.

    I think this would work.

    select a.*

    from Figtree a

    left join Vehicle b

    on a.VehicleNo = b.Figtree_id

    where b.Figtree_id is null

    I try never to do a sub select if I can use a Join to do the same thing.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply