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      |
+----+---------+---------+

Very good demonstration and definition of the problem +1 – SaidbakR Nov 25 '12 at 01:50 — SaidbakR, Nov 25 '12 at 01:50

Bill Karwin · Accepted Answer · 2012-11-25T23:01:38.840

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!

the result seems close, but not the desired result mentions above. The formatting is not supported the same in this comment so I pasted the result in the question. — panofish, Nov 25 '12 at 02:27
@panofish `SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id) UNION SELECT * FROM table1 RIGHT JOIN table2 ON (table1.id = table2.id)` is basically the same as what BillKarwin states but achieves slightly different results.. may be more what you're needing. — Pebbl, Nov 25 '12 at 02:36
closer but still doesnt produce the desired table... perhaps this is an example that can only be done programmatically? — panofish, Nov 25 '12 at 21:48
Thanks Bill... the update worked. Interesting that you must name the columns explicity. — panofish, Nov 26 '12 at 19:12
Yes, `SELECT a, b, c UNION SELECT a, c, b` technically works, but doesn't do what we intend. :) — Bill Karwin, Nov 26 '12 at 20:56

score 0 · Answer 2 · answered Nov 25 '12 at 02:01

0

You need FULL OUTER JOIN:

SELECT coalesce(a.id, b.id), a.name, b.state
FROM table1 a
FULL OUTER JOIN table2 b ON (a.id = b.id)

answered Nov 25 '12 at 02:01

mvp

111,019
13
122
148

4

MySQL doesn't support `FULL OUTER JOIN` – Michael Berkowski Nov 25 '12 at 02:02

MySQL true row merge... not just a union

2 Answers2