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)
This question is related to
mysql
sorting
unique
database-table
This worked for me in Oracle:
SELECT a.*
FROM tbl1 a
MINUS
SELECT b.*
FROM tbl2 b;
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.
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...
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
Try this simple query. It works perfectly.
select * from Table1 where (FirstName,LastName,BirthDate) not in (select * from Table2);
SELECT a.* FROM
FROM tbl_1 a
MINUS
SELECT b.* FROM
FROM tbl_2 b
Try:
SELECT * FROM table1
LEFT OUTER JOIN table2
ON table1.FirstName = table2.FirstName and table1.LastName=table2.LastName
WHERE table2.BirthDate IS NULL
Source: Stackoverflow.com