mysql full join 解决方案

mysql full join 解决方案

今天在跟群里面的同学聊天的时候，聊到full join。mysql是不支持full join的，那要实现mysql的full join怎么办呢？百度找来了一个用left join + right join +union的方式，但是这但是同学的sql比较复杂，写起来就一大堆了，于是乎就想找另外的解决方式。
先贴测试数据。我比较懒，就直接网上找了一个拿过来用了，包括他提到的left join + right join + union的方式，原文地址是：http://zhidao.baidu.com/link?url=7NJ3e4UWz5IHFeg2joVKRf7vuqGUFK_Rl-c49IrJf-an2WOJQoYj9-ryI9W7ScURqOuYlOfdKRjq8M1178-fD_

-- 学生表
CREATE TABLE student (
  SNO    INT,
  SNAME  VARCHAR(10),
  ICNO   INT
);

-- 食堂IC卡表
CREATE TABLE IC (
  ICNO     INT,
  ICNAME   VARCHAR(10),
  ICMoney  INT
);

INSERT INTO student VALUES(1, '张三', 1   );
INSERT INTO student VALUES(2, '李四', 2   );
INSERT INTO student VALUES(3, '王五', NULL);


INSERT INTO IC VALUES(1, '张三', 500 );
INSERT INTO IC VALUES(2, '李四', 250 );
INSERT INTO IC VALUES(3, '赵老师', 600);

下面是 SQL Server 使用 full join 的结果

SELECT
  student.SNO,
  student.SNAME,
  IC.ICNAME,
  IC.ICMoney
FROM
  student
    FULL JOIN IC
      ON (student.ICNO = IC.ICNO);
go
SNO         SNAME      ICNAME     ICMoney
----------- ---------- ---------- -----------
          1 张三         张三                 500
          2 李四         李四                 250
          3 王五        NULL              NULL
       NULL NULL       赵老师                600

(4 行受影响)

下面是 MySQL 的 LEFT JOIN + UNION + RIGHT JOIN 的方式。

mysql> SELECT
    ->   student.SNO,
    ->   student.SNAME,
    ->   IC.ICNAME,
    ->   IC.ICMoney
    -> FROM
    ->   student
    ->     LEFT JOIN IC
    ->       ON (student.ICNO = IC.ICNO)
    -> UNION
    -> SELECT
    ->   student.SNO,
    ->   student.SNAME,
    ->   IC.ICNAME,
    ->   IC.ICMoney
    -> FROM
    ->   student
    ->     RIGHT JOIN IC
    ->       ON (student.ICNO = IC.ICNO);
+------+-------+--------+---------+
| SNO  | SNAME | ICNAME | ICMoney |
+------+-------+--------+---------+
|    1 | 张三  | 张三   |     500 |
|    2 | 李四  | 李四   |     250 |
|    3 | 王五  | NULL   |    NULL |
| NULL | NULL  | 赵老师 |     600 |
+------+-------+--------+---------+
4 rows in set (0.01 sec)

以上是上面那个链接给的方式。这里留个备份，侵删。下面是我自己折腾的方法，我用1000条数据测试了一下速度，比上面的稍微快一点。更多的测试没做，留着当一个思路吧。

SELECT 
       student.SNO,
       student.SNAME,
       IC.ICNAME,
       IC.ICMoney
     FROM
       student
         LEFT JOIN IC
           ON (student.ICNO = IC.ICNO)
UNION ALL
SELECT
    NULL,
    NULL,
    IC.ICNAME,
    IC.ICMoney
FROM
 IC
    WHERE NOT EXISTS (SELECT 1 FROM student
         WHERE (student.ICNO = IC.ICNO));

大概想法就是，left join已经将full的大部分数据查到了，缺的是右表少的数据，那可不可以单独把右表再查一次。而且union ALL肯定比union快。结果就不贴了，跟上面一样的。