Mysql: Select rows from a table that are not in another

user1006989 picture user1006989 · Aug 1, 2012 · Viewed 201.6k times · Source

How to select all rows in one table that do not appear on another?

Table1:

+-----------+----------+------------+
| FirstName | LastName | BirthDate  |
+-----------+----------+------------+
| Tia       | Carrera  | 1975-09-18 |
| Nikki     | Taylor   | 1972-03-04 |
| Yamila    | Diaz     | 1972-03-04 |
+-----------+----------+------------+

Table2:

+-----------+----------+------------+
| FirstName | LastName | BirthDate  |
+-----------+----------+------------+
| Tia       | Carrera  | 1975-09-18 |
| Nikki     | Taylor   | 1972-03-04 |
+-----------+----------+------------+

Example output for rows in Table1 that are not in Table2:

+-----------+----------+------------+
| FirstName | LastName | BirthDate  |
+-----------+----------+------------+
| Yamila    | Diaz     | 1972-03-04 |
+-----------+----------+------------+

Maybe something like this should work:

SELECT * FROM Table1 WHERE * NOT IN (SELECT * FROM Table2)

Answer

Stennie picture Stennie · Aug 1, 2012

You need to do the subselect based on a column name, not *.

For example, if you had an id field common to both tables, you could do:

SELECT * FROM Table1 WHERE id NOT IN (SELECT id FROM Table2)

Refer to the MySQL subquery syntax for more examples.