How to combine results from multiple tables with different columns?

Question

I have several tables with different numbers and types of columns, and a single column in common. ``` +--------+---------+------------+-------------+ | person | beardID | beardStyle | beardLength | +--------+---------+------------+-------------+ +--------+-------------+----------------+ | person | moustacheID | moustacheStyle | +--------+-------------+----------------+ ``` I want to fetch all the results that match a given value of the shared column. I can do it using multiple select statements like this: ``` SELECT * FROM beards WHERE person = "bob" ``` and ``` SELECT * FROM moustaches WHERE person = "bob" ``` But this requires multiple mysql API calls, which seems inefficient. I was hoping I could use UNION ALL to get all the results in a single API call, but UNION requires that the tables have the same number and similar type of columns. I could write a SELECT statement that would manually pad the results from each table by adding columns with NULL values, but that would quickly ge…

Accepted Answer

this should be working fine:

```
SELECT * FROM `beards` b LEFT OUTER JOIN `mustaches` ON (0) WHERE  person = "bob"
UNION ALL
SELECT * FROM `beards` b RIGHT OUTER JOIN `mustaches` ON (0) WHERE  person = "bob"
```

you don't have to handle the columns by yourself. the left and right outer join do this job. unfortunately mysql doesn't have a full join. that's why you have to do it this way with a union

```
SELECT * FROM `customer` b LEFT OUTER JOIN `charges` ON (0) LEFT OUTER JOIN `day` ON (0)
UNION
SELECT * FROM `customer` b RIGHT OUTER JOIN `charges` ON (0) LEFT OUTER JOIN `day` ON (0)
UNION
SELECT * FROM `customer` b LEFT OUTER JOIN `charges` ON (0) RIGHT OUTER JOIN `day` ON (0)
```

this is a local test i made

How to combine results from multiple tables with different columns?

Solution

Problem