How to fetch uncommon records from two tables in mySQL without primary key-CodePudding

I have two tables, both have same number of columns with none of them having any primary key as below:

Table A

Name	Ranking	Genre	Category	Rated
M1	1	Comedy	Movie	G
M2	2	Action	Series	PG

Table B

Name	Ranking	Genre	Category	Rated
MX1	44	Thriller	Series	G
M2	2	Action	Series	PG

I need to fetch the uncommon records from both the tables, where Ranking and Name combined is unique for them.

I have tried using left and right join but it gives me all the records from either one table. My priority here is to fetch just the uncommon records as below:

Name	Ranking	Genre	Category	Rated
M1	1	Comedy	Movie	G
MX1	44	Thriller	Series	G

CodePudding user response：

You can union two outer joins. For example:

select a.*
from a
left join b on a.name = b.name and a.ranking = b.ranking
where b.name is null
union all
select b.*
from b
left join a on a.name = b.name and a.ranking = b.ranking
where a.name is null

CodePudding user response：

Try with inner join

SELECT *
FROM table_a
INNER JOIN table_b ON table_a.name = table_b.name AND table_a.ranking = table_b.ranking;