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

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)
mysqlsortinguniquedatabase-table


Solution 1:[1]

If you have 300 columns as you mentioned in another comment, and you want to compare on all columns (assuming the columns are all the same name), you can use a NATURAL LEFT JOIN to implicitly join on all matching column names between the two tables so that you don't have to tediously type out all join conditions manually:

SELECT            a.*
FROM              tbl_1 a
NATURAL LEFT JOIN tbl_2 b
WHERE             b.FirstName IS NULL

Solution 2:[2]

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.

Solution 3:[3]

SELECT *
FROM Table1 AS a
WHERE NOT EXISTS (
  SELECT *
  FROM Table2 AS b 
  WHERE a.FirstName=b.FirstName AND a.LastName=b.Last_Name
)

EXISTS will help you...

Solution 4:[4]

A standard LEFT JOIN could resolve the problem and, if the fields on join are indexed,
should also be faster 

SELECT *
FROM Table1 as t1 LEFT JOIN Table2 as t2 
ON t1.FirstName = t2.FirstName AND t1.LastName=t2.LastName
WHERE t2.BirthDate Is Null

Solution 5:[5]

Try this simple query. It works perfectly.

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

Solution 6:[6]

Try:

SELECT * FROM table1
    LEFT OUTER JOIN table2
    ON table1.FirstName = table2.FirstName and table1.LastName=table2.LastName
    WHERE table2.BirthDate IS NULL

Solution 7:[7]

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

Solution 8:[8]

This worked for me in Oracle:

SELECT a.* 
    FROM tbl1 a 
MINUS 
SELECT b.* 
    FROM tbl2 b;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Zane Bien
Solution 2 Stennie
Solution 3 go2null
Solution 4
Solution 5 Vijesh
Solution 6 ROMANIA_engineer
Solution 7 Heitor Giacomini
Solution 8 Simon PA

Related Questions