SQL: Returning unassigned rows

Question

assignments:
id | prospectid
1  | 1
2  | 2
3  | 5

prospects:
id | name
1  | purple
2  | red
3  | blue
4  | orange
5  | green

I'm wanting to return a list of prospects where prospect.id does not exist under the prospectid column within assignments. In a nutshell, I want to return unassigned prospects.

So from the above example, it ought to return:

prospects:
3 | blue
4 | orange

My failed SQL:

SELECT * FROM prospects
WHERE prospects.id != `assignments`.prospectid

Answer 1

You can use a NOT EXISTS clause

select * 
from prospects p
where not exists (select null from 
                  assignments a
                  where a.prospectid = p.id)

Answer 2

use LEFT JOIN

SELECT P.id, P.name
FROM Prospects P
LEFT JOIN assignments A
on P.id = A.prospectid
WHERE A.prospectid is NULL