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)

score 231 · Answer 1 · answered Aug 01 '12 at 21:12

231

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.

answered Aug 01 '12 at 21:12

Stennie

63,885
14
149
175

1

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... – Aug 01 '12 at 21:17
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](http://stackoverflow.com/questions/218499/mysql-diff-tool). – Stennie Aug 01 '12 at 21:25
6

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. – Mark Amery Nov 17 '14 at 18:11
10

But what if we're talking about big data? And Table2 contain 100M rows, for example? – frops May 18 '16 at 07:31

score 119 · Accepted Answer · answered Aug 01 '12 at 22:12

119

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

answered Aug 01 '12 at 22:12

Zane Bien

22,685
6
45
57

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. – Kyle Kochis Apr 07 '15 at 02:49
126

If you have 300 columns, you should redesign your database. – Iharob Al Asimi Jun 01 '16 at 13:50
hey this works for me too, thanks! but would that be a problem if the rows is > 300 like you mentioned above? – thekucays Jul 21 '16 at 04:21
1

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 – thekucays Jul 21 '16 at 04:29

score 73 · Answer 3 · edited Mar 20 '18 at 22:16

73

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

edited Mar 20 '18 at 22:16

go2null

2,080
1
21
17

answered Aug 01 '12 at 21:07

Ruzbeh Irani

2,318
18
10

6

Good answer, economical for large data sets, thanks. – ekerner Sep 08 '14 at 15:27
3

Strong. Best answer for large datasets – Ian Chadwick Feb 09 '16 at 19:54

Steve · Answer 4 · 2012-08-01T21:28:33.090

43

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

edited Aug 01 '12 at 21:28

answered Aug 01 '12 at 21:21

Steve

213,761
22
232
286

alright, i guess that must be it, btw why the `WHERE t2.Birthdate Is Null` instead of `AND t1.Birthdate = t2.Birthdate`? – Aug 01 '12 at 21:44
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 – Steve Aug 01 '12 at 22:12
2

This is a terrific answer, as it does not require returning all rows of `Table2`! – dotancohen Dec 08 '13 at 18:47
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. – DR. Dec 08 '15 at 19:08

score 12 · Answer 5 · answered Oct 30 '17 at 16:58

12

Try this simple query. It works perfectly.

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

answered Oct 30 '17 at 16:58

Vijesh

795
3
9
23

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). – Jose Manuel Abarca Rodríguez Jul 30 '21 at 17:31

score 10 · Answer 6 · edited Dec 08 '14 at 12:21

10

Try:

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

edited Dec 08 '14 at 12:21

ROMANIA_engineer

54,432
29
203
199

answered Dec 08 '14 at 11:58

Sachin Pundir

203
3
6

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'` – ozma Dec 05 '22 at 20:07

score 2 · Answer 7 · answered Nov 24 '21 at 13:46

2

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

answered Nov 24 '21 at 13:46

Heitor Giacomini

384
2
12

score 0 · Answer 8 · answered Feb 15 '23 at 01:51

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 )

score -3 · Answer 9 · edited Feb 07 '17 at 13:24

-3

This worked for me in Oracle:

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

edited Feb 07 '17 at 13:24

Simon PA

748
13
26

answered Feb 07 '17 at 12:16

Gennady Sorochan

13
1
3

1

The question was about MySQL. – jelder Feb 26 '20 at 23:57

score -8 · Answer 10 · edited Jun 16 '15 at 21:52

-8

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

edited Jun 16 '15 at 21:52

Matt

74,352
26
153
180

answered Jun 16 '15 at 18:52

Ingrid R. Forsale

1

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

10 Answers10

Linked

Related