Awesome q2a theme

Fetch from two tables in SQL?

0 like 0 dislike
68 views
This 2 tables table1 and table2, the fields are the same. How to get a sample
1. rows that exist in table1 and in table2
2. rows that are unique for each table and are not found in another?

Tried to use NOT IN, but to no avail.

SELECT * FROM table1 WHERE * NOT IN (SELECT * FROM table2);
by | 68 views

2 Answers

0 like 0 dislike
Something about the field is not a reality, in the first case, so be it.
select t1.*, t2.* from table1 t1 join table2 t2 on t2.field1 = t1.field1 and t2.field2 = t1.field2 and ... -- compare all fields

In the second you subtract:
select t1.* from table1 t1 UNION select t2.* from table2 t2 MINUS select t1.* from table1 t1 join table2 t2 on t2.field1 = t1.field1 and t2.field2 = t1.field2 and ... -- compare all fields
by
0 like 0 dislike
on the first issue might work (depending on DB or SQL standard):

select t1.* from table1 t1
INTERSECT
select t2.* from table2 t2
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
asked May 2, 2019 by atamahfmkey
0 like 0 dislike
2 answers
110,608 questions
257,187 answers
0 comments
40,796 users