MySQL true row merge... not just a union

Question

What is the mysql I need to achieve the result below given these 2 tables:

table1:

+----+-------+
| id | name  |
+----+-------+
|  1 | alan  |
|  2 | bob   |
|  3 | dave  |
+----+-------+

table2:

+----+---------+
| id | state   |
+----+---------+
|  2 | MI      |
|  3 | WV      |
|  4 | FL      |
+----+---------+

I want to create a temporary view that looks like this

desired result:

+----+---------+---------+
| id | name    | state   |
+----+---------+---------+
|  1 | alan    |         |
|  2 | bob     | MI      |
|  3 | dave    | WV      |
|  4 |         | FL      |
+----+---------+---------+

I tried a mysql union but the following result is not what I want.

create view table3 as
(select id,name,"" as state from table1)
union
(select id,"" as name,state from table2)

table3 union result:

+----+---------+---------+
| id | name    | state   |
+----+---------+---------+
|  1 | alan    |         |
|  2 | bob     |         |
|  3 | dave    |         |
|  2 |         | MI      |
|  3 |         | WV      |
|  4 |         | FL      |
+----+---------+---------+

Answer 1

You need to use an SQL operator called JOIN to get what you want.

JOIN is a fundamental part of the SQL language, as much as a while loop is for other programming languages.

Start here: A Visual Explanation of SQL Joins

Another way of thinking of JOIN vs. UNION is:

• UNION appends rows together.
• JOIN appends columns together.

For example:

SELECT * 
FROM table1
JOIN table2 USING (id);

This is a style of join that returns only the rows for which a matching id exists in both tables. But you want all of those id's for which a row exists in at least one table or the other.

SELECT * 
FROM table1
LEFT OUTER JOIN table2 USING (id);

This gets all the rows from table1, with their matching rows in table2. But to get the reverse, we'd need:

SELECT * 
FROM table1
RIGHT OUTER JOIN table2 USING (id);

SQL also defines a FULL OUTER JOIN which would do both at once, but unfortunately MySQL hasn't implemented that part of the SQL standard. But we can use UNION to combine the two types of joins. Union will eliminate duplicate rows by default.

update: I troubleshot this, and got it to work. It turns out you have to name the columns explicitly, to make sure they are in the same columns in both of the unioned queries. Here's the query that works, copied from my terminal window:

SELECT id, name, state
FROM table1
LEFT OUTER JOIN table2 USING (id)
UNION
SELECT id, name, state
FROM table1
RIGHT OUTER JOIN table2 USING (id);

It's a good habit in general to avoid using SELECT *, and this is just one more case where it's a good idea.

Also, I had left a superfluous semicolon in the query example before. That was just a typo on my part.

This query produces the following output, copied from my terminal window:

+----+------+-------+
| id | name | state |
+----+------+-------+
|  1 | alan | NULL  |
|  2 | bob  | MI    |
|  3 | dave | WV    |
|  4 | NULL | FL    |
+----+------+-------+

PS: Thank you for asking the question so clearly!