Often you want rows whose value appears in a list that comes from another table, like employees working in departments located in Pune. An IN subquery builds that list and checks membership. Use it when the inner query can return many rows. Be careful with NOT IN: if the list contains a NULL, it returns no rows at all, so NOT EXISTS is safer.