SQL Question

[legal-medium]

I have 2 tables

PASSPORT
id
1
2
3
4
5
6
7
8
9

MEMBERS
id
1
2
3
4
5
6
7
8

I want to return get all the rows from the PASSPORTS table where id does not match the MEMBERS table id column

So for this example the result would be

9

[tomhath]

From The MySQL Manual: http://www.mysql.com/doc/en/JOIN.html

If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;

This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2).