Two tables...
Table 1:
Name Eyes Hair
---- ---- ----
Able Blue Cyan
Bill Cyan Blue
Sara Blue Blue
Table 2:
Name Hand Foot
---- ---- ----
Carl Left Right
Sara Right Left
Can MySQL perform a "joined" or "unioned" or whatever query that can produce:
Result:
Name Eyes Hair Hand Foot
---- ---- ---- ---- ----
Able Blue Cyan null null
Bill Cyan Blue null null
Carl null null Left Right
Sara Blue Blue Right Left
This looks like a "full outer" join operation. Unfortunately, MySQL doesn't provide syntax to for this operation in a single SELECT statement.
Fortunately, it is possible to emulate a full outer join, using two SELECT statements.
Assuming that the `name` column is the primary key, or at least guaranteed to be unique...
There are essentially three possible conditions for a given name:
(The fourth condition, name value doesn't exist in either table1 or table2, we're not going to concern ourselves with, since there's no row source for e.g. Javier.)
To get rows that satisfy the first condition, we can use an INNER JOIN
The second condition, we can include those rows in the same SELECT by converting the INNER JOIN to a LEFT OUTER JOIN.
The third condition, we will need a separate SELECT, which can use an anti-join pattern. (We could also use a NOT EXISTS or a NOT IN, but this particular specification is one that anti-join is perfectly suited to.)
We can use a UNION ALL set operation to combine the results from the two SELECT statements.
We'll get the rows first, and then work on the expressions to get the columns
For the first query
SELECT t1.name
FROM table1 t1
LEFT
JOIN table2 t2
ON t2.name = t1.name
For the second query
SELECT a2.name
FROM table2 a2
LEFT
JOIN table1 a1
ON a1.name = a2.name
WHERE a1.name IS NULL
The trick here (the anti-join pattern) is the condition in the WHERE clause, that excludes any rows that found a match. So we are left with rows from a2 that didn't have a matching row in a1.
To use a UNION ALL set operation to combine those, the number of columns and datatypes of the columns have to match. Filling out the expressions in the SELECT lists, and adding an ORDER BY clause, we get something like this:
(not tested)
(
SELECT t1.name
, t1.eyes
, t1.hair
, t2.hand
, t2.foot
FROM table1 t1
LEFT
JOIN table2 t2
ON t2.name = t1.name
)
UNION ALL
(
SELECT a2.name
, a1.eyes
, a1.hair
, a2.hand
, a2.foot
FROM table2 a2
LEFT
JOIN table1 a1
ON a1.name = a2.name
WHERE a1.name IS NULL
)
ORDER BY `name`
Note that to get those ordered, we add parenthesis around each SELECT and follow the last one with an ORDER BY clause.
Again, in MySQL this requires a minimum of two SELECT statements.
There are other query patterns that can return an equivalent result, but those will require more than two SELECT.
Since there's no way I can test this, please be aware this might not work the first time you try it, and that I would need for you to post up the error you get so that I know (or you could create a SqlFiddle with some dummy data), but you should be able to do so like this:
Select Name, Eyes, Hair, Null as Hand, Null as Foot from Table1
Union
Select Name, Null as Eyes, Null as Hair, Hand, Foot from Table2
However, I think the best thing for you to do is to reconsider the way your database is set up currently. I can almost guarantee you'll run into problems.
insert table1 (name,eyes,hair) values
('Able','Blue','Cyan'),
('Bill','Cyan','Blue'),
('Sara','Blue','Blue');
insert table2 (name,hand,foot) values
('Carl','Left','Right'),
('Sara','Right','Left');
Select Name, Eyes, Hair, Null as Hand, Null as Foot from table1
where not exists (select * from table2 where table2.name=table1.name)
union
Select Name, Null as Eyes, Null as Hair, Hand, Foot from table2
where not exists (select * from table1 where table1.name=table2.name)
union
select t1.Name,t1.Eyes,t1.Hair,t2.Hand,t2.Foot
from table1 t1
join table2 t2
on t2.Name=t1.Name;
+------+------+------+-------+-------+
| Name | Eyes | Hair | Hand | Foot |
+------+------+------+-------+-------+
| Able | Blue | Cyan | NULL | NULL |
| Bill | Cyan | Blue | NULL | NULL |
| Carl | NULL | NULL | Left | Right |
| Sara | Blue | Blue | Right | Left |
+------+------+------+-------+-------+
4 rows in set (0.00 sec)
The query as I did it has 3 parts to the union. The first is that in table1 not in table2. The second is vice-versa. The third part is that in common (join). For the join, the t1 and t2 represent simple aliases for the table names.
For the first two parts of the union I used a not exists clause as opposed to a NOT IN clause as the latter is something I never use due to being over paranoid of dangerous NOT IN results. If one doesn't know their data extremely well, NOT IN can return unexpected results and waste a ton of time debugging in situations of data nullability.
SELECT FROM table1
FULL OUTER JOIN table2
ON table1.name = table2.name
WHERE table1.name = null
OR table2.name = null
