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)

Fennell

Answer 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

Kloster

Note that this only works as expected when none of the columns have NULL values. In MySQL NULL != NULL so every row that has a NULL value will be returned even if there is a duplicate row in the second table. – Assemblyman

If you have 300 columns, you should redesign your database. – Sheepcote

hey this works for me too, thanks! but would that be a problem if the rows is > 300 like you mentioned above? – Sightly

i'm still confused about the query btw..what if i change "where b.FirstName is null" to "where b.LastName is null" for example? what's the difference? i'm sorry for asking this, i'm still new to sql :D – Sightly

Answer 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.

Beg

thanks for the clarification! but i really don't need to base the selection of rows on any field, because i'm interested in any variation of any field in the row... – Fennell

If there are only a few columns to compare you can do a join as per @Steve's example. If you are actually asking for a general comparison of data in two tables with many columns, you probably want to look for a MySQL diff tool. – Beg

Note that this will always return an empty set if the column you're looking at in Table2 contains nulls. Not an issue if you're doing it based upon the primary key, but relevant to folks trying to use this query in other contexts. – Tonneson

But what if we're talking about big data? And Table2 contain 100M rows, for example? – Americana

Answer 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...

Tay

Good answer, economical for large data sets, thanks. – Castile

Strong. Best answer for large datasets – Julianejuliann

Answer 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

Serotherapy

alright, i guess that must be it, btw why the WHERE t2.Birthdate Is Null instead of AND t1.Birthdate = t2.Birthdate? – Fennell

Because if you add that, then every row will be returned, you say that in the output should appear only rows not in the second table – Serotherapy

This is a terrific answer, as it does not require returning all rows of Table2! – Chauncey

I agree, great answer. I have a man-many table between 4 tables, putting the AND in the inner join is most definitely going to be more economical. – Exhibit

Answer 5

Try this simple query. It works perfectly.

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

Fye

I liked this one because the names of the columns in one table don't have to be the same as the names of the columns in the other table, it just compares the corresponding values (tested and worked). – Matrass

Answer 6

Try:

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

Fricassee

It is a good answer but note that "IS NULL" will trigger full scan on table1. if possible one might want to add scoping to the where using an indexed column of table1. e.g AND table1.name = 'Joe' – Cliff

Answer 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

Khoury

Answer 8

SELECT 
table_A.column_1, 
table_A.column_2, 
table_A.email

FROM table_A

WHERE table_A.email 
NOT IN ( SELECT table_B.email FROM table_B )

An example with several columns from Table A.

Both, Table A and Table B have a column named "email".

Matching emails from Table B will be omitted in the query results.

( This question is similar to mysql select from table 1 where no match in table 2 )

Boydboyden

Answer 9

This worked for me in Oracle:

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

Maltzman

The question was about MySQL. – Bucella

Answer 10

SELECT a.* FROM 
FROM tbl_1 a
MINUS
SELECT b.* FROM 
FROM tbl_2 b

Claraclarabella