MySQL 5.5 Database Query Help

I am having a few issues with a DB query.

I have two tables, students (Fields: FirstName, LastName, StdSSN), and teachers (TFirstName, TLastName, TSSN) which I've stripped down for this example. I need to perform a query that will return all the students except for the students that are teachers themselves.

I have the query

SELECT student.FirstName, student.LastName
FROM `student`,`teachers`
WHERE student.StdSSN=teachers.TSSN

Which gives me a list of all the teachers who are also students it does not provide me with a list of students who are not teachers, so I tried changing to:

SELECT student.FirstName, student.LastName
FROM `student`,`teachers`
WHERE student.StdSSN!=teachers.TSSN

Which gives me a list of all the students with many duplicate values so I am a little stuck here. How can I change things to return a list of all students who are not teachers? I was thinking INNER/OUTER/SELF-JOIN and was playing with that for a few hours but things became complicated and I did not accomplish anything so I've pretty much given up.

Can anyone give me any advice? I did see the query before and it was pretty simple, but I've failed 开发者_运维问答somewhere.

---

Using NOT IN

SELECT s.*
  FROM STUDENTS s
 WHERE s.stdssn NOT IN (SELECT t.tssn
                          FROM TEACHERS t)

Using NOT EXISTS

SELECT s.*
  FROM STUDENTS s
 WHERE NOT EXISTS (SELECT NULL
                     FROM TEACHERS t
                    WHERE t.tssn = s.stdssn)

Using LEFT JOIN / IS NULL

   SELECT s.*
     FROM STUDENTS s
LEFT JOIN TEACHERS t ON t.tssn = s.stdssn
    WHERE t.column IS NULL

I used "column" for any column in TEACHERS other than what is joined on.

Comparison:

If the column(s) compared are nullable (value can be NULL), NOT EXISTS is the best choice. Otherwise, LEFT JOIN/IS NULL is the best choice (for MySQL).