How do I combine two tables in MySQL keeping all columns?

Question

I have two tables with completely different structures. I want to query MYSQL to get all columns from both tables in a single query.

So for example, if I have a Person table and an Animal table, I want my query to return:

person_name|person_age|animal_type|animal_name
______________________________________________
Ryan       |31        |NULL        |NULL
Fred       |23        |NULL        |NULL
NULL       |NULL      |Cat         |Whiskers
NULL       |NULL      |Dog         |Wishbone

I only have basic knowledge of MYSQL and I couldn't understand most of the material I was reading and googling, and it didn't seem to match exactly what I want.

Thanks!

See UNION as in SELECT a,b,NULL,NULL FROM x UNION SELECT NULL,NULL,c,d FROM y — Strawberry, Mar 28 '15 at 16:32

score 3 · Accepted Answer · answered Mar 28 '15 at 16:32

3

You want a UNION ALL query that concatenates two result sets

SELECT person_name, person_age,
       NULL animal_type, NULL animal_name
  FROM person
 UNION ALL 
SELECT NULL person_name, NULL person_age,
       animal_type, animal_name
  FROM animal

Each SELECT query shapes its result set in a similar way, and then the UNION ALL puts them together.

answered Mar 28 '15 at 16:32

O. Jones

103,626
17
118
172

Thanks...any way to use `*` here so I don't have to list out everything? – Nick Manning Mar 28 '15 at 16:49

How do I combine two tables in MySQL keeping all columns?

1 Answers1