SELECT t1.id,
t1.value,
t2.value2
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
UNION
SELECT t2.id,
t1.value,
t2.value2
FROM TABLE1 t1
RIGHT JOIN TABLE2 t2 ON t2.id = t1.id
该UNION
操作者除去行/记录重复的,所以你必须列适当地定义/列表。
DROP TABLE IF EXISTS `example`.`table1`;
CREATE TABLE `example`.`table1` (
`id` int(10) unsigned NOT NULL default '0',
`value` varchar(45) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO table1 VALUES (1, 'a'), (2, 'c'), (3, 'e');
DROP TABLE IF EXISTS `example`.`table2`;
CREATE TABLE `example`.`table2` (
`id` int(10) unsigned NOT NULL default '0',
`value2` varchar(45) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO table2 VALUES (1, 'b'), (3, 'd'), (4, 'f');
编辑:上面的固定线