I Have this query to get land which doesn't have a house on it. Usually this is work on MySQL, but in Oracle, it won't work


"SELECT ID FROM land
WHERE ID NOT IN (SELECT id_land AS ID FROM house)"

Can anyone help, what did I'm wrong ?

maybe "not in" does not what you expect, because oracle can understand it.
Look at this page:
http://jonathanlewis.wordpress.com/2007/02/25/not-in/

You may want to use the "NOT EXISTS" rather than "NOT IN" to do what you want.

SELECT ID 
FROM land l
WHERE NOT EXISTS (
    SELECT 1
    FROM house h
    WHERE h.id_land=l.ID
)

Thanks for the suggest.

I found that, my query need a little bit changes

"SELECT ID FROM land
WHERE ID NOT IN (SELECT id_land AS ID FROM house WHERE id_land NOT NULL)"

I didn't know why, but the record from 'house' table where id_land equal to null, affected the result of my old query. And now, the result show as I expected.

That's because of the nature of a null value.

A null value is an "unknown" value.
You cannot match it against anything that is not null itself.
And any operation on a null equals null.

The alternate syntax with a not exists eliminate this scenario, because as you do a join between the external query and the inner query, you naturally exclude the null values in the land.id table