- 论坛徽章:
- 0
|
mysql> select * from emp;
+-------+-------+--------+------+
| empno | ename | deptno | age |
+-------+-------+--------+------+
| 1001 | wang | 10 | 20 |
| 1002 | ang | 20 | 30 |
| 1003 | ng | 10 | 20 |
| 1004 | ling | 30 | 40 |
| 1004 | dong | 40 | 40 |
+-------+-------+--------+------+
5 rows in set (0.00 sec)
mysql> select * from dept;
+--------+-----------+----------+
| deptno | detp_name | location |
+--------+-----------+----------+
| 10 | sales | GZ |
| 20 | it | BJ |
| 30 | marketing | GZ |
+--------+-----------+----------+
3 rows in set (0.00 sec)
1) Left join
mysql> select empno,ename,age,emp.deptno,detp_name,location from emp left join dept on emp.deptno=dept.deptno;
+-------+-------+------+--------+-----------+----------+
| empno | ename | age | deptno | detp_name | location |
+-------+-------+------+--------+-----------+----------+
| 1001 | wang | 20 | 10 | sales | GZ |
| 1002 | ang | 30 | 20 | it | BJ |
| 1003 | ng | 20 | 10 | sales | GZ |
| 1004 | ling | 40 | 30 | marketing | GZ |
| 1004 | dong | 40 | 40 | NULL | NULL |
+-------+-------+------+--------+-----------+----------+
5 rows in set (0.02 sec)
2) Left jion + age>30
mysql> select empno,ename,age,emp.deptno,detp_name,location from emp left join dept on emp.deptno=dept.deptno and age>30;
+-------+-------+------+--------+-----------+----------+
| empno | ename | age | deptno | detp_name | location |
+-------+-------+------+--------+-----------+----------+
| 1001 | wang | 20 | 10 | NULL | NULL |
| 1002 | ang | 30 | 20 | NULL | NULL |
| 1003 | ng | 20 | 10 | NULL | NULL |
| 1004 | ling | 40 | 30 | marketing | GZ |
| 1004 | dong | 40 | 40 | NULL | NULL |
+-------+-------+------+--------+-----------+----------+
5 rows in set (0.00 sec)
3) Left jion + location='GZ'
mysql> select empno,ename,age,emp.deptno,detp_name,location from emp left join dept on emp.deptno=dept.deptno and location='GZ';
+-------+-------+------+--------+-----------+----------+
| empno | ename | age | deptno | detp_name | location |
+-------+-------+------+--------+-----------+----------+
| 1001 | wang | 20 | 10 | sales | GZ |
| 1002 | ang | 30 | 20 | NULL | NULL |
| 1003 | ng | 20 | 10 | sales | GZ |
| 1004 | ling | 40 | 30 | marketing | GZ |
| 1004 | dong | 40 | 40 | NULL | NULL |
+-------+-------+------+--------+-----------+----------+
本文来自ChinaUnix博客,如果查看原文请点:http://blog.chinaunix.net/u2/61329/showart_485889.html |
|