Mysql Select rows from a table that are not in another

Question

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

User · Answer

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.

User · Answer

Try this simple query  It works perfectly   select   from Table1 where  FirstName LastName BirthDate  not in  select   from Table2

User · Answer

SELECT a   FROM  FROM tbl 1 a MINUS SELECT b   FROM  FROM tbl 2 b

User · Answer

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

User · Answer

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

User · Answer

Try   SELECT   FROM table1     LEFT OUTER JOIN table2     ON table1 FirstName   table2 FirstName and table1 LastName table2 LastName     WHERE table2 BirthDate IS NULL

User · Answer

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

User · Answer

This worked for me in Oracle   SELECT a        FROM tbl1 a  MINUS  SELECT b        FROM tbl2 b

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

Examples related to mysql

Examples related to sorting

Examples related to unique

Examples related to database-table