作者:LAMP小白 点击:1923 发布日期:2012-10-26 23:57:00 返回列表
内联结只显示在两个数据表里能找到的匹配数据行,外联结除了显示同样的匹配结果,还可以把其中一个数据表在另一个数据表里没有匹配的数据行也显示出来.LEFT JOIN显示右数据表中没有的匹配数据,RIGHT JOIN显示左数据表中没有的数据
mysql> SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.i1 = t2.i2;
+----+----+----+----+
| i1 | c1 | i2 | c2 |
+----+----+----+----+
| 2 | b | 2 | c |
| 3 | c | 3 | b |
+----+----+----+----+
2 rows in set (0.04 sec)
mysql> SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2;
+----+----+------+------+
| i1 | c1 | i2 | c2 |
+----+----+------+------+
| 1 | a | NULL | NULL |
| 2 | b | 2 | c |
| 3 | c | 3 | b |
+----+----+------+------+
3 rows in set (0.04 sec)
LEFT JOIN需要注意一个问题,如果右数据表里的数据列没有全部被定义为NOT NULL,结果集里的数据行就可能不能反映真实情况
LEFT JOIN可以再只想找出右数据表里没有匹配的左数据表的行
mysql> SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 is NULL;
+----+----+------+------+
| i1 | c1 | i2 | c2 |
+----+----+------+------+
| 1 | a | NULL | NULL |
+----+----+------+------+
1 row in set (0.00 sec)
我们还能将t2的结果从结果集中去掉,因为他们没有什么意义
mysql> SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 is NULL;
+----+----+
| i1 | c1 |
+----+----+
| 1 | a |
+----+----+
1 row in set (0.00 sec)
上一篇:内存究竟有多快 下一篇:快递查询API