MySQL - 14表連接配接(内連接配接、外連接配接、自連接配接)

什麼是表連接配接

表連接配接(JOIN)是在多個表之間通過一定的連接配接條件，使表之間發生關聯，進而能從多個表之間擷取資料。

# 表連接配接文法
SELECT table1.column, table2.column 
FROM table1, table2
WHERE table1.column1 = table2.column2;      

學号	姓名	位址	…
2016001	張三	貴州貴陽
2016002	李芳	陝西興平
2016003	張曉燕	江西南昌

科目	學号	分數	…
離散數學	2016001	85
計算機	2016001	78
計算機	2016002	99

表連接配接的幾種方式

内連接配接：join 或 inner join

自連接配接：同一張表内的連接配接

外連接配接：左外連接配接，left join 右外連接配接，right join 全外連接配接，full join

各種表連接配接的差別

内連接配接

左連接配接

實戰案例

初始化表資料

drop table if exists score;
drop table if exists student;

create table student(
  stu_no varchar(20) not null primary key,
  name varchar(30),
  address varchar(150)
);

insert into student(stu_no, name, address) values('2019001', 'zhangsan', 'Shanghai');
insert into student(stu_no, name, address) values('2019002', 'lifang', 'xingping');
insert into student(stu_no, name, address) values('2019003', 'zhangxiao', 'changsha');

create table score(
  id int not null auto_increment primary key,
  course varchar(50),
  stu_no varchar(20),
  score int,
  foreign key(stu_no) references student(stu_no)
);

insert into score(course, stu_no, score) values('computer', '2019001', 99);
insert into score(course, stu_no, score) values('math', '2019001', 85);
insert into score(course, stu_no, score) values('computer', '2019002', 78);      

檢視表資料

mysql> select * from student;
+---------+-----------+----------+
| stu_no  | name      | address  |
+---------+-----------+----------+
| 2019001 | zhangsan  | Shanghai |
| 2019002 | lifang    | xingping |
| 2019003 | zhangxiao | changsha |
+---------+-----------+----------+
3 rows in set (0.00 sec)

mysql> select * from score;
+----+----------+---------+-------+
| id | course   | stu_no  | score |
+----+----------+---------+-------+
|  1 | computer | 2019001 |    99 |
|  2 | math     | 2019001 |    85 |
|  3 | computer | 2019002 |    78 |
+----+----------+---------+-------+
3 rows in set (0.00 sec)      

内連接配接

mysql> select A.stu_no, A.name, B.course, B.score
    -> from student A
    -> join score B on (A.stu_no = B.stu_no);
+---------+----------+----------+-------+
| stu_no  | name     | course   | score |
+---------+----------+----------+-------+
| 2019001 | zhangsan | computer |    99 |
| 2019001 | zhangsan | math     |    85 |
| 2019002 | lifang   | computer |    78 |
+---------+----------+----------+-------+
3 rows in set (0.00 sec)

mysql> select A.stu_no, A.name, B.course, B.score
    -> from student A
    -> inner join score B on (A.stu_no = B.stu_no);
+---------+----------+----------+-------+
| stu_no  | name     | course   | score |
+---------+----------+----------+-------+
| 2019001 | zhangsan | computer |    99 |
| 2019001 | zhangsan | math     |    85 |
| 2019002 | lifang   | computer |    78 |
+---------+----------+----------+-------+
3 rows in set (0.00 sec)

mysql> select A.stu_no, A.name, B.course, B.score
    -> from student A,  score B
    -> where A.stu_no = B.stu_no;
+---------+----------+----------+-------+
| stu_no  | name     | course   | score |
+---------+----------+----------+-------+
| 2019001 | zhangsan | computer |    99 |
| 2019001 | zhangsan | math     |    85 |
| 2019002 | lifang   | computer |    78 |
+---------+----------+----------+-------+
3 rows in set (0.00 sec)      

左連接配接

mysql> select A.stu_no, A.name, B.course, B.score
    -> from student A
    -> Left join score B on (A.stu_no = B.stu_no);
+---------+-----------+----------+-------+
| stu_no  | name      | course   | score |
+---------+-----------+----------+-------+
| 2019001 | zhangsan  | computer |    99 |
| 2019001 | zhangsan  | math     |    85 |
| 2019002 | lifang    | computer |    78 |
| 2019003 | zhangxiao | NULL     |  NULL |
+---------+-----------+----------+-------+
4 rows in set (0.00 sec)      

笛卡爾積

mysql> select A.stu_no, A.name, B.course, B.score
    -> from student A,  score B;
+---------+-----------+----------+-------+
| stu_no  | name      | course   | score |
+---------+-----------+----------+-------+
| 2019003 | zhangxiao | computer |    99 |
| 2019002 | lifang    | computer |    99 |
| 2019001 | zhangsan  | computer |    99 |
| 2019003 | zhangxiao | math     |    85 |
| 2019002 | lifang    | math     |    85 |
| 2019001 | zhangsan  | math     |    85 |
| 2019003 | zhangxiao | computer |    78 |
| 2019002 | lifang    | computer |    78 |
| 2019001 | zhangsan  | computer |    78 |
+---------+-----------+----------+-------+
9 rows in set (0.00 sec)