Mysql: Select rows from a table that are not in another
MysqlSortingUniqueDatabase TableMysql Problem Overview
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)
Mysql Solutions
Solution 1 - Mysql
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 2 - Mysql
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 3 - Mysql
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 - Mysql
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 - Mysql
Try this simple query. It works perfectly.
select * from Table1 where (FirstName,LastName,BirthDate) not in (select * from Table2);
Solution 6 - Mysql
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 - Mysql
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 - Mysql
This worked for me in Oracle:
SELECT a.*
FROM tbl1 a
MINUS
SELECT b.*
FROM tbl2 b;
Solution 9 - Mysql
SELECT a.* FROM
FROM tbl_1 a
MINUS
SELECT b.* FROM
FROM tbl_2 b