|
还是先 Create table 吧
create table emp(
id int not null primary key,
name varchar(10)
);
create table emp_dept(
dept_id varchar(4) not null,
emp_id int not null,
emp_name varchar(10),
primary key (dept_id,emp_id));
insert into emp() values
(1,Dennis-1),
(2,Dennis-2),
(3,Dennis-3),
(4,Dennis-4),
(5,Dennis-5),
(6,Dennis-6),
(7,Dennis-7),
(8,Dennis-8),
(9,Dennis-9),
(10,Dennis-10);
insert into emp_dept() values
(R&D,1,Dennis-1),
(DEv,2,Dennis-2),
(R&D,3,Dennis-3),
(Test,4,Dennis-4),
(Test,5,Dennis-5);
left join
-------------
select a.id,a.name,b.dept_id
from emp a left join emp_dept b on (a.id=b.emp_id);
# 挑出左边的 table emp 中的所有资料,即使 emp_dept 中没有的资料也挑出来,没有的就用 NULL 来显示,
# 也即显示资料是以左边的 table emp 中的资料为基础
mysql select a.id,a.name,b.dept_id
- from emp a left join emp_dept b on (a.id=b.emp_id);
+----+-----------+---------+
id name dept_id
+----+-----------+---------+
1 Dennis-1 R&D
2 Dennis-2 DEv
3 Dennis-3 R&D
4 Dennis-4 Test
5 Dennis-5 Test
6 Dennis-6 NULL
7 Dennis-7 NULL
8 Dennis-8 NULL
9 Dennis-9 NULL
10 Dennis-10 NULL
+----+-----------+---------+
# 挑出 table emp 中有而 table emp_dept 中没有的人员资料
select a.id,a.name,b.dept_id
from emp a left join emp_dept b on (a.id=b.emp_id)
where b.dept_id IS NULL;
mysql select a.id,a.name,b.dept_id
- from emp a left join emp_dept b on (a.id=b.emp_id)
- where b.dept_id IS NULL;
+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+
# 把 table emp_dept 放在左边的情形(当然以 emp_dept 中的数据为基础来显示资料,emp 中比emp_dept 中多的资料也就不会显示出来了):
select a.id,a.name,b.dept_id
from emp_dept b left join emp a on (a.id=b.emp_id);
mysql> select a.id,a.name,b.dept_id
-> from emp_dept b left join emp a on (a.id=b.emp_id);
+------+----------+---------+
| id | name | dept_id |
+------+----------+---------+
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&D |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+------+----------+---------+
>> right join
---------------
select a.id,a.name,b.dept_id
from emp a right join emp_dept b on (a.id=b.emp_id);
# 挑资料时以右边 table emp_dept 中的资料为基础来显示资料
mysql> select a.id,a.name,b.dept_id
-> from emp a right join emp_dept b on (a.id=b.emp_id);
+------+----------+---------+
| id | name | dept_id |
+------+----------+---------+
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&D |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+------+----------+---------+
5 rows in set (0.00 sec)
# 我们再把 table 的位置交换一下,再用 right join 试试
select a.id,a.name,b.dept_id
from emp_dept b right join emp a on (a.id=b.emp_id);
mysql> select a.id,a.name,b.dept_id
-> from emp_dept b right join emp a on (a.id=b.emp_id);
+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 1 | Dennis-1 | R&D |
| 2 | Dennis-2 | DEv |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+
# 是不是和 left join 一样了?
>> direct join
--------------
# 如果用right join 同不用 Join 直接挑资料是相同的,它等介於以下的指令
select a.id,a.name,b.dept_id
from emp a ,emp_dept b
where a.id=b.emp_id;
mysql> select a.id,a.name,b.dept_id
-> from emp a ,emp_dept b
-> where a.id=b.emp_id;
+----+----------+---------+
| id | name | dept_id |
+----+----------+---------+
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&D |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+----+----------+---------+
怎样,弄明白了吗?
Enjoy it!
作者：DennisLan
[MySql]MySQL Join详解
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
