EXISTS operator in SQL

The exists operator is useful in queries that can be formulated in the form: “Select all rows from the table such that there exist rows in another table.”

For example, here’s a query to select jobs that are performed by someone:

select l.Nome
from Lavoro l
where exists (
	select *
    from PersonaLavoro pl
	where l.ID = pl.IdLavoro
);

This isn’t a great example since the same result can be obtained with a simple join:

select l.Nome
from Lavoro l, PersonaLavoro pl
where l.ID = pl.IDLavoro;

However, we can use the reverse operator not exists to obtain something more interesting, specifically all jobs for which there is no one performing them:

select l.Nome
from Lavoro l
where not exists (
	select *
    from PersonaLavoro pl
	where l.ID = pl.IdLavoro
);

This result could be achieved in a less intuitive way using a left join with various conditions:

select l.Nome
from Lavoro l
left join PersonaLavoro pl
on l.ID = pl.IDLavoro
where pl.IDLavoro is NULL;

---

tags: databases SQL