MYSQL - how to merge this 2 tables to get a specific table output

Question

I have been struggling and would like some ideas/thoughts on how to do this. Unfortunately, I inherited these tables from a former coworker and need to work with what I got. I want to some how merge Table A with Table B to get Table C.

Table A:

ID	ID_P	IP_Type	Name
1	Cat	Animal	Henry
2	Cat	Apple
3	Cat	Bananas
4	Shark	Animal	George
5	Cat	Animal	Richard

question from:https://stackoverflow.com/questions/65895048/mysql-how-to-merge-this-2-tables-to-get-a-specific-table-output

Answer 1

You will need a UNION between the full contents of table A and you can use a LEFT JOIN between A and B to look for non-existing records based on ID_P Name for the other half of the UNION.

Use literal NULLs to fill the columns that do not exist in the B side of the union, to produce the same column set.

-- Take all the cols from table A
SELECT
  ID,
  ID_P,
  IP_Type,
  Name
FROM tableA
-- Combine with a filtered set from B
UNION
SELECT
  NULL AS ID,
  b.ID_P,
  NULL AS ID_Type,
  b.name
FROM
  tableB b
  -- Join on ID_P and Name
  LEFT JOIN tableA a ON b.ID_P = a.ID_P AND b.Name = A.Name
-- NULLs on the left join mean non-matching
WHERE a.ID_P IS NULL

Here it is in action