---

还是先 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;www.chinaitpower.comj3qqn

+----+-----------+---------+
│ 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);www.chinaitpower.comj3qqn



+------+----------+---------+
│ 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 │
+----+-----------+---------+www.chinaitpower.comj3qqn

# 是不是和 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!