How to find data from one table which don't exist in another table?

Question

I have 2 tables, as given below. How can I check which data_entry's information are not in the task_detail table.

data_entry
+----+----------+----------+
| id | firstname| lastname |
+----+----------+----------+
| 1  | Anil     | Chaudhari|
+----+----------+----------+
| 2  | Sunil    | Chaudhari|
+----+----------+----------+
| 3  | Pranil   | Chaudhari|
+----+------+--------------+

task_detail
+----+-------+--------------+--------+
| id | de_id | field_name   | amount |
+----+-------+--------------+--------+
| 1  | 1     | firstname    | 0.1    |
+----+-------+--------------+--------+
| 2  | 1     | lastname     | 0.1    |
+----+-------+--------------+--------+

I just want

data_entry
+----+----------+----------+
| id | firstname|lastnamer |
+----+----------+----------+
| 2  | Sunil    | Chaudhari|
+----+----------+----------+
| 3  | Pranil   | Chaudhari|
+----+------+--------------+

[Subqueries with EXISTS or NOT EXISTS](http://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html) — bansi, Dec 10 '15 at 04:41

Jibin Balachandran · Accepted Answer · 2015-12-10T04:48:04.570

2

Maybe this will work:

SELECT * FROM data_entry 
WHERE  id NOT IN (SELECT  de_id FROM task_detail)

edited Dec 10 '15 at 04:48

answered Dec 10 '15 at 04:42

Jibin Balachandran

3,381
1
24
38

@Jain is right, Sorry by mistake I had taken id instead of de_id. – Jibin Balachandran Dec 10 '15 at 04:49
one up for you bro :) – Jain Dec 10 '15 at 05:03

score 2 · Answer 2 · edited May 23 '17 at 12:07

2

In most cases joins are faster than sub queries ref: sql join vs sub query, so you can also use left join:

select de.* from data_entry de left join task_detail td on td.de_id = de.id where td.id is null

edited May 23 '17 at 12:07

Community

1
1

answered Dec 10 '15 at 04:51

Chetan Ameta

7,696
3
29
44

@AnilChaudhari absolute pleasure :) – Chetan Ameta Dec 10 '15 at 05:25

Jain · Answer 3 · 2015-12-10T05:00:35.377

1

Using subquery.

SELECT * FROM data_entry WHERE id NOT IN (SELECT de_id FROM task_detail)

Using left join

SELECT de.* FROM data_entry de LEFT JOIN task_detail td ON de.id=td.de_id WHERE td.field_name IS NULL

edited Dec 10 '15 at 05:00

answered Dec 10 '15 at 04:44

Jain

854
5
15

1

@AnilChaudhari accept j19 answer, He gave it first. But first you should run both queries and check which one is fast on your tables. And do share the results. :) – Jain Dec 10 '15 at 05:02
Obviously .. joins are faster than subquiries. I have 6k and 40k records respectively and join's execution time is faster than subqueries.. I have tested in my SQLyog.. – Anil Chaudhari Dec 10 '15 at 05:18
How about using NOT EXISTS?? @Jain – Jibin Balachandran Dec 10 '15 at 05:26
@J19 Have a look at .... http://stackoverflow.com/questions/34193704/how-to-find-data-from-one-table-which-dont-exist-in-another-table/34194373#34194373 – Anil Chaudhari Dec 10 '15 at 05:51
@AnilChaudhari Ya bro, Thanks – Jibin Balachandran Dec 10 '15 at 05:54

score 1 · Answer 4 · answered Dec 10 '15 at 05:30

1

You can try this:

SELECT * FROM data_entry 
WHERE NOT EXISTS
(SELECT * FROM task_detail WHERE de_id=data_entry.id)

answered Dec 10 '15 at 05:30

Jibin Balachandran

3,381
1
24
38

@Anil Chaudhari Check this one also – Jibin Balachandran Dec 10 '15 at 05:31
1

Using Subqueries : Total results 1464 Showing rows 0 - 24 (1464 total, Query took 0.0170 seconds.) Using Left Join : Total results 1464 Showing rows 0 - 24 (1464 total, Query took 0.0020 seconds.) Using NOT EXISTS : Showing rows 0 - 24 (1464 total, Query took 0.2390 seconds.) – Anil Chaudhari Dec 10 '15 at 05:43

score 0 · Answer 5 · answered Dec 10 '15 at 04:52

0

select t1.* from data_entry t1 join data_entry t2 on t1.id = t2.de_id where t1.id !=  t2.de_id

answered Dec 10 '15 at 04:52

ashish mulani

197
1
14

How to find data from one table which don't exist in another table?

5 Answers5