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

Admin 1
Viewed 240255

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)
database-table unique sorting mysql
9 Answers

Try this simple query. It works perfectly.

select * from Table1 where (FirstName,LastName,BirthDate) not in (select * from Table2);
user_61928620

A Option would be

SELECT A.*
    FROM TableA as A
    LEFT JOIN TableB as B
    ON A.id = B.id
    Where B.id Is NULL
user_63373080
Related