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 |
+------------+--------+--------+