I need the records which doesn't matches in both tables

Question

I need those records in a table list which doesn't link to table t1 to table t2

I have tried inner join. I need the records without subquery

CREATE TABLE t1 (
    id INT PRIMARY KEY
);

CREATE TABLE t2 LIKE t1;

INSERT INTO t1(id) VALUES(1),(2),(3);

INSERT INTO t2(id) VALUES(2),(3),(4);

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

mysql> select * from t2;
+----+
| id |
+----+
|  2 |
|  3 |
|  4 |
+----+
3 rows in set (0.00 sec)

I need :

+----+
| id |
+----+
|  1 |
|  4 |
+----+

(Obviously--) This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. When you ask a question show what you have tried & researched. — philipxy, Jun 19 '19 at 22:40
Possible duplicate of [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — philipxy, Jun 19 '19 at 22:44

Willis Blackburn · Answer 1 · 2019-06-21T12:04:11.510

0

select * from t1 where not exists (select * from t2 where t1.id = t2.id)

or

select * from t1 where id not in (select id from t2)

and likewise for t2. Union them together if you want them in a single result set.

Update: subquery-less version:

select coalesce(t1.id, t2.id) from t1 full outer join t2 on (t1.id = t2.id)
where t1.id is null or t2.id is null

edited Jun 21 '19 at 12:04

answered Jun 19 '19 at 19:50

Willis Blackburn

8,068
19
36

Thanks. Comment for the First query: But I don't want to use a subquery. Comment for the Second query: There is a limit of in a conditional statement. – Er. Sujeet Kumar Jun 21 '19 at 10:46
Sorry, missed that. I added a solution that doesn't use a subquery. – Willis Blackburn Jun 21 '19 at 12:52

score 0 · Answer 2 · answered Jun 19 '19 at 19:54

0

you could try left join on the union of the id and check for null

select  t3.id  
from  (
  select  t1.id from t1 
  union 
  select t2.id  from t2 
) t3 
left join t1 on t1.id  = t3.id 
left join t2. on t2.id  = t3.id 
where t2.id is null  
or t1.id is null

answered Jun 19 '19 at 19:54

ScaisEdge

131,976
10
91
107

You have created a temp table using a subquery. Please solve without subquery. What if there were 1 lakh each table records then. – Er. Sujeet Kumar Jun 21 '19 at 10:51
@Er.SujeetKumar SO is not a fre coding service .. but a question/answer site .. based on your question my answer is a right solution .. .. you don't want a subuery .. then you should find a method for obtain all the id fro b9th the table and join with the table itself .. i think that a union is thye simplest way – ScaisEdge Jun 21 '19 at 11:12

I need the records which doesn't matches in both tables

2 Answers2