mysql innerjoin 和 leftjoin

1, cross join 交叉连接

select t1.*,t2.* from table1 t1,table2 t2;

select t1.*,t2.* from table1 t1 cross join table2 t2;

我们把上述"没有任何限制条件的连接方式"称之为"交叉连接"，"交叉连接"后得到的结果跟线性代数中的"笛卡尔乘积"一样。

2, inner join 内链接

select t1.*,t2.* from table1 t1,table2 t2 where t1.id = t2.id;
select t1.*,t2.* from table1 t1 inner join table2 t2 on t1.id = t2.id;

"有条件的" 交叉链接

3,左外链接 left join

select t1.*,t2.* from table1 t1 left join table2 t2 on t1.id = t2.id;

如果连接字段在右表中不唯一,左外链接会出现重复列情况, 
所以一般用主键进行关联,注意主表的选择;

mysql> select * from stu;
+----+-------+
| id | name  |
+----+-------+
|  1 | Kobe  |
|  2 | James |
|  3 | Bosh  |
+----+-------+
3 rows in set (0.03 sec)

mysql> select * from class;
+----+------+------+
| id | s_id | c_id |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |    2 |
|  3 |    2 |    5 |
+----+------+------+
3 rows in set (0.01 sec)

mysql> select * from stu left join class on stu.id= class.s_id;
+----+-------+------+------+------+
| id | name  | id   | s_id | c_id |
+----+-------+------+------+------+
|  1 | Kobe  |    1 |    1 |    1 |
|  1 | Kobe  |    2 |    1 |    2 |
|  2 | James |    3 |    2 |    5 |
|  3 | Bosh  | NULL | NULL | NULL |
+----+-------+------+------+------+
4 rows in set (0.03 sec)