MySQL 的 JOIN 查詢

語言: CN / TW / HK

本文已參與「掘力星計劃」,贏取創作大禮包,挑戰創作激勵金。

本文主要是講述 SQL 的語法結構,以及 SQL 的解析過程。還有就是 7 種查詢語法介紹。

SQL 語句

SQL 語法格式

語法格式如下: image.png

SQL 解析過程

SQL 執行過程 image.png SQL 解析過程 SQL 解析過程.png

JSON 語句

JSON 圖解

SQL 圖解如下 image.png

建表SQL

``sql create databaseoemp`;

use oemp;

部門表

create table tb_dept ( id int(11) not null auto_increment, name varchar(30) default null, storey varchar(40) default null, primary key(id) ) engine = innodb auto_increment=1 default charset=utf8;

員工表

create table tb_emp ( id int(11) not null auto_increment, name varchar(30) default null, dept_id int(11) default null, primary key(id), key idx_dept_id(dept_id)

, constraint fk_dept_id foregign key(dept_id) references tb_dept (id)

) engine = innodb auto_increment=1 default charset=utf8;

部門資料

insert into tb_dept(id, name, storey) values('1', 'RD', '11'); insert into tb_dept(id, name, storey) values('2', 'HR', '12'); insert into tb_dept(id, name, storey) values('3', 'MK', '13'); insert into tb_dept(id, name, storey) values('4', 'MIS', '14'); insert into tb_dept(id, name, storey) values('5', 'FD', '15');

員工資料

insert into tb_emp(name, dept_id) values('z3', 1); insert into tb_emp(name, dept_id) values('z4', 1); insert into tb_emp(name, dept_id) values('z5', 1);

insert into tb_emp(name, dept_id) values('w5', 2); insert into tb_emp(name, dept_id) values('w6', 2);

insert into tb_emp(name, dept_id) values('s7', 3); insert into tb_emp(name, dept_id) values('s8', 4); insert into tb_emp(name, dept_id) values('s9', 51); ```

7 種 JOIN 查詢

```sql

關聯查詢

select * from tb_dept; select * from tb_emp;

內連線查詢

select * from tb_emp a inner join tb_dept b on a.dept_id = b.id;

左連線查詢

select * from tb_emp a left join tb_dept b on a.dept_id = b.id;

右連線查詢

select * from tb_emp a right join tb_dept b on a.dept_id = b.id;

左差集查詢

select * from tb_emp a left join tb_dept b on a.dept_id = b.id where b.id is null;

右差集查詢

select * from tb_emp a right join tb_dept b on a.dept_id = b.id where a.id is null;

全連線查詢 (全集)(MySQL不支援 full outer join 語法)

oracle: select * from tb_emp a full outer join tb_dept b on a.dept_id = b.id;

union 拼接且去重

select * from tb_emp a left join tb_dept b on a.dept_id = b.id union select * from tb_emp a right join tb_dept b on a.dept_id = b.id;

A/B獨有的資料(差集)

select * from tb_emp a left join tb_dept b on a.dept_id = b.id where b.id is null union select * from tb_emp a right join tb_dept b on a.dept_id = b.id where a.id is null; ```

1. left join

sql #左連線查詢 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id;

2. right join

```sql

右連線查詢

select * from tb_emp a right join tb_dept b on a.dept_id = b.id; ```

3. 交集 (inner join)

```sql

關聯查詢

select * from tb_dept; select * from tb_emp;

內連線查詢

select * from tb_emp a inner join tb_dept b on a.dept_id = b.id; ```

4. 左差集

```sql

左差集查詢

select * from tb_emp a left join tb_dept b on a.dept_id = b.id where b.id is null; ```

5. 右差集

```sql

右差集查詢

select * from tb_emp a right join tb_dept b on a.dept_id = b.id where a.id is null; ```

6. 全集

```sql

全連線查詢 (全集)(MySQL不支援 full outer join 語法)

oracle: select * from tb_emp a full outer join tb_dept b on a.dept_id = b.id;

union 拼接且去重

select * from tb_emp a left join tb_dept b on a.dept_id = b.id union select * from tb_emp a right join tb_dept b on a.dept_id = b.id; ```

7. 差集

```sql

A/B獨有的資料(差集)

select * from tb_emp a left join tb_dept b on a.dept_id = b.id where b.id is null union select * from tb_emp a right join tb_dept b on a.dept_id = b.id where a.id is null; ```

參考文件