正在阅读：数据库学习:MySQL Join详解数据库学习:MySQL Join详解

2004-04-01 13:26 出处：CSDN 作者：DennisLan 责任编辑：linjixiong

+----+-----------+---------+
| 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);

察看评论详细内容我要发表评论