连接查询

笛卡尔积

• 有两个集合A和B，笛卡尔积表示A集合中的元素和B集合中的元素
  任意相互关联产生的所有可能的结果
• Sql中笛卡尔积标识语法
  • select 字段 from 表1,表2[,表N];
  • select 字段 from 表1 join 表2 [join 表N];

/*
mysql> select * from t_team;
+----+-----------+
| id | team_name |
+----+-----------+
| 1 | 架构组 |
| 2 | 测试组 |
| 3 | java组 |
| 4 | 前端组 |
+----+-----------+
4 rows in set (0.00 sec)
t_employee 表5条记录，如下：
mysql> select * from t_employee;
+----+---------------+---------+
| id | emp_name | team_id |
+----+---------------+---------+
| 1 | 路⼈甲Java | 1 |
| 2 | 张三 | 2 |
| 3 | 李四 | 3 |
| 4 | 王五 | 0 |
| 5 | 赵六 | 0 |
+----+---------------+---------+
5 rows in set (0.00 sec)
*/
select * from t_team,t_employee;
/*
mysql> select * from t_team,t_employee;
+----+-----------+----+---------------+---------+
| id | team_name | id | emp_name | team_id |
+----+-----------+----+---------------+---------+
| 1 | 架构组 | 1 | 路⼈甲Java | 1 |
| 2 | 测试组 | 1 | 路⼈甲Java | 1 |
| 3 | java组 | 1 | 路⼈甲Java | 1 |
| 4 | 前端组 | 1 | 路⼈甲Java | 1 |
| 1 | 架构组 | 2 | 张三 | 2 |
| 2 | 测试组 | 2 | 张三 | 2 |
| 3 | java组 | 2 | 张三 | 2 |
| 4 | 前端组 | 2 | 张三 | 2 |
| 1 | 架构组 | 3 | 李四 | 3 |
| 2 | 测试组 | 3 | 李四 | 3 |
| 3 | java组 | 3 | 李四 | 3 |
| 4 | 前端组 | 3 | 李四 | 3 |
| 1 | 架构组 | 4 | 王五 | 0 |
| 2 | 测试组 | 4 | 王五 | 0 |
| 3 | java组 | 4 | 王五 | 0 |
| 4 | 前端组 | 4 | 王五 | 0 |
| 1 | 架构组 | 5 | 赵六 | 0 |
| 2 | 测试组 | 5 | 赵六 | 0 |
| 3 | java组 | 5 | 赵六 | 0 |
| 4 | 前端组 | 5 | 赵六 | 0 |
+----+-----------+----+---------------+---------+
20 rows in set (0.00 sec)
*/

---

内连接

• 内连接相当于在笛卡尔积的基础上加上了连接的条件
• 当没有连接条件的时候，内连接上升为笛卡尔积
• sql
  • select 字段 from 表1 inner join 表2 on 连接条件;
  • select 字段 from 表1 join 表2 on 连接条件;
  • select 字段 from 表1, 表2 [where 关联条件];

-- 有条件内连接
select t1.emp_name,t2.team_name from t_employee t1 inner join
t_team t2 on t1.team_id = t2.id;
/*
+---------------+-----------+
| emp_name | team_name |
+---------------+-----------+
| 路⼈甲Java | 架构组 |
| 张三 | 测试组 |
| 李四 | java组 |
+---------------+-----------+
3 rows in set (0.00 sec)
*/
select t1.emp_name,t2.team_name from t_employee t1 join t_team
t2 on t1.team_id = t2.id;
/*
+---------------+-----------+
| emp_name | team_name |
+---------------+-----------+
| 路⼈甲Java | 架构组 |
| 张三 | 测试组 |
| 李四 | java组 |
+---------------+-----------+
3 rows in set (0.00 sec)
*/
select t1.emp_name,t2.team_name from t_employee t1, t_team t2
where t1.team_id = t2.id;
/*
+---------------+-----------+
| emp_name | team_name |
+---------------+-----------+
| 路⼈甲Java | 架构组 |
| 张三 | 测试组 |
| 李四 | java组 |
+---------------+-----------+
3 rows in set (0.00 sec)
*/

-- 无连接条件
select t1.emp_name,t2.team_name from t_employee t1 inner join
t_team t2;
/*
+---------------+-----------+
| emp_name | team_name |
+---------------+-----------+
| 路⼈甲Java | 架构组 |
| 路⼈甲Java | 测试组 |
| 路⼈甲Java | java组 |
| 路⼈甲Java | 前端组 |
| 张三 | 架构组 |
| 张三 | 测试组 |
| 张三 | java组 |
| 张三 | 前端组 |
| 李四 | 架构组 |
| 李四 | 测试组 |
| 李四 | java组 |
| 李四 | 前端组 |
| 王五 | 架构组 |
| 王五 | 测试组 |
| 王五 | java组 |
| 王五 | 前端组 |
| 赵六 | 架构组 |
| 赵六 | 测试组 |
| 赵六 | java组 |
| 赵六 | 前端组 |
+---------------+-----------+
20 rows in set (0.00 sec)
*/
-- 组合条件查询
select t1.emp_name,t2.team_name from t_employee t1 inner join
t_team t2 on t1.team_id = t2.id and t2.team_name = '架构组';
/*
+---------------+-----------+
| emp_name | team_name |
+---------------+-----------+
| 路⼈甲Java | 架构组 |
+---------------+-----------+
1 row in set (0.00 sec)
*/
select t1.emp_name,t2.team_name from t_employee t1 inner join
t_team t2 on t1.team_id = t2.id where t2.team_name = '架构组';
/*
+---------------+-----------+
| emp_name | team_name |
+---------------+-----------+
| 路⼈甲Java | 架构组 |
+---------------+-----------+
1 row in set (0.00 sec)
*/
select t1.emp_name,t2.team_name from t_employee t1, t_team t2
where t1.team_id = t2.id and t2.team_name = '架构组';
/*
+---------------+-----------+
| emp_name | team_name |
+---------------+-----------+
| 路⼈甲Java | 架构组 |
+---------------+-----------+
1 row in set (0.00 sec)
*/
-- 方式1：on中使用了组合条件。
-- 方式2：在连接的结果之后再进行过滤，相当于先获取连接的结果，然后使用where中的条件再对连接结果进行过滤。
-- 方式3：直接在where后面进行过滤。

• 总结
  • 内连接建议使用第3种语法
  • select 字段 from 表1, 表2 [where 关联条件];

---

外连接

• 外连接涉及到2个表，分为：主表和从表，要查询的信息主要来自于哪个表，谁就是主
  表。
• 外连接查询结果为主表中所有记录。如果从表中有和它匹配的，则显示匹配的值，这部分
  相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null
• 外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录
• 左外链接：使用left join关键字，left join左边的是主表
  • select 列 from 主表 left join 从表 on 连接条件;
• 右外连接：使用right join关键字，right join右边的是主表
• select 列 from 从表 right join 主表 on 连接条件;

-- 左连接
SELECT
  t1.emp_name,
  t2.team_name
FROM
  t_employee t1
LEFT JOIN
  t_team t2
ON
  t1.team_id = t2.id;
/*
+---------------+-----------+
| emp_name | team_name |
+---------------+-----------+
| 路⼈甲Java | 架构组 |
| 张三 | 测试组 |
| 李四 | java组 |
| 王五 | NULL |
| 赵六 | NULL |
+---------------+-----------+
*/
SELECT
  t1.emp_name,
  t2.team_name
FROM
  t_employee t1
LEFT JOIN
  t_team t2
ON
  t1.team_id = t2.id
WHERE
  t2.team_name IS NOT NULL;
/*
+---------------+-----------+
| emp_name | team_name |
+---------------+-----------+
| 路⼈甲Java | 架构组 |
| 张三 | 测试组 |
| 李四 | java组 |
+---------------+-----------+
3 rows in set (0.00 sec)
*/

-- 右连接
SELECT
  t2.team_name,
  t1.emp_name
FROM
  t_team t2
RIGHT JOIN
  t_employee t1
ON
  t1.team_id = t2.id;
/*
+-----------+---------------+
| team_name | emp_name |
+-----------+---------------+
| 架构组 | 路⼈甲Java |
| 测试组 | 张三 |
| java组 | 李四 |
| NULL | 王五 |
| NULL | 赵六 |
+-----------+---------------+
5 rows in set (0.00 sec)
*/
SELECT
  t2.team_name,
  t1.emp_name
FROM
  t_team t2
RIGHT JOIN
  t_employee t1
ON
  t1.team_id = t2.id
WHERE
  t2.team_name IS NOT NULL;
/*
+-----------+---------------+
| team_name | emp_name |
+-----------+---------------+
| 架构组 | 路⼈甲Java |
| 测试组 | 张三 |
| java组 | 李四 |
+-----------+---------------+
3 rows in set (0.00 sec)
*/

/*
mysql> select * from test1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> select * from test2;
+------+
| b |
+------+
| 3 |
| 4 |
| 5 |
+------+
3 rows in set (0.00 sec)
*/
-- 内连接
select * from test1 t1,test2 t2;
/*
+------+------+
| a | b |
+------+------+
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
+------+------+
9 rows in set (0.00 sec)
*/
select * from test1 t1,test2 t2 where t1.a = t2.b;
/*
+------+------+
| a | b |
+------+------+
| 3 | 3 |
+------+------+
1 row in set (0.00 sec)
*/
-- 左连接
select * from test1 t1 left join test2 t2 on t1.a = t2.b;
/*
+------+------+
| a | b |
+------+------+
| 3 | 3 |
| 1 | NULL |
| 2 | NULL |
+------+------+
3 rows in set (0.00 sec)
*/
select * from test1 t1 left join test2 t2 on t1.a>10;
/*
+------+------+
| a | b |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
+------+------+
3 rows in set (0.00 sec)
*/
select * from test1 t1 left join test2 t2 on 1=1;
/*
+------+------+
| a | b |
+------+------+
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
+------+------+
9 rows in set (0.00 sec)
*/