mysql - 如何从表 A 和 B 中获取所有结果？ (外连接不存在？)

Question

最简单的解释方式是举个例子:

mysql> select * from table_a left join table_b on col_a=col_b;
+-------+-------+
| col_a | col_b |
+-------+-------+
|     1 |  NULL |
|     2 |  NULL |
|     3 |     3 |
|     4 |     4 |
+-------+-------+

mysql> select * from table_a right join table_b on col_a=col_b;
+-------+-------+
| col_a | col_b |
+-------+-------+
|     3 |     3 |
|     4 |     4 |
|  NULL |     5 |
|  NULL |     6 |
+-------+-------+

但是我如何得到这个呢？

mysql> select * from table_a ???? table_b on col_a=col_b;
+-------+-------+
| col_a | col_b |
+-------+-------+
|     1 |  NULL |
|     2 |  NULL |
|     3 |     3 |
|     4 |     4 |
|  NULL |     5 |
|  NULL |     6 |
+-------+-------+

---

@Abe 的结构:

CREATE TABLE IF NOT EXISTS `table_a` (
  `col_a` int(11) NOT NULL,
  PRIMARY KEY (`col_a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `table_a` (`col_a`) VALUES
(1),
(2),
(3),
(4);

CREATE TABLE IF NOT EXISTS `table_b` (
  `col_b` int(11) NOT NULL,
  PRIMARY KEY (`col_b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `table_b` (`col_b`) VALUES
(3),
(4),
(5),
(6);

Answer 1

由于 MySQL 中没有 FULL OUTER JOIN 选项，我认为您只能使用 UNION:

select * from table_a left join table_b on col_a=col_b
UNION 
select * from table_a right join table_b on col_a=col_b