- --原始两张表数据如下:
+-------+---------+--+
| a.id | a.name |
+-------+---------+--+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 7 | y |
| 8 | u |
+-------+---------+--+
+-------+---------+--+
| b.id | b.name |
+-------+---------+--+
| 2 | bb |
| 3 | cc |
| 7 | yy |
| 9 | pp |
+-------+---------+--+
- --inner join(内关联) :返回结果只显示join关联上的
select * from a inner join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
+-------+---------+-------+---------+--+
- --outer join(外关联):显示所有的join结果
select * from a full outer join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 3 | c | 3 | cc |
| NULL | NULL | 9 | pp |
| 1 | a | NULL | NULL |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 2 | b | 2 | bb |
| 8 | u | NULL | NULL |
+-------+---------+-------+---------+--+
- --left join(左连接):以左表为准 显示左表所有内容 右边表与之join 关联上显示 不关联上显示null
select * from a left join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
+-------+---------+-------+---------+--+
- --right join(右关联):以右表为准 显示右表所有内容 左边表与之join 关联上显示 不关联上显示null
select * from a right join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
| NULL | NULL | 9 | pp |
+-------+---------+-------+---------+--+
- --left semi join:相当于内关联只显示左表的部分。
select * from a left semi join b on a.id = b.id;
select a.* from a inner join b on a.id=b.id;
+-------+---------+--+
| a.id | a.name |
+-------+---------+--+
| 2 | b |
| 3 | c |
| 7 | y |
+-------+---------+--+
- --cross join
--返回两个表的笛卡尔积结果,不需要指定关联键。
select a.*,b.* from a cross join b;