MySQL Join详解

　　还是先 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!