MySQL内外连接

文章目录

• • 1. 内连接
  • 2. 外连接
  • • 2.1 左外连接
    • 2.2 右外连接

1. 内连接

内连接实际上就是利用where子句对两种表形成的笛卡尔积进行筛选

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

采用oracle的测试表scott

显示ALLEN名字和部门名称：

mysql> select ename,dname from emp,dept where emp.deptno=dept.deptno and ename='ALLEN';
+-------+-------+
| ename | dname |
+-------+-------+
| ALLEN | SALES |
+-------+-------+

# 内连接标准写法
mysql> select ename, dname from emp inner join dept on emp.deptno=dept.deptno and ename='ALLEN';
+-------+-------+
| ename | dname |
+-------+-------+
| ALLEN | SALES |
+-------+-------+

2. 外连接

2.1 左外连接

使用联合查询时，让左侧的表完全显示，不做任何过滤

语法：

select 字段 from 表1 left join 表2 on 连接条件 and 其他条件;

创建示例表：

mysql> create table stu (
    -> id int,
    -> name varchar(20)
    -> );
mysql> insert into stu values (1, 'zhangsan'), (2, 'lisi'), (3, 'wangwu'), (4, 'zhaoliu');

mysql> create table exam ( 
    -> id int,
    -> grade char(1)
    -> );
mysql> insert into exam values (1, 'A'), (2, 'B'), (3, 'D'), (9, 'C');

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来：

mysql> select * from stu left join exam on stu.id=exam.id;
+------+----------+------+-------+
| id   | name     | id   | grade |
+------+----------+------+-------+
|    1 | zhangsan |    1 | A     |
|    2 | lisi     |    2 | B     |
|    3 | wangwu   |    3 | D     |
|    4 | zhaoliu  | NULL | NULL  |
+------+----------+------+-------+

2.2 右外连接

联合查询时，让右侧的表完全显示

语法：

select 字段 from 表1 right join 表2 on 连接条件;

对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来：

mysql> select * from stu right join exam on stu.id=exam.id;
+------+----------+------+-------+
| id   | name     | id   | grade |
+------+----------+------+-------+
|    1 | zhangsan |    1 | A     |
|    2 | lisi     |    2 | B     |
|    3 | wangwu   |    3 | D     |
| NULL | NULL     |    9 | C     |
+------+----------+------+-------+

列出部门名称和这些部门的员工信息，同时列出没有员工的部门：

# 左外连接
mysql> select dname, ename, dept.deptno from dept left join emp on dept.deptno=emp.deptno order by dept.deptno asc;
+------------+--------+--------+
| dname      | ename  | deptno |
+------------+--------+--------+
| ACCOUNTING | CLARK  |     10 |
| ACCOUNTING | MILLER |     10 |
| ACCOUNTING | KING   |     10 |
| RESEARCH   | FORD   |     20 |
| RESEARCH   | JONES  |     20 |
| RESEARCH   | SMITH  |     20 |
| RESEARCH   | ADAMS  |     20 |
| RESEARCH   | SCOTT  |     20 |
| SALES      | TURNER |     30 |
| SALES      | MARTIN |     30 |
| SALES      | ALLEN  |     30 |
| SALES      | JAMES  |     30 |
| SALES      | BLAKE  |     30 |
| SALES      | WARD   |     30 |
| OPERATIONS | NULL   |     40 |
+------------+--------+--------+

# 右外连接
mysql> select dname, ename, dept.deptno from emp right join dept on dept.deptno=emp.deptno order by dept.deptno asc;
+------------+--------+--------+
| dname      | ename  | deptno |
+------------+--------+--------+
| ACCOUNTING | CLARK  |     10 |
| ACCOUNTING | MILLER |     10 |
| ACCOUNTING | KING   |     10 |
| RESEARCH   | FORD   |     20 |
| RESEARCH   | JONES  |     20 |
| RESEARCH   | SMITH  |     20 |
| RESEARCH   | ADAMS  |     20 |
| RESEARCH   | SCOTT  |     20 |
| SALES      | TURNER |     30 |
| SALES      | MARTIN |     30 |
| SALES      | ALLEN  |     30 |
| SALES      | JAMES  |     30 |
| SALES      | BLAKE  |     30 |
| SALES      | WARD   |     30 |
| OPERATIONS | NULL   |     40 |
+------------+--------+--------+