You can do the reverse of IN & EXISTS by placing NOT in front of them. This returns you all the rows from the parent which don't have a match in the subquery.

For example to find the rows in colours without colour in bricks, you can use NOT EXISTS.
#Oracle
#Not_In vs #Not_Exists

But do the same with NOT IN
#Oracle
#Not_In vs #Not_Exists

For remember NOT IN condition to be true, comparing all its elements to the parent table must return false.

But remember that comparing anything to null gives unknown ! So the whole expression is unknown and you get no data.

To resolve this, either use NOT EXISTS or add a where clause to stop the subquery returning null values
#Oracle
#Not_In vs #Not_Exists

For remember NOT IN condition to be true, comparing all its elements to the parent table must return false.

But remember that comparing anything to null gives unknown ! So the whole expression is unknown and you get no data.

To resolve this, either use NOT EXISTS or add a where clause to stop the subquery returning null values
#Oracle
#Not_In vs #Not_Exists