[SOLVED] Simple Join Query Driving me nuts

[johnc71]

I have two tables, students and europeans.  I would like to get a list of students that ARE NOT europeans. 

 

I can get a list of the ones that ARE europeans by doing the following:

SELECT * FROM students a, europeans b where a.student_id = b.student_id

 

but how do I get the list of the ones that ARE NOT?  It is 2:40AM and my brain is not working anymore.

 

[pre]

students

+--------------------------+-----------------+

| student_id              | student_name    |

+--------------------------+-----------------+

| 1                        | Mark            |

| 2                        | Anna            |

| 3                        | Milos          |

| 4                        | Debby          |

| 5                        | Stan            |

| 6                        | Natasha        |

+--------------------------+-----------------+

 

european

+--------------------------+-----------------+

| id                      | student_id      |

+--------------------------+-----------------+

| 1                        | 2              |

| 2                        | 3              |

| 3                        | 6              |

+--------------------------+-----------------+

[/pre]

[Mchl]

SELECT * FROM students WHERE student_id NOT IN (SELECT student_id  FROM europeans)

 

This should work. However I have some concerns regarding this design...

[fenway]

Just like I have concerns with "NOT IN"... ;-)

[Mchl]

What's your concern about it?

[mtoynbee]

SELECT * FROM students AS a

LEFT OUTER JOIN europeans AS b ON a.student_id = b.student_id

WHERE b.student_id IS NULL

[Mchl]

Nice

[fenway]

What's your concern about it?

Performance nightmare.